Trying to update record, keep getting this error vb.net

Question

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. :)

score 1 · Answer 1 · edited May 23 '17 at 12:21

So much wrong with this.

You need a space after each comma, not before it.
You should be escaping your values before using them in the query. If I put "0 WHERE 1=1 -- " in any of your text boxes, it'll trash your entire table.
You should ALWAYS name your form controls properly. If I sent you back to this code in a year's time and told you there was a problem with TextBox44, would you know what it means? Same goes for your variables. Sometimes it's ok to have 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?

thanks for your advice, I will make sure I do as you said. If only I could choose several answers as my accepted answer, all have been very informative! — , Nov 07 '11 at 17:11

score 0 · Accepted Answer · answered Nov 07 '11 at 16:48

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.

forgot to thank you....thanks! Got it all working. To be honest I had zero idea about this method (only been programming for 6 months or so, first time I've dealt with databases), but I can clearly see the benefit of this method. Will definitely follow your advice in future. — , Nov 08 '11 at 10:26

Trying to update record, keep getting this error vb.net

2 Answers2