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
– JSR, Commented Mar 27, 2013 at 16:48
Does the exact same SQL string give you an error if you do it via Access instead? — Roger Rowland
– Roger Rowland, Commented Mar 27, 2013 at 16:48
The Message that is displayed is simply "Syntax error in UPDATE statement." — mlieven
– mlieven, Commented Mar 27, 2013 at 16:50
I think Note is a reserved word in MS-Access, not sure if this causes the error or not though — Sparky
– Sparky, Commented Mar 27, 2013 at 16:53

Community · Accepted Answer · 2017-05-23 11:54:04Z

5

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.

edited May 23, 2017 at 11:54

CommunityBot

11 silver badge

answered Mar 27, 2013 at 16:53

Steve

217k22 gold badges242 silver badges296 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

mlieven Over a year ago

Thanks It works now. That's what I get for 1. not knowing that, and 2 using someone-else's database

Sparky Over a year ago

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.

Collectives™ on Stack Overflow

VB.NET Syntax error in UPDATE statement

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related