VB.NET Syntax error in UPDATE statement

Question

I'm currently working on a simple project the shows a list of people, and basically indicates if they are in the office, or out. The people can also edit the list to indicate whether they are in the office or out, and update a message saying when they'll be back.

My problem is that when I update the list, I'm getting an error that says that there is a Syntax error in my Update Statement, but I can not find it. I am using visual studio 2012, developing in VB.NET, and using an access database, accessed through an OleDB connection.

Here is the VB code in question

 Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
    If InOutComboBox.SelectedItem = "IN" Then
        MessageTextBox.Text = ""
    End If
    con = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\..\People.mdb")
    con.Open()
    If ListView1.SelectedIndices.Count > 0 Then
        Dim comStr As String = "UPDATE tblStaffNames SET OutIn = '" & InOutComboBox.SelectedItem & "', Note = '" & MessageTextBox.Text & "' WHERE recid = " & ListView1.SelectedItems(0).SubItems(0).Text
        cmd = New OleDbCommand(comStr, con)
        Try
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show(ex.Message & " - " & ex.Source)
        End Try
    End If
    ListView1.Clear()
    LoadList()

End Sub

And here is a sample value of comStr when I run the code

UPDATE tblStaffNames SET OutIn = 'OUT', Note = 'on vacation' WHERE recid = 26

Any help would be much appreciated. Thank You.

Does it fail every time or only sometimes. When it does fail, what is the value of MessageTextBox.Text ? — JSR, Mar 27 '13 at 16:48
Does the exact same SQL string give you an error if you do it via Access instead? — Roger Rowland, Mar 27 '13 at 16:48
The Message that is displayed is simply "Syntax error in UPDATE statement." — mlieven, Mar 27 '13 at 16:50
@ JSR It fails every time regardless of what is in the text box — mlieven, Mar 27 '13 at 16:51
I think Note is a reserved word in MS-Access, not sure if this causes the error or not though — Sparky, Mar 27 '13 at 16:53
Maybe you should try putting the default schema before the table name. — user2156850, Mar 27 '13 at 16:54

score 5 · Accepted Answer · edited May 23 '17 at 11:54

NOTE is a reserved keyword for Jet-MsAccess.
You need to encapsulate with square brackets before submitting your command to the database engine

You have another big problem in your query. The user input cannot be trusted to be sent directly to the database using string concatenation. You should use Parametrized query (Sql Injection)

Using con = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=..\..\People.mdb")
    con.Open()
    If ListView1.SelectedIndices.Count > 0 Then
        Dim comStr As String = "UPDATE tblStaffNames SET OutIn = ?, [Note] = ?" & _ 
               " WHERE recid = ?"
        cmd = New OleDbCommand(comStr, con)
        cmd.Parameters.AddWithValue("@p1", InOutComboBox.SelectedItem)
        cmd.Parameters.AddWithValue("@p2", MessageTextBox.Text)
        cmd.Parameters.AddWithValue("@p3", ListView1.SelectedItems(0).SubItems(0).Text)
        Try
            cmd.ExecuteNonQuery()
        Catch ex As Exception
            MessageBox.Show(ex.Message & " - " & ex.Source)
        End Try
    End If
End Using

Pay attention to the parameter order, in OleDb is positional, so every parameter should appear in the Parameters collection in the exact order expected by the ? placeholders.

Also, as you can see, I have added also the Using statement that assure a correct closing and disposing of a connection object when you have finished working with it.

Thanks It works now. That's what I get for 1. not knowing that, and 2 using someone-else's database — mlieven, Mar 27 '13 at 16:58
Thanks, I thought Note but be a reserved word, but haven't used Access is a long time, so wasn't sure how to escape it. — Sparky, Mar 27 '13 at 16:59

VB.NET Syntax error in UPDATE statement

1 Answers1