Select & Update at the same table at same time

Discussion in 'ASP General' started by andri.wardhana@gmail.com, Nov 9, 2005.

  1. Guest

    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.
     
    , Nov 9, 2005
    #1
    1. Advertising

  2. AlanM Guest

    "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


    <> wrote in message
    news:...
    > 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, Nov 9, 2005
    #2
    1. Advertising

  3. Guest

    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='9834876dcfb05cb167a5c24953eba58c4ac89b1adf57f28f2f9d09af107ee8f0'
    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
     
    , Nov 9, 2005
    #3
  4. 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='9834876dcfb05cb167a5c24953eba58c4ac89b1adf57f28f2f9d09af107ee8f0'
    > 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&lr=&ie=UTF-8&oe=UTF-8&selm=

    http://groups.google.com/groups?hl=...=1&selm=

    http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/b3d322b882a604bd

    or by using a Command object to pass parameters to a string containing ODBC
    parameter markers:
    http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

    --
    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], Nov 9, 2005
    #4
  5. Guest

    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
     
    , Nov 10, 2005
    #5
  6. 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], Nov 10, 2005
    #6
  7. Guest

    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:
    > 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"
     
    , Nov 11, 2005
    #7
  8. 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"
     
    Bob Barrows [MVP], Nov 11, 2005
    #8
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Hartmut Schroth
    Replies:
    3
    Views:
    3,505
    Hartmut Schroth
    Dec 1, 2003
  2. flamesrock
    Replies:
    8
    Views:
    475
    Hendrik van Rooyen
    Nov 24, 2006
  3. rob merritt
    Replies:
    0
    Views:
    188
    rob merritt
    Mar 1, 2005
  4. palmiere
    Replies:
    1
    Views:
    413
    Erwin Moller
    Feb 9, 2004
  5. Replies:
    2
    Views:
    186
Loading...

Share This Page