Answered by:
Syntax error in UPDATE statement

Question
-
I using Visual Basic 2010 and MS Access 2010 database. My OS is Windows7. In my earlier complaint I had my database stored on root drive C: I changed that and the program worked fine. Using the exact same code I wrote another program using a different database, now I get an error stating "Syntax error in UPDATE statement", the only thing I did was change the name of the database. Again, Please Help.
Public Class Form1 Dim inc As Integer Dim MaxRows As Integer Dim con As New OleDb.OleDbConnection Dim dbProvider As String Dim dbSource As String Dim ds As New DataSet Dim da As New OleDb.OleDbDataAdapter Dim sql As String Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;" dbSource = "Data Source = C:\DataBaseII\AddressBook1.mdb" con.ConnectionString = dbProvider & dbSource con.Open() sql = "SELECT * FROM tblContacts" da = New OleDb.OleDbDataAdapter(sql, con) da.Fill(ds, "Addressbook1") 'MsgBox("Database is Open") con.Close() MaxRows = ds.Tables("AddressBook1").Rows.Count inc = -1 'MsgBox("Database is Close") End Sub Private Sub NavigateRecords() txtFirstName.Text = ds.Tables("AddressBook1").Rows(inc).Item(1) txtLastName.Text = ds.Tables("AddressBook1").Rows(inc).Item(2) txtAddress1.Text = ds.Tables("AddressBook1").Rows(inc).Item(3) txtAddress2.Text = ds.Tables("AddressBook1").Rows(inc).Item(4) txtAddress3.Text = ds.Tables("AddressBook1").Rows(inc).Item(5) txtPostCode.Text = ds.Tables("AddressBook1").Rows(inc).Item(6) End Sub Private Sub btnNext_Click(sender As System.Object, e As System.EventArgs) Handles btnNext.Click If inc <> MaxRows - 1 Then inc = inc + 1 NavigateRecords() Else MsgBox("No More Rows") End If End Sub Private Sub btnPrevious_Click(sender As System.Object, e As System.EventArgs) Handles btnPrevious.Click If inc > 0 Then inc = inc - 1 NavigateRecords() Else MsgBox("First Record") End If End Sub Private Sub btnLast_Click(sender As System.Object, e As System.EventArgs) Handles btnLast.Click If inc <> MaxRows - 1 Then inc = MaxRows - 1 NavigateRecords() End If End Sub Private Sub btnFirst_Click(sender As System.Object, e As System.EventArgs) Handles btnFirst.Click If inc <> 0 Then inc = 0 NavigateRecords() End If End Sub Private Sub btnUpdate_Click(sender As System.Object, e As System.EventArgs) Handles btnUpdate.Click Dim cb As New OleDb.OleDbCommandBuilder(da) ds.Tables("AddressBook1").Rows(inc).Item(1) = txtFirstName.Text ds.Tables("AddressBook1").Rows(inc).Item(2) = txtLastName.Text ds.Tables("AddressBook1").Rows(inc).Item(3) = txtAddress1.Text ds.Tables("AddressBook1").Rows(inc).Item(4) = txtAddress2.Text ds.Tables("AddressBook1").Rows(inc).Item(5) = txtAddress3.Text ds.Tables("AddressBook1").Rows(inc).Item(6) = txtPostCode.Text Try da.Update(ds, "AddressBook1") Catch ex As Exception MessageBox.Show(ex.ToString) End Try MsgBox("Data updated") End Sub Exception: --------------------------- --------------------------- System.Data.OleDb.OleDbException (0x80040E14): Syntax error in UPDATE statement. at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataSet dataSet, String srcTable) at DataBaseII.Form1.btnUpdate_Click(Object sender, EventArgs e) in C:\DataBaseII\DataBaseII\Form1.vb:line 99 --------------------------- OK ---------------------------
- Edited by jbhII Tuesday, September 13, 2011 6:19 AM
Thursday, September 1, 2011 4:19 PM
Answers
-
Not so strange as this code you show is for Access 2003 (I see you have edited your question, Paul, Andrew and Mike see this direct and probably even I would have seen that direct)
dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
So all of us have assumed it was Jet (Access 2003)
Change your connectionstring accoording to Access 2007/2010
http://www.connectionstrings.com/access-2007
Success
Cor
- Edited by Cor Ligthert Tuesday, September 13, 2011 8:17 AM
- Proposed as answer by Mike Feng Wednesday, September 14, 2011 7:03 AM
- Marked as answer by Mike Feng Tuesday, September 27, 2011 11:07 AM
Tuesday, September 13, 2011 8:14 AM
All replies
-
Change your code to this and see if the description is correct. If you have really investigated it and did not see it, than copy with Ctrl A, Ctrl C what is in the messagebox and show it to us here.
Private Sub UpdateToolStripMenuItem_Click(sender As System.Object, e As System.EventArgs) Handles UpdateToolStripMenuItem.Click Try Dim cb As New OleDb.OleDbCommandBuilder(da) ds.Tables("Db01").Rows(inc).Item(1) = txtFirstName.Text ds.Tables("Db01").Rows(inc).Item(2) = txtLastName.Text da.Update(ds, "Db01") MsgBox("Data updated") Catch ex As Exception MessageBox.Show(ex.ToString) End Try End Sub
Success
CorThursday, September 1, 2011 4:41 PM -
I can't see any of the column names in your UPDATE statement but I would suspect you are using a reserved word as a column name. Perhaps you could list out the column names in your Contacts table.
Paul ~~~~ Microsoft MVP (Visual Basic)Thursday, September 1, 2011 5:15 PM -
And in that (beside the reply from Paul), a different database is also a primary key in the table you are updating?
Success
Cor
- Edited by Cor Ligthert Friday, September 2, 2011 8:02 AM added text about Paul
Friday, September 2, 2011 7:43 AM -
Just a follow-up to my other post. If you are using any of the following keywords as column names then you should rename them.
List of Microsoft Jet 4.0 reserved words
Paul ~~~~ Microsoft MVP (Visual Basic)Friday, September 2, 2011 11:47 AM -
Thanks Paul, but I already check the list of Microsoft Jet 4.0 reserved words, and I have none.Friday, September 2, 2011 3:05 PM
-
I checked the properties first database (which works) with that of the second database (that gives the error) and they are exactly alike.Friday, September 2, 2011 3:13 PM
-
In that case, can we see what the Update (SQL) statement looks like after using the CommandBuilder?
Paul ~~~~ Microsoft MVP (Visual Basic)Friday, September 2, 2011 3:18 PM -
And not more than 100 columns (seems rare if I see your updating but you never know) the commandbuilder does maximum 100 ones.
Success
CorFriday, September 2, 2011 4:17 PM -
You might as well use the correct Windows directory separator character of "\" instead of "/", just to eliminate that as a possibility in
dbSource = "Data Source = C:/BHAddressBook/Db01.mdb"
--
AndrewFriday, September 2, 2011 6:54 PM -
Thank you all for your help and suggestions, after creating another database this time using Access 2003, my code above works perfectly. I believe the problem is with Access 2010 and I will be giving them a call.
Thanks again,
Friday, September 2, 2011 8:09 PM -
the Exception is as follows: System.Data.OleDb.OleDbException (0x80040E14): Syntax error in UPDATE statement. at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEve ntArgs rowUpdatedEvent, BatchCommandinfo[] batchCommand, Int32 commandCount) at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdagedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount) at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping) at System.DataCommon.DbDataAdapter.UpdateFromData Table (Data Table data Table, DataTableMapping tableMapping) at System.Data.Common.DbDataAdapter.Update(DataSet dataset, String src Table) At MonkeySee.Form1.btnUpdate_Click(Object sender, EventArgs e) in C:\User|Bernard and Alicia\AppData\Local\Temporary Projects\MondeySee\Form1.vb:line 91
Hi Jbh,
Welcome to the MSDN Forum.
In the call stack, we can see the error line is in the btnUpdate_Click subroutine, but we didn't see this sub in your orginal post.
You have said you just change the database name, so you can try to rename your working database name to your current one and test it with your current code. Please try to find out the differences of the two code snippets and two database, If you can list them here, it will help us to find the root cause more quickly.
Best regards,
Mike Feng [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Tuesday, September 6, 2011 5:58 AM -
I updated the orginal post and included the eexception I ran.Tuesday, September 13, 2011 6:24 AM
-
I updated the orginal post and included the eexception I ran.Tuesday, September 13, 2011 6:24 AM
-
Not so strange as this code you show is for Access 2003 (I see you have edited your question, Paul, Andrew and Mike see this direct and probably even I would have seen that direct)
dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
So all of us have assumed it was Jet (Access 2003)
Change your connectionstring accoording to Access 2007/2010
http://www.connectionstrings.com/access-2007
Success
Cor
- Edited by Cor Ligthert Tuesday, September 13, 2011 8:17 AM
- Proposed as answer by Mike Feng Wednesday, September 14, 2011 7:03 AM
- Marked as answer by Mike Feng Tuesday, September 27, 2011 11:07 AM
Tuesday, September 13, 2011 8:14 AM -
I am not using the .accdb extention, I am using .mdb extention, would that matter when converting the new connectionstring? I will try it, Thanks.Wednesday, September 14, 2011 11:19 PM
-
Hi Jbh,
Yes, if you try to connect access 2003, the provider should be Microsoft.Jet.OLEDB.4.0, and if you try to connect access 2007/2010, you need to use Microsoft.ACE.OLEDB.12.0.
Please try it again.
Best regards,
Mike Feng [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.
Friday, September 16, 2011 8:36 AM -
There could be a difference in SQL syntax parsing, although I have yet to see your SQL update statement.
Paul ~~~~ Microsoft MVP (Visual Basic)Friday, September 16, 2011 1:28 PM