Hi I have a project managing user accounts, After the user entered his details a password is generated and updated to his record. Whole project worked perfect except when it comes to updating a record. Pasted below is thesection which is giving me troubel.
The same SQL statement was pasted run from Access and updated the row perfectly. and when run from ASP.net
[UPDATE Users SET Password = '63780' WHERE IDcard = '888M']
the following OleDbException was thrown:
Message "Syntax error in UPDATE statement."
ErrorCode -2147217900 Integer
The code is pasted below. Can someone please tell me what is wrong
Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" connStr += Server.MapPath("~\data\Database.mdb") Dim queryStr As String = "UPDATE Users SET Password = '" + GenPassword.ToString + "' WHERE IDcard = '" + IdNumber.ToString + "'" Dim adapter As OleDbDataAdapter = New OleDbDataAdapter(queryStr, connStr) Dim Conn As OleDbConnection = New OleDbConnection(connStr) Dim cmd As OleDbCommand = New OleDbCommand(queryStr, Conn) TryConn.Open() cmd.ExecuteNonQuery() If Conn.State = ConnectionState.Open Then Conn.Close() Catch ex As OleDbExceptionConn.Close() Response.Redirect("Error.aspx") End Try |
Thank You
Syntax error in UPDATE statement
Morteza Hashemi
Hi,
Have you tried checking the GenPassword and IdNumber variables Check if it contains illegal characters such as a single quote. Also try renaming your table into Users1 or just enclose it in brackets [Users] in the query statement...
cheers,
Paul June A. Domag
androKLez
Something to consider is that I believe Password is a keyword in Access. That may be what is stopping the stored procedure from executing correctly.
ercilia hirata
never 'build' sql from input values from variables:
Dim queryStr As String = "UPDATE Users SET Password = where IDCard = "
Dim cmd As OleDbCommand = New OleDbCommand(queryStr, Conn)
cmd.Parameters(0).Value = GenPassword.ToString();
cmd.Parameters(1).Value = IdNumber.ToString();