Syntax error in Update statement against Access database (using OleDbCommandBuilder)

Question

I have been working on this issue for ages! I have an MSAccess 2007 db and I am using Visual Studio 2010 and Visual Basic to write an application that reads and writes from the database. The read works just fine but when I try and write an updated password back into the database, it fails. Initially I had just a plain 'Syntax Error' which wasn't that helpful, but with a bit of research I noted that access databases seem to have an issue with columns called password. I renamed that and tried again, now I get this error when the

da.Update(ds,"All_Users")

command is executed.

The full error message from VS is : Syntax error (missing operator) in query expression '((ID = ?) AND ((? = 1 AND Forename IS NULL) OR (Forename = ?)) AND ((? = 1 AND Surname IS NULL) OR (Surname = ?)) AND ((? = 1 AND User_Level IS NULL) OR (User_Level = ?)) AND ((? = 1 AND Last Logon IS NULL) OR (Last Logon = ?)) AND ((? = 1 AND Allow IS NU'.

My code is as follows:

Private Sub btnSave_Click(snder as System.Object, e As System.EventArgs) Handles btnSave.Click
    Dim Con As New OleDb.OleDbConnection
    Dim ConString As String
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim Sql As String = "SELECT * FROM tblUsers"
    '
    ConString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
        Application.StartupPath & "\Data\Users.accdb;Jet OLEDB:Database Password=---------;"
    Con.ConnectionString = ConString
    Con.Open()
    da = New OleDb.OleDbDataAdapter(Sql, Con)
    da.Fill(ds, "All_Users")
    'Now loop through the records until you find the one for this user
    For i = 0 To ds.Tables("All_Users").Rows.Count - 1
        If ds.Tables("All_Users").Rows(i).Item(0).ToString = CurrentUser.ID Then
            ds.Tables("All_Users").Rows(i).Item(6) = txtConfirmPassword.Text
        End If
    Next
    CurrentUser.Password = txtConfirmPassword.Text
    '
    Dim cb As New OleDb.OleDbCommandBuilder(da)
    da.Update(ds, "All_Users")
    '
    Con.Close()
    MessageBox.Show("Your password has been sucessfully updated.", "Success", _
                    MessageBoxButtons.OK, MessageBoxIcon.Information, MessageBoxDefaultButton.Button1)
    Me.Close()

It seems you have a field named Last Logon. Rename that field to Last_Logon (to eliminate the space) and try your code again. — HansUp
– HansUp, Commented Nov 18, 2013 at 16:49

Gord Thompson · Accepted Answer · 2013-11-18 17:13:46Z

2

When using OleDbCommandBuilder always set the .QuotePrefix and .QuoteSuffix properties:

Dim cb As New OleDb.OleDbCommandBuilder(da)
cb.QuotePrefix = "["
cb.QuoteSuffix = "]"

answered Nov 18, 2013 at 17:13

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Syntax error in Update statement against Access database (using OleDbCommandBuilder)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related