Syntax Error insert into statement error

Question

Public Class frmOrder
Dim ItemNumber As String
Dim con As New OleDb.OleDbConnection
Dim dbprovider As String
Dim dbsource As String
Dim ds As New DataSet
Dim da As OleDb.OleDbDataAdapter
Dim sql As String
Dim ds1 As New DataSet
Dim da1 As OleDb.OleDbDataAdapter
Dim sql1 As String
Dim ds2 As New DataSet
Dim da2 As OleDb.OleDbDataAdapter
Dim sql2 As String
Dim ds3 As New DataSet
Dim da3 As OleDb.OleDbDataAdapter
Dim sql3 As String
Dim ds4 As New DataSet
Dim da4 As OleDb.OleDbDataAdapter
Dim sql4 As String
Dim lastReceiptNumber As Integer
Dim CustomerID1 As Integer

Private Sub frmOrder_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    ItemNumber = frmShop.Label13.Text
    CustomerID1 = Convert.ToInt32(frmLogin.Label3.Text)
    dbprovider = "PROVIDER = Microsoft.ACE.OLEDB.12.0;"
    dbsource = "Data Source = C:\Users\Patrick\Desktop\FinalDatabase.accdb"
    con.ConnectionString = dbprovider + dbsource
    con.Open()
    sql = "SELECT * FROM ITEMS"
    da = New OleDb.OleDbDataAdapter(sql, con)
    da.Fill(ds, "Items")
    sql1 = "SELECT Address FROM CUSTOMER WHERE CustomerID= " & CustomerID1 & ""
    da1 = New OleDb.OleDbDataAdapter(sql1, con)
    da1.Fill(ds1, "Address")

    sql2 = "SELECT * FROM [ORDER]"
    da2 = New OleDb.OleDbDataAdapter(sql2, con)
    da2.Fill(ds2, "ORDER")

    sql3 = "SELECT MAX(ReceiptNumber) FROM [ORDER]"
    da3 = New OleDb.OleDbDataAdapter(sql3, con)
    da3.Fill(ds3, "Orders")
    sql4 = "SELECT * FROM DELIVERY"
    da4 = New OleDb.OleDbDataAdapter(sql4, con)
    da4.Fill(ds4, "Delivery")

    con.Close()

    txtItemCode.Text = ds.Tables("Items").Rows(ItemNumber).Item(0)
    txtItemName.Text = ds.Tables("Items").Rows(ItemNumber).Item(1)
    txtSize.Text = ds.Tables("Items").Rows(ItemNumber).Item(2)
    txtPrice.Text = ds.Tables("Items").Rows(ItemNumber).Item(4)
    txtDeliveryFee.Text = "100.00"
    txtDeliveryAddress.Text = ds1.Tables("Address").Rows(0).Item(0)

End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
    frmShop.Show()
    Me.Close()
End Sub

Private Sub btnBuy_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBuy.Click


    Dim today As System.DateTime
    Dim deliverydate As System.DateTime
    today = System.DateTime.Now
    deliverydate = today.AddDays(7)
    Dim cb As New OleDb.OleDbCommandBuilder(da2)
    Dim dsNewRow As DataRow
    Dim cb1 As New OleDb.OleDbCommandBuilder(da3)
    Dim dsNewRow1 As DataRow

    dsNewRow = ds2.Tables("ORDER").NewRow()
    dsNewRow.Item(1) = CustomerID1
    dsNewRow.Item(2) = txtItemCode.Text
    dsNewRow.Item(3) = txtQuantity.Text
    dsNewRow.Item(4) = txtPrice.Text
    dsNewRow.Item(5) = txtDeliveryFee.Text

    ds2.Tables("ORDER").Rows.Add(dsNewRow)
    da2.Update(ds2, "ORDER")




    lastReceiptNumber = ds3.Tables("Orders").Rows(0).Item(0)

    dsNewRow1 = ds4.Tables("Delivery").NewRow()
    dsNewRow1.Item(0) = lastReceiptNumber
    dsNewRow1.Item(1) = txtDeliveryAddress.Text
    dsNewRow1.Item(3) = deliverydate
    dsNewRow1.Item(4) = "Processing"

    ds4.Tables("Delivery").Rows.Add(dsNewRow1)
    da4.Update(ds4, "Delivery")
    MsgBox("Item Bought!", MsgBoxStyle.OkOnly + MsgBoxStyle.Information)

End Sub

End Class

im having an error on the da2.Fill(ds2, "Order") can someone help me with this one? The error is syntax error insert into statement. I would really appreciate all those that would help. Thanks!

Your code has many issues. Please revise your coding style, try catching errors, try using the "using" method and let system close connection after your uses, try using parameters to avoid sql injections and finally please use prefix for your tables which will get rid of name conflicts! order is a keyword in access! — Krish
– Krish, Commented Sep 23, 2016 at 8:20
@krishKM: I assume you mean the Using statement. I strongly disagree your "prefix for your tables" suggestion. — onedaywhen
– onedaywhen, Commented Sep 23, 2016 at 12:49
@onedaywhen prefixing tables isn't bad idea as long as you are not depending on the prefix itself. Since ms access does not support schemas prefixing tables would be an advantage! — Krish
– Krish, Commented Sep 23, 2016 at 13:21

jmcilhinney · Accepted Answer · 2016-09-23 09:49:50Z

When you use a command builder to create the InsertCommand, UpdatedCommand and DeleteCommand for a data adapter, it will either take the same column names as you used in your SelectCommand or, if you used a wildcard (*) it will take the column names from the database table. If one of your column names is a reserved word or contains spaces or other special characters then the SQL generated will not be valid. There are three ways to deal with this:

Change your column names so that they are not reserved words or contain spaces or other special characters. This should be your first preference.
Don't use a wildcard in your query. If you specify each column explicitly then you will be forced to escape any reserved words, etc, and the command builder will do the same.
Set the QuotePrefix and QuoteSuffix properties of the command builder so that it escapes all column names.

If you can't go with option 1 then option 2 is strictly the better choice of the remainder but option 3 is easier. As you're using a Microsoft database, you would set those properties to "[" and "]" respectively.

Collectives™ on Stack Overflow

Syntax Error insert into statement error

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related