Please help me update records of access filenames "Data.accdb"in Vb.net using oledb connection

Question

Getting Error While Updating Record.

Firs of all i select a row from Datagrid then selected row's values displayed to textbox then i click update button to update record. After entered New usename and password click save Buton then getting error as follow.

ERROR
system.data.oledb.oledbException(0x80040e14):syntax error in UPDATE statement.
at
system.data.oledb.oledbCommand.executeCommandTextErrorHandling(Oledb Hresult hr)
at
System.data.Oledb.OledbCommand.ExecutecommandTextforSingleResult(tagDBPARAMS dbParams,
Object& executeResult)
at
System.data.Oledb.OledbCommand.Executecommand(CommandBehavior Behavior, String method)
at System.data.Oledb.OledbCommand.ExecuteNonQuery()
at Nakul.frmusers.cmdsave_click(object sender, EventArgs e)in
E:\kul....\frmusers.vb:line 152

Note : I did not want to update the userid.

Data.accdb // Access File Name
table Name : users
Design View: userid   Number     // primary key
                      username   text
                      password   text

Imports System.Data
Imports System.Data.OleDb
public class users

Dim str As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=E:\kul\Data.accdb;Persist Security Info=False;"

Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click

    Dim conn As New OleDbConnection(str)
    Dim qry As New OleDbCommand("update users set username='" & txtusername.Text & "', password='" & txtpassword.Text & "' where userid=" & txtuserid.Text, conn)

            Try
                conn.Open()
                qry.ExecuteNonQuery()       // Error Line No 152 in Coading
                conn.Close()
                MsgBox("Record Updated")
            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
 End Sub
End class

Answer 1

The main reason for the Syntax error is the word PASSWORD. It is a reserved keyword in Access and thus you need to encapsulate it with square brackets

Said that, you need to change you query and use a parameterized approach and not a string concatenation. Building your query concatenating strings could be another source of syntax errors (a user name that contains a single quote?) and open the door to sql injection attacks

Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click

    dim query = "update users set username=?, [password]=? where userid=?"
    Using conn = new OleDbConnection(str)
    Using qry = New OleDbCommand(query, conn)
        qry.Parameters.AddWithValue("@p1",txtusername.Text )
        qry.Parameters.AddWithValue("@p2",txtpassword.Text )
        qry.Parameters.AddWithValue("@p3",Convert.ToInt32(txtuserid.Text))
        conn.Open()
        qry.ExecuteNonQuery()       
        conn.Close()
        MsgBox("Record Updated")
    End Using
    End Using
 End Sub

Also note the using statement that ensure the correct closing and disposing of the connection and the command.