Insert Statment Problem....

[ncozzolino]

Having a problem with my change password code. It bombs on the INSERT statement.....I get an error stating thier is a syntax error in my insert statement but I can't figure out where. The delete statement works fine and yes all the variables are getting passed. A little help would be greatly appreciated. Thanks!





<%

dim curname, curold, curnew, curconfirm
curname=request.QueryString("txtname")
curold=request.QueryString("txtold")
curnew=request.QueryString("txtnew")
curconfirm=request.QueryString("txtconfirm")
%>
<%
If curnew=curconfirm Then
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source='c:\database\qa.mdb' ;Persist Security Info=False"
set dbConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")
dbConn.Open strConnect

strSQL = "SELECT * FROM userTable WHERE password='" & curold & "'" & "AND username='" & curname & "'"

objRS.Open strSQL, dbConn

If objRS.BOF And objRS.EOF then

response.write "The username or old password was typed incorrectly, please <a href='" & "changepassword.asp" & "'>" & "try again." & "</a>"

Else

objRS.Close

strDSQL = "DELETE FROM userTable WHERE password='" & curold & "'"

strISQL = "INSERT into userTable(username, password) values('" & curname & "', '" & curnew & "')" <--Problem here

objRS.Open strDSQL, dbconn
objRS.Open strISQL, dbconn

end if

Else

Response.write "The password in the new and confirm box do not match. Please <a href='" & "changepassword.asp" & "'>" & "try again." & "</a>"

End If
%>

[russell]

Why not just do an update?
Change this:

strDSQL = "DELETE FROM userTable WHERE password='" & curold & "'"

strISQL = "INSERT into userTable(username, password) values('" & curname & "', '" & curnew & "')" <--Problem here

objRS.Open strDSQL, dbconn
objRS.Open strISQL, dbconn

TO

sql = "UPDATE userTable SET password = '" & curnew & "' " &_
"WHERE username = '" & curname & "' " &_
"AND password = '" & curold & "'"

dbConn.Execute(sql)

Also be sure to escape single quotes in the fields passed to the db.

[ncozzolino]

I did that and it gave me

Microsoft JET Database Engine (0x80040E14)
Syntax error in UPDATE statement.
/drintranet/changepassprocess.asp, line 28

line 28 = dbConn.Execute(sql)

[russell]

do this

sql = "UPDATE userTable SET password = '" & curnew & "' " &_
"WHERE username = '" & curname & "' " &_
"AND password = '" & curold & "'"

Response.Write sql
Response.End
dbConn.Execute(sql)


What gets written out?

[ncozzolino]

UPDATE userTable SET password = '1234' WHERE username = 'ncozzolino' AND password = 'alexis4598'

That looks correct.....database problem???

[russell]

Try executing the sql directly in Access?

Also, try wrapping brackets around the field names (Can't remember off the top of my head if username and password are reserved in msaccess)

UPDATE userTable SET [password] = '1234' WHERE [username] = 'ncozzolino' AND [password] = 'alexis4598'

[ncozzolino]

The brackets worked....THANKS!!!

[russell]

very glad to help. don't forget to change your password now! :)