Deleting from a datagrig on VB.NET

[jurelmo]

Hello there, I am new to VB.NET. And have been struggling with this datagrid. i always seem to have the below errors

Line 1: Incorrect syntax near '='.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Line 1: Incorrect syntax near '='.

Source Error:


Line 235: ' execute the command
Line 236: SqlConnection1.Open()
Line 237: DeleteCommand.ExecuteNonQuery()
Line 238: SqlConnection1.Close()
Line 239:


Source File: c:\inetpub\

http://wwwroot\Assigment\admin.aspx.vb

Line: 237

Stack Trace:


[SqlException: Line 1: Incorrect syntax near '='.]
System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
Assigment.admin.DataGrid1_DeleteCommand(Object source, DataGridCommandEventArgs e) in c:\inetpub\

http://wwwroot\Assigment\admin.aspx.vb:237

System.Web.UI.WebControls.DataGrid.OnDeleteCommand(DataGridCommandEventArgs e)
System.Web.UI.WebControls.DataGrid.OnBubbleEvent(Object source, EventArgs e)
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args)
System.Web.UI.WebControls.DataGridItem.OnBubbleEvent(Object source, EventArgs e)
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args)
System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e)
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
System.Web.UI.Page.ProcessRequestMain()


this is the piece of code




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


StatusLabel.Text = "Deleted was pressed"

Dim keycolumn As Integer = 1
' gives us the actual data value of the key field of the current row
Dim keyvalue As String = e.Item.Cells(keycolumn).Text

StatusLabel.Text = "Delete was pressed on row " + e.Item.Cells(keycolumn).Text

SqlCommand1.CommandText = "Delete "

Dim DeleteCommand As New SqlCommand("DELETE FROM links WHERE id =" & keyvalue, SqlConnection1)

' execute the command
SqlConnection1.Open()
DeleteCommand.ExecuteNonQuery()
SqlConnection1.Close()

' rebind the grid
DataGrid1.CurrentPageIndex = 0
DataGrid1.EditItemIndex = -1
BindGrid()

End Sub


Thanks

 

[ThatRickGuy]

2 things.

1) This is the VB.Net forum, not to be confussed with the ASP.Net forum.

2) Compare the error to the code:

[SqlException: Line 1: Incorrect syntax near '='.]

Dim DeleteCommand As New SqlCommand("DELETE FROM links WHERE id =" & keyvalue, SqlConnection1)

there are 2 things by the '='. A straight forward SQL statement, and a variable. The statement is syntacticly correct, but what would you guess happens if Keyvalue is over the wrong type, or is null, or an empty string?

I'll give you a hint, is starts with SQL and ends in Exception

-Rick

----------------------
If you're about to post an ASP.Net question,
please don't do it in the VB.Net forum

http://www.ringdev.com

I believe in killer coding ninja monkeys.

 

[jurelmo]

I am sorry, about the mistake. I am pretty new to the .NET environment.
Thank You for your reply. But as I said I am pretty new to .NET and cant see how to change. I have been trying to solve this problem without success.

Thanks for your assistance

Jurelmo

 

[ThatRickGuy]

What kind of field is ID? If it is a varchar/char/string/text or what not, your compare value needs to have "'"s arround it:

Dim DeleteCommand As New SqlCommand("DELETE FROM links WHERE id = '" & keyvalue & "'", SqlConnection1)

if ID is a numeric field, you need to make sure keyvalue is a number:

if isnumeric(keyvalue) then
  Dim DeleteCommand As New SqlCommand("DELETE FROM links WHERE id =" & keyvalue, SqlConnection1)
else
  StatusLabel.Text = "Please select a row to delete"
  exit sub
end if

-Rick

----------------------
If you're about to post an ASP.Net question,
please don't do it in the VB.Net forum

http://www.ringdev.com

I believe in killer coding ninja monkeys.