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]

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

​

 

[ss7thirty]

ExecuteNonReader will return a Integer (maybe a long) containing the rows affected, use that for executing SQL Insert, Updates, Deletes, and any other sql commands that do not return result sets.

 

[jmcilhinney]

E.g.

Using connection As New OleDbConnection("connection string here"), _
      command As New OleDbCommand("DELETE SomeTable WHERE SomeColumn = @SomeColumn", connection)
    command.Parameters.AddWithValue("@SomeColumn", someValue)
    connection.Open()
    command.ExecuteNonQuery()
End Using

 

[jmcilhinney]

ExecuteNonReader will return a Integer (maybe a long) containing the rows affected, use that for executing SQL Insert, Updates, Deletes, and any other sql commands that do not return result sets.

That would be ExecuteNonQuery.

 

[nicole200718]

Idk if I really quite understand?

 

[jmcilhinney]

You start by creating a connection. You then create a command with the appropriate SQL code to delete the data and the connection you created previously.

You don't use string concatenation to insert variables into the SQL code. You use parameters. You put a place-holder in the SQL code with a "@" prefix, using an appropriate name. That name should be the name of the column in most cases, but something else might be more appropriate in some cases.

You then add the parameter to the command. You add the parameters by name, using the same names as the place-holders. Note that, if there are multiple parameters, you MUST add them in the same order that they appear in the SQL code. That is not the case for all data sources but it is for Access.

You then open the connection and execute the command.

Finally, any objects created by the Using statement, i.e. the connection and the command, are disposed by the End Using statement. This will release any resources those objects are holding, which will implicitly close the connection.

 

[nicole200718]

Basic Database EOC?

Finish the Delete Button logic so it removes a record from the database and the listbox?? I dont understand how to do this?

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

Option Explicit On
Option Strict On

' Include the Datavase 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 is an 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

' 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
End With

' Execute Reader

' This runs the SQL statements and collects the results

objReader = objCmd.ExecuteReader()
' Read each record returned 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 Conenctions 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 is an 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

' 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
End With

' Execute Reader

' This runs the SQL statements and collects the results

objReader = objCmd.ExecuteReader()
Dim blnHasRows As Boolean = 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)
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]

Is this right at all to remove a record from the database and the listbox??

lstName.Items.Remove(lstName.SelectedItem)

Dim con As New OleDb.OleDbConnection
Dim sSql As String

'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 As New OleDbCommand(sSql, con)
With command.Parameters
.AddWithValue("@Customer_Name", Me.lstName.SelectedItem)
End With

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

 

[nicole200718]

Is this right to remove a record from the database and the listbox??

lstName.Items.Remove(lstName.SelectedItem)

Dim con As New OleDb.OleDbConnection
Dim sSql As String

'Connect To Accounts 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 As New OleDbCommand(sSql, con)
With command.Parameters
.AddWithValue("@Customer_Name", Me.lstName.SelectedItem)
End With

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

 

[jmcilhinney]

Yes and no. That will delete a record, but it will delete the wrong record. You remove the SelectedItem from the ListBox first, then you use the SelectedItem to delete the record. Obviously the item you delete will not be the same item you remove.

 

[nicole200718]

ok so can u help me figure it out please?

 

[jmcilhinney]

If this is wrong:

You remove the SelectedItem from the ListBox first, then you use the SelectedItem to delete the record.

what are the alternatives?

 

[nicole200718]

c ik what ur saying but I'm not quite understand what goes wher n how to do it n what do u mean alternatives???

 

[jmcilhinney]

This is basic logic that requires no programming experience whatsoever. If you're doing A first and then B and that's wrong, do B first and then A.