CODEWITHSUNDEEP
vb.netms-access
Justin E.
Justin E.

Reputation: 18

VB.net Access Update Query

VB.net access update query is giving a Syntax Error in Update Query Error. My query is as follows:

query = "UPDATE users SET username='" & newUsername & "', password='" & newPassword & "', department='" & newDepartment & "', display_name='" & newDisplayName & "', email='" & newEmail & "', extension='" & newExtension & "', access_level='" & newAccessLevel & "' WHERE id=" & usrID

None of the above variables have any symbols at all. What am I doing wrong?

::UPDATE::

UPDATE users SET username='alison', password='farm1234',department='1',display_name='Alison *****', email='production@**********.com', extension='1012',access_level='50' WHERE id=1

This is what the query runs as.

Upvotes: 0

Views: 35498

Answers (2)

senthilkumar2185
senthilkumar2185

Reputation: 2566

   ConStr()
    Qry="UPDATE users SET username=@uname, [password]=@pass, department=@dept, " & _
            "display_name=@dnam, email=@email, extension=@ext, access_level=@acslvl" & _
            " WHERE id=@id"
    cmd = new oledbcommand(Qry,Conn)
    cmd.Parameters.AddWithValue("@uname",newUsername)
    cmd.Parameters.AddWithValue("@pass",newPassword)
    cmd.Parameters.AddWithValue("@dept",newDepartment)
    cmd.Parameters.AddWithValue("@dnam",newDisplayName)
    cmd.Parameters.AddWithValue("@email",newEmail)
    cmd.Parameters.AddWithValue("@ext",newExtension)
    cmd.Parameters.AddWithValue("@acslvl",newAccessLevel)
    cmd.Parameters.AddWithValue("@id",usrID)
    cmd.ExecuteNonQuery()

Upvotes: 0

Steve
Steve

Reputation: 216263

The error is caused by the usage of the reserved keyword PASSWORD without enclosing it in square brackets.

Said that, you never use string concatenation to build sql commands, but always a parameterized query to avoid Sql Injection problems but also syntax error in parsing text values (containing single quotes) or decimal values with their decimal separators or dates values.

So, a possible approach to your task could be

query = "UPDATE users SET username=?, [password]=?, department=?, " & _
        "display_name=?, email=?, extension=?, access_level=?" & _
        " WHERE id=?"

Using cmd = new OleDbCommand(query, connection)
   cmd.Parameters.AddWithValue("@p1", newUsername)
   cmd.Parameters.AddWithValue("@p2", newPassword)   
   cmd.Parameters.AddWithValue("@p3", newDepartment)   
   cmd.Parameters.AddWithValue("@p4", newDisplayName)
   cmd.Parameters.AddWithValue("@p5", newEmail)
   cmd.Parameters.AddWithValue("@p6", newExtension)
   cmd.Parameters.AddWithValue("@p7", newAccessLevel)   
   cmd.Parameters.AddWithValue("@p8", usrID)
   cmd.ExecuteNonQuery()

End Using

Keep in mind that OleDb doesn't use the parameter names to find the corresponding placeholder in sql command text. Instead it uses a positional progression and thus adding the parameters to the collection should respect the order in which the parameter appears in the sql command text

Upvotes: 2

Related Questions