syntax error in INSERT INTO statement

Question

I have had the following error for a couple days and can't find the error. Can anyone please help me with this and ideally re-write the code with the solution.

Private Sub btnAdd_Click(sender As Object, e As EventArgs) Handles btnAdd.Click
     Dim cmd As New OleDb.OleDbCommand
     If Not cnn.State = ConnectionState.Open Then
        'The line of code below opens the connection to the database if it isnt open
        cnn.Open()
     End If

    cmd.Connection = cnn
    'Check whether to add new or update
    If Me.txtItemID.Tag & "" = "" Then
        'Add new 
        'The line of coding below adds data to table
        cmd.CommandText = "INSERT INTO Product ([Item ID], [Item Name], [Item Type], [Quantity], [Min Shelf Stock], [Purchase Price], [Note]) " & _
                        " VALUES (" & Me.txtItemID.Text & ",'" & Me.txtItemName.Text & "','" & _
                        Me.cboItemType.Text & "','" & Me.txtQuantity.Text & "','" & _
                        Me.txtMinShelfStock.Text & "','" & Me.txtPurchasePrice.Text & "','" & _
                        Me.txtNote.Text & "')"
        cmd.ExecuteNonQuery()
    Else
        'Update data in the table 
        cmd.CommandText = "UPDATE Product " & _
                    " SET Item ID=" & Me.txtItemID.Text & _
                    ", Item Name='" & Me.txtItemName.Text & "'" & _
                    ", Item Type='" & Me.cboItemType.Text & "'" & _
                    ", Quantity='" & Me.txtQuantity.Text & "'" & _
                    ", Min Shelf Stock='" & Me.txtMinShelfStock.Text & "'" & _
                    ", Purchase Price='" & Me.txtPurchasePrice.Text & "'" & _
                    ", Note='" & Me.txtNote.Text & "'" & _
                    " WHERE Item ID=" & Me.txtItemID.Tag
        cmd.ExecuteNonQuery()
    End If
    'Refresh data in list
    RefreshData()
    'Clear the form
    Me.btnClear.PerformClick()

    'The code below closes the connection to the database
    cnn.Close()
End Sub

In the updatestatement you need to enclose the field names in brackets when they contain blanks; eg: SET Item ID -> SET [Item ID] — jpw
– jpw, Commented Apr 8, 2014 at 14:48
possible duplicate of having this error An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in System.Data.dll — HansUp
– HansUp, Commented Apr 8, 2014 at 15:03

rodit · Accepted Answer · 2014-04-08 15:00:32Z

2

Try This...

cmd.CommandText = "INSERT INTO Product ([Item Id], [Item Name], [Item Type], [Quantity], [Min  Shelf Stock], [Purchase Price], [Note]) VALUES (@id, @name, @iType, @quantity, @minshelfstock, @price, @note)"
cmd.Paramaters.AddWithValue("@id", txtItemId.Text)
cmd.Paramaters.AddWithValue("@name", txtItemName.Text)
cmd.Paramaters.AddWithValue("@iType", cboItemType.Text)
cmd.Paramaters.AddWithValue("@quantity", txtQuantity.Text)
cmd.Paramaters.AddWithValue("@minshelfstock", txtMinShelfStock.Text)
cmd.Paramaters.AddWithValue("@price", txtPurchasePrice.Text)
cmd.Paramaters.AddWithValue("@note", txtNote.Text)

Hope this helps

RoDiT

answered Apr 8, 2014 at 15:00

rodit

1,7862 gold badges21 silver badges35 bronze badges

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

3 Comments

Bader100 Over a year ago

Thanks but it gives me a error stating 'Error 1 'Paramaters' is not a member of 'System.Data.OleDb.OleDbCommand'.

rodit Over a year ago

Sorry just my spelling XD! Try Param e ters link

rodit Over a year ago

@Bader100 See message above in case you didn't get notified

Zaider · Accepted Answer · 2014-04-08 15:17:14Z

0

The first thing that I notice is that you have Quantity, Price and Min Shelf Stock surrounded by quotes in your select statement like ,'" & Me.txtQuantity.Text & "', which would submit them as text, but if these fields in your table are number formats of some sort then they have to be entered as numbers ," & Me.txtQuantity.Text & ",.

answered Apr 8, 2014 at 15:17

Zaider

2,0231 gold badge24 silver badges33 bronze badges

1 Comment

Matt Wilko Over a year ago

We should be leading the OP down the route of parametrised queries - this issue then becomes an irrelevance

Collectives™ on Stack Overflow

syntax error in INSERT INTO statement

2 Answers 2

3 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related