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]

How do I ask before I delete the item from the listobox I did get it to work my code thankx for all you help (about the word condition what am I suppose to name it?)

 

[jmcilhinney]

And what is the value of 'Me.lstName.SelectedItem' when this error occurs?

By the way, we don't need that enormous code listing. It's just a waste of time our looking through loads of irrelevant code. Please post the relevant code and only the relevant code.

 

[nicole200718]

I don't get an error anymore its all good

I was just wondering how do I ask before I delete the item from the listbox like have a window come up saying yes or no??

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

 

[jmcilhinney]

You may not be getting an error but you will also not be deleting anything. You could try using ? as the place-holder for the parameter in the SQL code, although I would think that the @ notation should work.

 

[nicole200718]

Where do I put the ?

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

 

[jmcilhinney]

That line creates a String containing the SQL DELETE statement. You put it wherever you need a String containing a SQL DELETE statement.

 

[nicole200718]

Huh?? Confused

 

[jmcilhinney]

You buy a chair because you want to use a chair somewhere. You put the chair where you want to use it. The same goes here. You create a String containing a SQL DELETE statement because you want to use a SQL DELETE statement somewhere. You put the code where you want to use the SQL DELETE statement. You tell us where that is. You know why you created it so you must know where you want to use it.

 

[nicole200718]

I attached a picture of what I have for my Access database.... Can you tell me what I need to name the condition id thinks its condition?

 

[nicole200718]

Ok I got it Now how do I ask before I delete a name like a message box?

' 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 Customer_Name = @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]

Exaftly what you said: display a MessageBox. You would then only delete if the user clicks Yes or OK, depending on what buttons you display.

 

[nicole200718]

Ya but idk how to do the if statement I'm so poor at them and where do I put the if statement exactly?

 

[jmcilhinney]

We're not here to think for you. It's your homework so it's for you to do. We can help you but you have to make the effort.

So, what do you think it should look like? Where do you think it should go? Start by asking yourself how you want the application to behave. First the user should click the Button, then the message should be displayed, then the record gets deleted. You already know that, right? It's your app so you should know how you want it to behave. Given that behaviour, how many places could the code to display the message possibly go?

As for the If statement, what do you think it should look like? I'm sure that you have seen examples of If statements before and, I would hope, had them explained to you. Have you looked back through your book or notes to review what you've learned about If statements? What is the point of an If statement? To do something if a condition is true, right? So what is it that you want to do if the condition is true and what is the condition? None of this stuff is programming specific. This is all just about how you want the app to behave.

Once you've thought about those things and made an effort to write some code, if you are still having issues then show us what you've done and we can help you fix it. At least then we have something of yours to start with though, instead of us just doing your homework for you. Keep in mind, we are happy to help you but I, at least, expect you to think for yourself and make an effort. Once I see that, I can guide you to the point where you can fix the errors yourself.