OleDbExeption Was unhandled in VB.Net
- by ritch
Syntax error (missing operator) in query expression '((ProductID = ?) AND ((? = 1 AND Product Name IS NULL) OR (Product Name = ?)) AND ((? = 1 AND Price IS NULL) OR (Price = ?)) AND ((? = 1 AND Quantity IS NULL) OR (Quantity = ?)))'.
I need some help sorting this error out in Visual Basics.Net 2008. I am trying to update records in a MS Access Database 2008. I have it being able to update one table but the other table is just not having it.
Private Sub Admin_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Reads Users into the program from the text file (Located at Module.VB)
ReadUsers()
'Connect To Access 2007 Database File
con.ConnectionString = ("Provider=Microsoft.ACE.OLEDB.12.0;" & "Data Source=E:\Computing\Projects\Login\Login\bds.accdb;")
con.Open()
'SQL connect 1
sql = "Select * From Clients"
da = New OleDb.OleDbDataAdapter(sql, con)
da.Fill(ds, "Clients")
MaxRows = ds.Tables("Clients").Rows.Count
intCounter = -1
'SQL connect 2
sql2 = "Select * From Products"
da2 = New OleDb.OleDbDataAdapter(sql2, con)
da2.Fill(ds, "Products")
MaxRows2 = ds.Tables("Products").Rows.Count
intCounter2 = -1
'Show Clients From Database in a ComboBox
ComboBoxClients.DisplayMember = "ClientName"
ComboBoxClients.ValueMember = "ClientID"
ComboBoxClients.DataSource = ds.Tables("Clients")
End Sub
The button, the error appears on da2.update(ds, "Products")
Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click
Dim cb2 As New OleDb.OleDbCommandBuilder(da2)
ds.Tables("Products").Rows(intCounter2).Item("Price") = ProductPriceBox.Text
da2.Update(ds, "Products")
'Alerts the user that the Database has been updated
MsgBox("Database Updated")
End Sub
However the code works on updating another table
Private Sub UpdateButton_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles UpdateButton.Click
'Allows users to update records in the Database
Dim cb As New OleDb.OleDbCommandBuilder(da)
'Changes the database contents with the content in the text fields
ds.Tables("Clients").Rows(intCounter).Item("ClientName") = ClientNameBox.Text
ds.Tables("Clients").Rows(intCounter).Item("ClientID") = ClientIDBox.Text
ds.Tables("Clients").Rows(intCounter).Item("ClientAddress") = ClientAddressBox.Text
ds.Tables("Clients").Rows(intCounter).Item("ClientTelephoneNumber") = ClientNumberBox.Text
'Updates the table withing the Database
da.Update(ds, "Clients")
'Alerts the user that the Database has been updated
MsgBox("Database Updated")
End Sub
