Additional information: Syntax error (missing operator) in query expression 'Item ID =1'

Question

I am receiving the following error when trying to edit information entered in a database.

Additional information: Syntax error (missing operator) in query expression 'Item ID =1'.

Can anyone help? Thanks

Private Sub btnEdit_Click(sender As Object, e As EventArgs) Handles btnEdit.Click
    'check for the selected item in list
    If Me.dgvData.Rows.Count > 0 Then
        If Me.dgvData.SelectedRows.Count > 0 Then
            Dim intItemID As Integer = Me.dgvData.SelectedRows(0).Cells("Item ID").Value
            'Get the data from database followed Item ID
            'Open the connection
            If Not cnn.State = ConnectionState.Open Then
                cnn.Open()
            End If
            'Get the data into the datatable
            Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM Product " & _
                                                  " WHERE Item ID =" & intItemID, cnn)
            Dim dt As New DataTable
            da.Fill(dt)

            Me.txtItemID.Text = intItemID
            Me.txtItemName.Text = dt.Rows(0).Item("Item Name")
            Me.cboItemType.Text = dt.Rows(0).Item("Item Type")
            Me.txtQuantity.Text = dt.Rows(0).Item("Quantity")
            Me.txtMinShelfStock.Text = dt.Rows(0).Item("Min Shelf Stock")
            Me.txtPurchasePrice.Text = dt.Rows(0).Item("Purchase Price")
            Me.txtNote.Text = dt.Rows(0).Item("Note")
            '
            'Hide the ID to be edited in TAG of txtItemID in case ID is changed 
            Me.txtItemID.Tag = intItemID
            'Change the add button to update 
            Me.btnAdd.Text = "Update"
            'Disable the Edit button
            Me.btnEdit.Enabled = False
            'Close the connection
            cnn.Close()
        End If
    End If
End Sub

score 1 · Answer 1 · edited May 23 '17 at 11:57

If you have a field name that contains a space you need to enclose it in square bracket to avoid confuse the sql parser of your database engine

SELECT * FROM Product WHERE [Item ID] .....

Also, while your query text has a low possibility to be hacked with Sql Injection, it is always the best practice to use a parameterized query and not a string concatenation

Dim da As New OleDb.OleDbDataAdapter("SELECT * FROM Product " & _
                                     " WHERE [Item ID] = ?", cnn)
da.SelectCommand.Parameters.AddWithValue("@p1", intItemID)

Additional information: Syntax error (missing operator) in query expression 'Item ID =1'

1 Answers1