asp.net - Access OLEDB reports "syntax error" but the statement looks correct

Question

I'm writing a simple page in ASP.net to update a password in a Access table. Here's syntax for a SELECT query that works:

    Dim dbConn As OleDbConnection
    Dim dbCommand As OleDbCommand
    Dim dbReader As OleDbDataReader

    'Connect to db
    dbConn = New OleDbConnection(Application("strConnectionString"))
    dbConn.Open()

    'Get user info
    strSQL = "SELECT * FROM users WHERE Username = '" & strUsername & "';"
    dbCommand = New OleDbCommand(strSQL, dbConn)
    dbReader = dbCommand.ExecuteReader()

And my connection string:

    Application("strConnectionString") = _
        "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strDBPath & _
            "; Jet OLEDB:Database Password=" & strDBPassword & ";"

The SELECT works fine, so I know my connection is OK. But this query gives me a syntax error:

strSQL = "UPDATE users SET Password = '1'"

With everything else the same, the ASP error says there is an error with my syntax. But when I response.write the strSQL line, it gives me this:

UPDATE users SET Password = '1' 

and when I paste this into a query editor in Access, the query updates all 'Password' field in the table to '1', so I know the syntax is OK. I tried it without the datareader and using dbCommand.ExecuteNonQuery(), same result.

I've got the permissions on the Access file set so that everybody has full control, so I don't think it's a permission problem.

Can anybody see my mistake? I'm really stuck. Thanks.

Answer 1

PASSWORD is a reserved word in Access SQL so you need to enclose it in square brackets if it is a column name.

strSQL = "UPDATE users SET [Password] = '1'"