vb.net Incorrect syntax near the keyword where

Question

Incorrect syntax near the keyword WHERE

Dim cmd As New SqlCommand
        cmd.Connection = conn
        cmd.CommandText = "UPDATE main WHERE ID = '" & ID.Text & "'" & vbCrLf & _
        "SET Date = '" & _date.Text & "'," & vbCrLf & _
        "Artist = '" & artist.Text & "'," & vbCrLf & _
        "City = '" & City.Text & "'," & vbCrLf & _
        "Venue = '" & venue.Text & "'"
cmd.ExecuteNonQuery()

I don't understand why I am getting this error. I have tried changing every little thing about it???

the set is right under update. i also tried with where at the bottom — user3232927, Jan 29 '14 at 18:46
Since the error is coming from SQL, what's the resulting SQL query that you're actually executing? — David, Jan 29 '14 at 18:53

score 1 · Answer 1 · edited May 23 '17 at 10:31

The correct syntax for UPDATE is

UPDATE tablename SET field1=Value1,..... WHERE keyField=keyValue

So your query should be written as

Dim cmd As New SqlCommand
cmd.Connection = conn
cmd.CommandText = "UPDATE main SET Date = @dt, Artist = @art, City = @city," & _
                  "Venue = @ven WHERE ID = @id"

cmd.Parameters.AddWithValue("@dt", date.Text )
cmd.Parameters.AddWithValue("@art", artist.Text )
cmd.Parameters.AddWithValue("@city", City.Text )
cmd.Parameters.AddWithValue("@ven", venue.Text )
cmd.Parameters.AddWithValue("@id", ID.Text )
cmd.ExecuteNonQuery()

Notice how removing the string concatenation and using parameters your query text is more understandable and easier to verify.
Of course this removes also any possibility of Sql Injection and parsing problems.
Still, this could not be completely correct if any of your fields are not of text type.
As written all the parameter's values are passed as strings and this could cause problems if (for example) your Date field is a smalldatetime instead of a string (or ID is a number).

Thanks for using parameters. – Chris Farmer Jan 29 '14 at 18:57 — Chris Farmer, Jan 29 '14 at 18:57

Obsidian Phoenix · Answer 2 · 2014-01-29T19:05:46.920

You have no space between WHERE ID = '" & ID.Text & "' and "SET Date = '" (The vbcrlfs don't really translate across to Sql when you send it). You may also have something in your textboxes with an apostrophe inside it, which is causing one of the sql strings to terminate early.

For this reason (and so it can't be hacked via Sql Injection) you should use SqlParameters.

Imagine this, for instance. I enter ';Truncate Table main;' into _date.Text. Your query suddenly looks like this:

Update main Set Date = ''; Truncate Table main;'', Artist = '', City = '', venue = '', where id = ''

Most of that will fail, but Truncate Table main is likely to execute and clear your data.

To get around this, use SqlParameter:

    Dim conn As New SqlConnection()
    Dim cmd As New SqlCommand

    cmd.Parameters.Add(New SqlParameter("ID", ID.Text))
    cmd.Parameters.Add(New SqlParameter("Date", _date.Text))
    cmd.Parameters.Add(New SqlParameter("Artist", artist.Text))
    cmd.Parameters.Add(New SqlParameter("City", City.Text))
    cmd.Parameters.Add(New SqlParameter("Venue", venue.Text))

    cmd.Connection = conn
    cmd.CommandText = "UPDATE main " & vbCrLf & _
                        "SET Date = @Date, " & vbCrLf & _
                        "Artist = @Artist, " & vbCrLf & _
                        "City = @City, " & vbCrLf & _
                        "Venue = @Venue " & vbCrLf & _
                        "WHERE ID = @ID"


    cmd.ExecuteNonQuery()

vb.net Incorrect syntax near the keyword where

2 Answers2