Delete SQL in datagrid.

tiffany

Well-known member
Joined
Aug 7, 2005
Messages
57
Programming Experience
1-3
Hi,
Did anybody knows how to use delete sql statement? I'm using this:

Private Sub DataGrid1_DeleteCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DataGrid1.DeleteCommand

Dim i As Integer = e.Item.ItemIndex

Dim mtitle As String = DataGrid1.DataKeys(e.Item.ItemIndex)

Dim strConn2 As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & Server.MapPath("movie.mdb")

Dim cmd As New OleDbCommand("DELETE from movieDet WHERE Title = '" + mtitle + "' ")



cmd.Connection.Open()

cmd.ExecuteNonQuery()

cmd.Connection.Close()

End Sub

i use this for the link button in datagrid. But ther is an error when run in this line: cmd.Connection.Open()

It says that Object reference not set to an instance of an object.

Help pls...
 
First off, you haven't assigned a valid OleDbConnection object to the Connection property of the OleDbCommand.

Secondly, that code is not going to affect the data in your DataGrid at all. Assuming you do add a connection, you are going to delete the matching rows from the database without touching the data currently in your DataGrid. I suggest you take a look at the data access examples in the 101 VB samples.
 
Hi, i had tried the sample that you had gave me. But i had a little error. Beacuse i don't use SQL server. I use Oledb and i come out with this code:

PrivateSub DeleteItem(ByVal strTitle AsString)

Dim strSQL AsString = "DELETE from movieDet WHERE Title = strTitle "

Dim strConn AsString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= " & Server.MapPath("movie.mdb")

Dim scnnNW AsNew OleDbConnection(strConn)

Dim scmd AsNew OleDbCommand(strSQL)

Try

scnnNW.Open()

scmd.ExecuteNonQuery()

Cache.Remove("dvProducts")

BindProductsGrid()

strMsg = "Product successfully deleted from the database."

Catch exp As Exception

strErrorMsg = "Database error! Product not deleted from database. " & _

"Error message: " & exp.Message

Finally

scnnNW.Close()

EndTry

EndSub


But it did not delete in the datagrid and database. I had this code in datagrid:

Private Sub DataGrid1_ItemCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DataGrid1.ItemCommand

DeleteItem(DataGrid1.DataKeys(e.Item.ItemIndex).ToString)

End Sub



Help pls!
 
Last edited:
The fact that you are using OleDn instead of SqlClient is irrelevant because all the objects correspond directly. Again, you have not assigned a connection to the command. You create a connection and a command but they don't know anything about each other unless you tell them. You either need to pass the connection to the constructor of the command:
VB.NET:
[size=2][color=#0000ff][size=2][color=#0000ff]Dim[/color][/size][size=2] scmd [/size][size=2][color=#0000ff]As[/color][/size][size=2][color=#0000ff]New[/color][/size][size=2] OleDbCommand(strSQL, [/size][/color][/size][size=2][color=#0000ff][size=2]scnnNW[/size][/color][/size][size=2][color=#0000ff][size=2])
 [/size][/color][/size]
or assign it to the Connection property:
VB.NET:
scmd.Connection = scnnNW

Also, like I said before, this code has no affect on your DataGrid whatsoever. What you should be doing is deleting the row from the DataTable that I assume you have used to fill the DataGrid and then using an OleDbDataAdapter to delete the row from the database by calling Update. There is at least one example of this in the 101 samples.

Can you please put your code within
VB.NET:
 tags to increase readability.  See my signature for more info.
 
Back
Top