Select & Update at the same table at same time

[andri.wardhana]

Hi Guys,

I have a problem with my ASP file. since I'm all new in ASP, i found
that the error statement generated by ASP is confusing. basically what
I want to do in this script is ability to change password. Currently
this script's running well in the PHP version ( I rewrote the entire
PHP app to ASP app):
1. The form contains these fields : old password, new passwd, confirm
new passwd. users have to input old passwd in order to change their
passwd.
2. then the ASP script check old password against tbl_users for that
userid. if found, the script then update password for that user.

I thought this would be easy, since I have no problem running it on
PHP. but when I did it for ASP, I had this following error :
Syntax error in update statement

This is my (horribly) simple code :

oldpasswd = Request.Form("oldpasswd")
newpasswd = Request.Form("newpasswd")
userid = SESSION("userid")
query = "SELECT password FROM tbl_users WHERE userid='" & userid & "'"
set RSusers = dataConn.Execute(query)
referer = Request.Form("referer")
storedpwd = RSusers("password")
if sha256(oldpasswd) = storedpwd then
newencrypted = sha256(newpasswd)
kueri_update = "UPDATE tbl_users SET password='" & newencrypted & "'
WHERE NPK=" & SESSION("npk")
dataConn.Execute(kueri_update)
dataConn.close
Response.Redirect(referer & ".asp")
else
Response.Redirect(referer & ".asp" & "?page=changepasswd&err=on")
END IF
Thank you for your helps.

[AlanM]

"UPDATE tbl_users SET password='" & newencrypted & "'
WHERE NPK=" & SESSION("npk")

my guess is that NPK is not a numeric field in your database, so you need to
use quotes.

or one of the variables used is empty at time of execution
<an************@gmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

Hi Guys,

I have a problem with my ASP file. since I'm all new in ASP, i found
that the error statement generated by ASP is confusing. basically what
I want to do in this script is ability to change password. Currently
this script's running well in the PHP version ( I rewrote the entire
PHP app to ASP app):
1. The form contains these fields : old password, new passwd, confirm
new passwd. users have to input old passwd in order to change their
passwd.
2. then the ASP script check old password against tbl_users for that
userid. if found, the script then update password for that user.

I thought this would be easy, since I have no problem running it on
PHP. but when I did it for ASP, I had this following error :
Syntax error in update statement

This is my (horribly) simple code :

oldpasswd = Request.Form("oldpasswd")
newpasswd = Request.Form("newpasswd")
userid = SESSION("userid")
query = "SELECT password FROM tbl_users WHERE userid='" & userid & "'"
set RSusers = dataConn.Execute(query)
referer = Request.Form("referer")
storedpwd = RSusers("password")
if sha256(oldpasswd) = storedpwd then
newencrypted = sha256(newpasswd)
kueri_update = "UPDATE tbl_users SET password='" & newencrypted & "'
WHERE NPK=" & SESSION("npk")
dataConn.Execute(kueri_update)
dataConn.close
Response.Redirect(referer & ".asp")
else
Response.Redirect(referer & ".asp" & "?page=changepasswd&err=on")
END IF
Thank you for your helps.

[andri.wardhana]

Thanks Alan for the quick response.

I've checked that possibility and none of variables are empty at the
time of execution. Here's the output of SQL query when I response.write
it :
UPDATE tbl_users SET
password='9834876dcfb05cb167a5c24953eba58c4ac89b1a df57f28f2f9d09af107ee8f0'
WHERE NPK=989
the NPK field is already a number type in db (I use access). Is it
possible that there is a lock mechanism in Access that prevent updating
table when other query read the same table in the same page?

Thanks again :D

[Bob Barrows [MVP]]

an************@gmail.com wrote:

Thanks Alan for the quick response.

I've checked that possibility and none of variables are empty at the
time of execution. Here's the output of SQL query when I
response.write
it :
UPDATE tbl_users SET
password='9834876dcfb05cb167a5c24953eba58c4ac89b1a df57f28f2f9d09af107ee8f0'
WHERE NPK=989
the NPK field is already a number type in db (I use access). Is it
possible that there is a lock mechanism in Access that prevent
updating table when other query read the same table in the same page?

No, especially when you use the Execute method to open the recordset: this
will default to a read-only, forward-only cursor, so, no locks.

You are more probably facing a "reserved keyword" problem. If you look here:
http://www.aspfaq.com/show.asp?id=2080
You will see that "password" is a reserved keyword wihich should be avoided
for your db object names.

My suggestion would be to change the name of the field, but, if you can't
for some reason, you will need to remember to enclose it in brackets []
whenever you use it in a query run via ADO.

With that out of the way, you need to be aware that by using dynamic sql
(using concatenation to build sql statements) you are leaving your database
and site vulnerable to hackers using sql injection:
http://mvp.unixwiz.net/techtips/sql-injection.html
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

The best way to defeat sql injection is to use parameters, either via saved
parameter queries (my preferred technique):
http://www.google.com/groups?hl=en&l...TNGP12.phx.gbl

http://groups.google.com/groups?hl=e...tngp13.phx.gbl

http://groups-beta.google.com/group/...d322b882a604bd

or by using a Command object to pass parameters to a string containing ODBC
parameter markers:
http://groups-beta.google.com/group/...e36562fee7804e

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[andri.wardhana]

It is indeed the problem. I've change the field name and everything
goes fine :D. pheww, thanks Bob, really appreciate it, and also thanks
for the advice.

but it is funny to think that the PHP version (also use Access thru
ODBC), didn't encounter the same problem.

Thanks again :D

[Bob Barrows [MVP]]

an************@gmail.com wrote:

It is indeed the problem. I've change the field name and everything
goes fine :D. pheww, thanks Bob, really appreciate it, and also thanks
for the advice.

but it is funny to think that the PHP version (also use Access thru
ODBC), didn't encounter the same problem.

Does PHP use ADO?

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[andri.wardhana]

I guess not. PHP connects to Access via ODBC, so I think it lacks of
ADO functionality. (pardon me, I'm not so knowledgeable on ADO thingy)

So, maybe because of that PHP can use ADO's reseved words as field's
name, although it uses Access as database. Right?

Thanks for shed some lights here. (and sorry for top posting, won't
happen again :D)

Bob Barrows [MVP] wrote:

an************@gmail.com wrote:

It is indeed the problem. I've change the field name and everything
goes fine :D. pheww, thanks Bob, really appreciate it, and also thanks
for the advice.

but it is funny to think that the PHP version (also use Access thru
ODBC), didn't encounter the same problem.

Does PHP use ADO?

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

[Bob Barrows [MVP]]

an************@gmail.com wrote:

I guess not. PHP connects to Access via ODBC, so I think it lacks of
ADO functionality. (pardon me, I'm not so knowledgeable on ADO thingy)

You can find the ADO API Reference at msdn.microsoft.com/library
So, maybe because of that PHP can use ADO's reseved words as field's
name, although it uses Access as database. Right?
Sort of. ADO enforces a different set of reserved keywords than the set of
keywords enforced by ODBC on its own.

Thanks for shed some lights here. (and sorry for top posting, won't
happen again :D)

Not a problem for me.
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"