Strange Error Message

[Guest]

When I run the following UpdateCommand event I get the error: "Line 1:
Incorrect syntax near '?'." and the highlighted line is
"updateCommand.ExecuteNonQuery()". I've been troubleshooting this for 2
hours and have no clue what the problem is. Could someone please help me
understand what is going wrong? Thanks.



Private Sub DataGrid1_UpdateCommand(ByVal source As Object, ByVal e As
System.Web.UI.WebControls.DataGridCommandEventArgs) Handles
DataGrid1.UpdateCommand
'Retrieve the values from the control
Dim PKColumn As String
PKColumn = (CType(e.Item.FindControl("HL99"), HyperLink)).Text
Dim SpecieID As Integer
SpecieID = Integer.Parse(CType(e.Item.Cells(3).Controls(1),
DropDownList).SelectedItem.Value)
Dim updateCommand As New SqlCommand("UPDATE tblLogs SET SpecieID = ?
WHERE (PKColumn = ?)", SqlConnection1)
updateCommand.Parameters.Add("@SpecieID", SqlDbType.VarChar).Value
= SpecieID
updateCommand.Parameters.Add("@PKColumn", SqlDbType.VarChar).Value =
PKColumn
updateCommand.Connection.Open()
updateCommand.ExecuteNonQuery()
'Routine tasks...
DataGrid1.EditItemIndex = -1
Bind()
End Sub

 

[Chris R. Timmons]

When I run the following UpdateCommand event I get the error:
"Line 1: Incorrect syntax near '?'." and the highlighted line is
"updateCommand.ExecuteNonQuery()". I've been troubleshooting
this for 2 hours and have no clue what the problem is. Could
someone please help me understand what is going wrong? Thanks.



Private Sub DataGrid1_UpdateCommand(ByVal source As Object,
ByVal e As
System.Web.UI.WebControls.DataGridCommandEventArgs) Handles
DataGrid1.UpdateCommand
'Retrieve the values from the control
Dim PKColumn As String
PKColumn = (CType(e.Item.FindControl("HL99"),
HyperLink)).Text Dim SpecieID As Integer
SpecieID =
Integer.Parse(CType(e.Item.Cells(3).Controls(1),
DropDownList).SelectedItem.Value)
Dim updateCommand As New SqlCommand("UPDATE tblLogs SET
SpecieID = ?
WHERE (PKColumn = ?)", SqlConnection1)
updateCommand.Parameters.Add("@SpecieID",
SqlDbType.VarChar).Value
= SpecieID
updateCommand.Parameters.Add("@PKColumn",
SqlDbType.VarChar).Value =
PKColumn
updateCommand.Connection.Open()
updateCommand.ExecuteNonQuery()
'Routine tasks...
DataGrid1.EditItemIndex = -1
Bind()
End Sub

Change the SQL to use named parameters instead of positional
parameters:

UPDATE tblLogs SET SpecieID = @SpecieID WHERE (PKColumn = @PKColumn)

 

[Guest]

Thank you Chris. However, after making this update the error is now:
"Invalid column name 'PKColumn'." My full code is below. Thanks much for
your help.

'UPDATE command:
Dim updateCommand As New SqlCommand("UPDATE tblLogs SET SpecieID =
@SpecieID WHERE (PKColumn = @PKColumn)", SqlConnection1)
updateCommand.Parameters.Add("@SpecieID",
SqlDbType.VarChar).Value = SpecieID
updateCommand.Parameters.Add("@PKColumn", SqlDbType.VarChar).Value =
PKColumn
updateCommand.Connection.Open()
updateCommand.ExecuteNonQuery()
DataGrid1.EditItemIndex = -1
Bind()

 

[Chris R. Timmons]

Thank you Chris. However, after making this update the error is
now: "Invalid column name 'PKColumn'." My full code is below.
Thanks much for your help.

'UPDATE command:
Dim updateCommand As New SqlCommand("UPDATE tblLogs SET
SpecieID =
@SpecieID WHERE (PKColumn = @PKColumn)", SqlConnection1)
updateCommand.Parameters.Add("@SpecieID",
SqlDbType.VarChar).Value = SpecieID
updateCommand.Parameters.Add("@PKColumn",
SqlDbType.VarChar).Value =
PKColumn
updateCommand.Connection.Open()
updateCommand.ExecuteNonQuery()
DataGrid1.EditItemIndex = -1
Bind()

Is the PKColumn column in tblLogs a VARCHAR or some numeric type?
I'm guessing it's an INT, and that's why you're getting that error
message.

The statement

updateCommand.Parameters.Add("@PKColumn",
SqlDbType.VarChar).Value = PKColumn

is treating the value in PKColumn as a VARCHAR, so the generated SQL
statement looks something like this:

UPDATE tblLogs SET SpecieID = "999" WHERE (PKColumn = "42")

SQL Server doesn't do type coercion in this case, so it thinks "42"
is a literal column name which, of course, doesn't exist in tblLogs.

The solution is to change the SqlDbType in the Parameters.Add
statement to reflect the column type it is responsible for. If
PKColumn is an INT, then SqlDbType.Int should be used. Same advice
for the SpecieID column.