Patrick Gomez Patrick Gomez - 2 months ago 8
Vb.net Question

Syntax Error insert into statement error

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!

Answer

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:

  1. Change your column names so that they are not reserved words or contain spaces or other special characters. This should be your first preference.
  2. 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.
  3. 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.