Question Execute Non Query Help

[nicole200718]

I need help with what to put in the btnDelete_Click(On the bottom of the code) can anyone help how can I delete a specific name when I select it what do i put here?

Purpose: Template for building an example to show how to
' use an OLEDBCOMMAND ExecuteReader & ExecuteNonQuery

Option Explicit On
Option Strict On

' Include the database access objects in this project
Imports System.Data.OleDb


Public Class ExecuteNonQuery

Dim strCN As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Customer.mdb"

Dim strSQL As String

Private Sub ExecuteNonQuery_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

' Define a connection to the database
' OleDbConnection i san object that represents
' an open connection to the data source
Dim objCon As New OleDbConnection(strCN)

' Define command
' OleDbCommand is an object that represents
' an SQL statement or stored procedure to execute
' against the data source
Dim objCmd As New OleDbCommand()

' Define DataReader
' OleDbDataReader defines a way to read a forward-only
' strem of data rows from the data soruce
Dim objReader As OleDbDataReader

' Define the SQL statement to run
strSQL = "SELECT Customer_Name FROM tblCustomers ORDER BY Customer_Name"

' Populate properties and run methods of the ObjCmd object
' objCmd.Connection = objCon
' objCmd.Connection.Open()
' objCmd.CommandText = strSQL

' Shortcut way of setting properties
With objCmd
.Connection = objCon
.Connection.Open()
.CommandText = strSQL
End With

' Execute Reader
' This runs the SQL statements and collects the results
objReader = objCmd.ExecuteReader()

' Read each record returning from the query
Do While (objReader.Read())
' Read the name on the current record from
' the table field 'Customer_Name'
Dim objField As Object = objReader.Item("Customer_Name")

' Add the name to the listBox on the GUI
lstName.Items.Add(objField)
Loop

' Close Connections and objects
objReader.Close()
objCmd.Dispose()
objCon.Close()
objCon.Dispose()

End Sub

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
' Insert record into the database

Dim strNewName As String = txtName.Text

' Define a connection to the database
' OleDbConnection i san object that represents
' an open connection to the data source
Dim objCon As New OleDbConnection(strCN)

' Define command
' OleDbCommand is an object that represents
' an SQL statement or stored procedure to execute
' against the data source
Dim objCmd As New OleDbCommand()

' Define DataReader
' OleDbDataReader defines a way to read a forward-only
' strem of data rows from the data soruce
Dim objReader As OleDbDataReader

' Define the SQL statement to run
strSQL = "SELECT Customer_Name FROM tblCustomers WHERE Customer_Name ='" & _
strNewName & "'"

' Populate properties and run methods of the ObjCmd object
' objCmd.Connection = objCon
' objCmd.Connection.Open()
' objCmd.CommandText = strSQL

' Shortcut way of setting properties
With objCmd
.Connection = objCon
.Connection.Open()
.CommandText = strSQL
End With

' Execute Reader
' This runs the SQL statements and collects the results
objReader = objCmd.ExecuteReader()
Dim binHasRows As Boolean = objReader.HasRows
objReader.Close()

If (binHasRows) Then
' Name exists, show message
MessageBox.Show("Name already exists!")
Else
' Name not in database

' Setup the insert statment
strSQL = "INSERT INTO tblCustomers values ('" & strNewName & "')"

' Update SQL statement on command object
objCmd.CommandText = strSQL

' Execute Statement
objCmd.ExecuteNonQuery()

' Duplicate listBox
Me.lstName.Items.Add(strNewName)
End If

' Clean up
objCmd.Dispose()
objCon.Close()
objCon.Dispose()
End Sub

Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
' DELETE FROM tableName WHERE condition
End Sub
End Class

 

[nicole200718]

So like this idk??

Dim con AsNew OleDb.OleDbConnection
Dim sSql AsString

'Connect To Customer Database

con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Customer.mdb"

con.Open()
sSql = "DELETE FROM tableName WHERE condition = @Customer_Name "

Dim command AsNew OleDbCommand(sSql, con)
With command.Parameters
.AddWithValue("@Customer_Name", Me.lstName.SelectedItem)
EndWith

Me.lstName.Refresh()
command.ExecuteNonQuery()
con.Close()

