CODEWITHSUNDEEP
vb.netms-access
Bader100
Bader100

Reputation: 13

syntax error in INSERT INTO statement

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

Upvotes: 1

Views: 107

Answers (2)

Zaider
Zaider

Reputation: 2013

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 & ",.

Upvotes: 0

rodit
rodit

Reputation: 1766

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

Upvotes: 2

Related Questions