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   
---------------------------

Answer 1

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
Cor

Answer 2

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)

Answer 3

And in that (beside the reply from Paul), a different database is also a primary key in the table you are updating?

---

Success
Cor

Answer 4

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)

Answer 5

Thanks Paul, but I already check the list of Microsoft Jet 4.0 reserved words, and I have none.

Answer 6

I checked the properties first database (which works) with that of the second database (that gives the error) and they are exactly alike. 

Answer 7

In that case, can we see what the Update (SQL) statement looks like after using the CommandBuilder?

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 8

And not more than 100 columns (seems rare if I see your updating but you never know) the commandbuilder does maximum 100 ones. 

 

---

Success
Cor

Answer 9

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"

--
Andrew

Answer 10

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,

Answer 11

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.

Answer 12

I updated the orginal post and included the eexception I ran.

Answer 13

I updated the orginal post and included the eexception I ran.

Answer 14

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

Answer 15

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.

Answer 16

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.

Answer 17

There could be a difference in SQL syntax parsing, although I have yet to see your SQL update statement.

---

Paul ~~~~ Microsoft MVP (Visual Basic)