lstName.Items.Remove(lstName.SelectedItem)

 

[jmcilhinney]

When you tried it, did it work?

 

[nicole200718]

idk the reason I can't run it is cause I get this error everytime and have no clue on where to download it or anything and I have Windows 7

The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.

System.InvalidOperationException was unhandled
Message="The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine."
Source="System.Data"
StackTrace:
at System.Data.OleDb.OleDbServicesWrapper.GetDataSource(OleDbConnectionString constr, DataSourceWrapper& datasrcWrapper)
at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.OleDb.OleDbConnection.Open()
at ExecuteNonQuery.ExecuteNonQuery.ExecuteNonQuery_Load(Object sender, EventArgs e) in C:\Users\Desktop\ExecuteNonQueryTemplate\ExecuteNonQuery\ExecuteNonQuery.vb:line 47
at System.EventHandler.Invoke(Object sender, EventArgs e)
at System.Windows.Forms.Form.OnLoad(EventArgs e)
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
InnerException:

 

[jmcilhinney]

That's probably something that you should have mentioned to start with. The reason for that will almost certainly be that you are running a 64-bit OS and there is no 64-bit version of Jet. You need to change your project's target platform to x86 instead of Any CPU so that it will run in 32-bit mode on all sysytems.

 

[nicole200718]

how do I do this?

 

[nicole200718]

Ok I got that first error now I got this one??

System.Data.OleDb.OleDbException was unhandled
ErrorCode=-2147217904
Message="No value given for one or more required parameters."
Source="Microsoft JET Database Engine"
StackTrace:
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
at ExecuteNonQuery.ExecuteNonQuery.btnDelete_Click(Object sender, EventArgs e) in C:\Users\Nicole and Russell\Desktop\ExecuteNonQueryTemplate\ExecuteNonQuery\ExecuteNonQuery.vb:line 155
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventArgs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at ExecuteNonQuery.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException:

 

[jmcilhinney]

Are you using the exact code form post #16? If not, please show us the exact code you're running. Is an item selected in the ListBox when you run the code, i.e. is SelectedItem definitely not Nothing?

 

[nicole200718]

Yes I am using the exact code as post # 16 n no I have to select the item myself.

 

[jmcilhinney]

Then that would be your issue. You can't delete the selected item if no item is selected.

 

[nicole200718]

Ok but I do select a customer but when I do n then click delete I get this:

OleDbException was unhandled

