Update Query in Visual Basic Express 2010

Question

I'm trying to update an Access 2003 database using Visual Basic Express 2010 via SQL, I have so far got SELECT, DELETE and INSERT queries to work, but update will not...

con.ConnectionString = dbProvider & dbSource
        con.Open()                                          'Open connection to the database
        sqlstatement = "UPDATE users SET password = '" & NewPassword & "' WHERE USERID = " & ID & ";"
        Dim dc As New OleDb.OleDbCommand(sqlstatement, con)
        dc.ExecuteNonQuery()
        con.Close()

Like I said, all other statements work, the error produced is:

https://i.stack.imgur.com/acFBT.png

Thank you!

score 0 · Accepted Answer · edited May 23 '17 at 12:05

The first problem is the word PASSWORD. It is a reserved keyword in MS-Access database. If you want to use it you should enclose it in square brackets.

Said that, please start using a parameterized query and not a string concatenation when you work with any type of database

So your code should be:

sqlstatement = "UPDATE users SET [password] = ? WHERE USERID = ?"
Using con = new OleDbConnection(dbProvider & dbSource)
Using dc = new OleDbCommand(sqlstatement, con)
    con.Open()
    dc.Parameters.AddWithValue("@p1", NewPassword)
    dc.Parameters.AddWithValue("@p2", ID)
    dc.ExecuteNonQuery()
End Using
End Using

You could read about the importance of Parameterized Queries and Sql Injection in many places, this link is a most famous one to start with

Update Query in Visual Basic Express 2010

1 Answers1