Reputation:
I'm sure this question will be easy for you lot... :) I'm simply trying to update an existing record in my database using the following:
Private Sub Button12_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button12.Click
If Not cnn.State = ConnectionState.Open Then
cnn.Open()
End If
cmd2.Connection = cnn
cmd2.CommandText = "UPDATE HireItemRecord SET HireItemBeginDate = " & TextBox45.Text & _
" ,HireItemEndDate = " & TextBox44.Text & _
" ,HireItemCost = " & TextBox16.Text & _
" ,PaymentMethod = " & TextBox17.Text & _
" ,Staff_Id = " & TextBox19.Text & _
" ,HireItemNotes = " & TextBox18.Text & _
" ,HireItemReturnDate = " & TextBox43.Text & _
"WHERE HireRecord_Id = " & TextBox13.Text
cmd2.ExecuteNonQuery()
ds1.Clear()
daHireItemRecord.Fill(ds1, "PersonDetails")
cnn.Close()
End Sub
However no matter what record is selected and whatever details are in the boxes I keep getting this same error over and over: SqlException was unhandled Incorrect syntax near '12'.
When there is absolutely nothing in the textboxes the error changes to: Incorrect syntax near ','.
I'm very new to this and I just can't seem to understand why this is happening. Thank you very much for your help. :)
Upvotes: 0
Views: 1589
Reputation: 28346
So much wrong with this.
i
, x
or tbl
for a variable name, but in general they should be descriptive.
Example for #2, where I've put "'1/1/1999' WHERE 1=1 --" into TextBox45:
`UPDATE HireItemRecord SET HireItemBeginDate = '1/1/1999' WHERE 1=1 -- , HireItemEndDate...`
Everything after the --
becomes a comment, so you get this:
`UPDATE HireItemRecord SET HireItemBeginDate = '1/1/1999' WHERE 1=1`
Can you imagine what would happen if I executed that query? Nothing good.
You should use parameterized queries, as per the recommendations in this question: Algorithm to avoid SQL injection on MSSQL Server from C# code?
Upvotes: 1
Reputation: 7863
You should never use string concatenation to build SQL. It leaves you open to SQL Injection attacks. Try using the SQLCommand object provided in .Net. This allows you to "parameterize" your query and you don't have to worry about where to put " and '.
It will also allow you add parameters naturally without having to convert them to strings. Something like this:
Dim command As New SqlCommand("SELECT * FROM Table", connection)
command.Parameters.Add("@ID", SqlDbType.Int)
command.Parameters("@ID").Value = customerID
I stole that code from the documentation about SQL Parameters here.
Upvotes: 0