System.Data.OleDb.OleDbException was unhandled
ErrorCode=-2147217904
Message="No value given for one or more required parameters."
Source="Microsoft JET Database Engine"
StackTrace:
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextE rrorHandling(OleDbHResult hr)
at System.Data.OleDb.OleDbCommand.ExecuteCommandTextF orSingleResult(tagDBPARAMS dbParams, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommandText( Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteCommand(Comm andBehavior behavior, Object& executeResult)
at System.Data.OleDb.OleDbCommand.ExecuteReaderIntern al(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()
at ExecuteNonQuery.ExecuteNonQuery.btnDelete_Click(Ob ject sender, EventArgs e) in C:\Users\Nicole and Russell\Desktop\ExecuteNonQueryTemplate\ExecuteNon Query\ExecuteNonQuery.vb:line 154
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnMouseUp(MouseEventAr gs mevent)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.O nMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.W ndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallba ck(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchM essageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager. System.Windows.Forms.UnsafeNativeMethods.IMsoCompo nentManager.FPushMessageLoop(Int32 dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.Run MessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.Run MessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(ApplicationCo ntext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsF ormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsF ormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsF ormsApplicationBase.Run(String[] commandLine)
at ExecuteNonQuery.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.Run UsersAssembly()
at System.Threading.ThreadHelper.ThreadStart_Context( Object state)
at System.Threading.ExecutionContext.Run(ExecutionCon text executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException:

 

[jmcilhinney]

sSql = "DELETE FROM tableName WHERE condition = @Customer_Name "

Do you really have a table named 'tableName'?

 

[nicole200718]

I think it's named tblCustomers

 

[jmcilhinney]

Then either you aren't using the exact code from post #16 or else you aren't using the proper table name.

 

[nicole200718]

This is the code I now have...


command.ExecuteNonQuery() (I get the error on this line the very bottom of the solution)

OptionExplicitOn
OptionStrictOn

' Include the Database access objects in this project

Imports System.Data.OleDb


PublicClass ExecuteNonQuery
Dim strCN AsString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Customer.mdb"

Dim strSQL AsString

PrivateSub ExecuteNonQuery_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) HandlesMyBase.Load
' Define a Connection to the database

' OleDbConnection is an object that represents

' an open connection to the data source

Dim objCon AsNew OleDbConnection(strCN)
' Define Command

' OleDbCommand is an object that represents

' an SQL statement or stored procedure to execute

' against the data source

Dim objCmd AsNew OleDbCommand()
' Define DataReader

' OleDbDataReader defines a way to read a forward-only

' stream of data rows from the data source

Dim objReader As OleDbDataReader
' Define the SQL statement to run

strSQL = "SELECT Customer_Name FROM tblCustomers ORDER BY Customer_Name"

' Populate properties and run methods of the ObjCmd object

'objCmd.Connection = objCon

'objCmd.Connection.Open()

'objCmd.CommandText = strSQL

' Shortcut way of setting properties

With objCmd
.Connection = objCon
.Connection.Open()
.CommandText = strSQL
EndWith

' Execute Reader

' This runs the SQL statements and collects the results

objReader = objCmd.ExecuteReader()
' Read each record returned from the query

DoWhile (objReader.Read())
' Read the name on the current record from

' the table field 'Customer_Name'

Dim objField AsObject = objReader.Item("Customer_Name")
' Add the name to the listBox on the GUI

lstName.Items.Add(objField)
Loop

' Close Conenctions and objects

objReader.Close()
objCmd.Dispose()
objCon.Close()
objCon.Dispose()
EndSub

PrivateSub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
' Insert record into the database

Dim strNewName AsString = txtName.Text
' Define a Connection to the database

' OleDbConnection is an object that represents

' an open connection to the data source

Dim objCon AsNew OleDbConnection(strCN)
' Define Command

' OleDbCommand is an object that represents

' an SQL statement or stored procedure to execute

' against the data source

Dim objCmd AsNew OleDbCommand()
' Define DataReader

' OleDbDataReader defines a way to read a forward-only

' stream of data rows from the data source

Dim objReader As OleDbDataReader
' Define the SQL statement to run

strSQL = "SELECT Customer_Name FROM tblCustomers WHERE Customer_Name ='" & _
strNewName & "'"

' Populate properties and run methods of the ObjCmd object

With objCmd
.Connection = objCon
.Connection.Open()
.CommandText = strSQL
EndWith

' Execute Reader

' This runs the SQL statements and collects the results

objReader = objCmd.ExecuteReader()
Dim blnHasRows AsBoolean = objReader.HasRows
objReader.Close()
If (blnHasRows) Then

' Name exists, show message

MessageBox.Show("Name already exists!")
Else

' Name not in database

' Setup the insert statement

strSQL = "INSERT INTO tblCustomers values ('" & strNewName & "')"

' Update SQL statement on command object

objCmd.CommandText = strSQL
' Execute Statement

objCmd.ExecuteNonQuery()
' Populate listBox

Me.lstName.Items.Add(strNewName)
EndIf

' Clean up

objCmd.Dispose()
objCon.Close()
objCon.Dispose()
EndSub

PrivateSub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
' DELETE FROM tableName WHERE condition

Dim con AsNew OleDb.OleDbConnection
Dim sSql AsString

'Connect To Customer Database

con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Customer.mdb"

con.Open()
sSql = "DELETE FROM tblCustomers WHERE condition = @Customer_Name "

Dim command AsNew OleDbCommand(sSql, con)
With command.Parameters
.AddWithValue("@Customer_Name", Me.lstName.SelectedItem)
EndWith

Me.lstName.Refresh()
command.ExecuteNonQuery()
con.Close()
lstName.Items.Remove(lstName.SelectedItem)
EndSub
EndClass

 

[InertiaM]

sSql = "DELETE FROM tblCustomers WHERE condition = @Customer_Name "

The column name where you store the customers' names is called "condition". Really???