sql = "Update tblUserAccount set username = '" & txtUsername.Text & "', password = '" & txtPassword.Text & "', AccessLevel = '" & cmbAccessLevel.Text & "', Attempts = '" & txtAttempts.Text & "', AccountStatus = '" & cmbAccountStatus.Text & "' where ID = '" & txtID.Text & "'"
cmd = New OleDbCommand(sql, cn)
cmd.ExecuteNonQuery()
The sql query works on Ms Access, but when I tried to run it in visual studio 2022 I receive this error code.
Your code to construct the SQL statement appears valid.
sql =
"
Update tblUserAccount
set username = '" & txtUsername.Text & "',
password = '" & txtPassword.Text & "',
AccessLevel = '" & cmbAccessLevel.Text & "',
Attempts = '" & txtAttempts.Text & "',
AccountStatus = '" & cmbAccountStatus.Text & "'
where ID = '" & txtID.Text & "'
"
The only way the resulting SQL statement could cause the error is if your data contains invalid characters. One way to avoid this is to use parameters. See Adding Parameters to OleDBConnection
sql = "UPDATE tblUserAccount SET
username = @Username,
password = @Password,
AccessLevel = @AccessLevel,
Attempts = @Attempts,
AccountStatus = @AccountStatus
WHERE ID = @ID"
Using cmd As New OleDb.OleDbCommand(sql, cn)
cmd.Parameters.AddWithValue("@Username", txtUsername.Text)
cmd.Parameters.AddWithValue("@Password", txtPassword.Text)
cmd.Parameters.AddWithValue("@AccessLevel", cmbAccessLevel.Text)
cmd.Parameters.AddWithValue("@Attempts", txtAttempts.Text)
cmd.Parameters.AddWithValue("@AccountStatus", cmbAccountStatus.Text)
cmd.Parameters.AddWithValue("@ID", txtID.Text)
cmd.ExecuteNonQuery()
End Using 'cmd
where ID = '" & txtID.Text & "'"ID is going into single quotes. Is it numeric?