locked
Syntax error in UPDATE statement RRS feed

  • 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
    Cor
    Thursday, 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
    Cor
    Friday, 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"

    --
    Andrew

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