UPDATE record

Discussion in 'ASP General' started by David, Sep 3, 2007.

  1. David

    David Guest

    Hi,

    I have an asp page for which I am trying to update a record, but keep
    getting errors in my SQL:::::

    todate = request.form("todate")
    notes = request.form("notes")
    job = Session("JOB_ADJ")
    sid = Session("SID")


    dt=date()
    yy = Year(dt)
    mm = Month(dt)
    dd = Day(dt)
    v_date = yy & "/" & mm & "/" & dd


    ShipD=todate
    yy = Year(ShipD)
    mm = Month(ShipD)
    dd = Day(ShipD)
    S_date = yy & "/" & mm & "/" & dd

    uSQL = "SELECT * FROM PCBForecast WHERE PCBForecastID = " &
    Session("SID") & ""
    Set RS = adoDataConn.Execute(uSQL)


    sql = "UPDATE PCBForecast"
    sql = sql & " SET ShipQty = " & RS("ShipQty") & ","
    sql = sql & " ShipETA = " & S_date & ","
    sql = sql & " Notes = '" & RS("Notes") & "',"
    sql = sql & " Entrydate = " & v_date & ","

    sql = sql & " WHERE PCBForecastID = "&sid&""


    set RS2 = adoDataConn.Execute(sql)



    What is wrong with the above code ?

    Thanks

    David
     
    David, Sep 3, 2007
    #1
    1. Advertising

  2. David wrote:
    > Hi,
    >
    > I have an asp page for which I am trying to update a record, but keep
    > getting errors in my SQL:::::
    >
    > todate = request.form("todate")
    > notes = request.form("notes")
    > job = Session("JOB_ADJ")
    > sid = Session("SID")
    >
    >
    > dt=date()
    > yy = Year(dt)
    > mm = Month(dt)
    > dd = Day(dt)
    > v_date = yy & "/" & mm & "/" & dd
    >
    >
    > ShipD=todate
    > yy = Year(ShipD)
    > mm = Month(ShipD)
    > dd = Day(ShipD)
    > S_date = yy & "/" & mm & "/" & dd
    >
    > uSQL = "SELECT * FROM PCBForecast WHERE PCBForecastID = " &
    > Session("SID") & ""
    > Set RS = adoDataConn.Execute(uSQL)
    >
    >
    > sql = "UPDATE PCBForecast"
    > sql = sql & " SET ShipQty = " & RS("ShipQty") & ","
    > sql = sql & " ShipETA = " & S_date & ","
    > sql = sql & " Notes = '" & RS("Notes") & "',"
    > sql = sql & " Entrydate = " & v_date & ","
    >
    > sql = sql & " WHERE PCBForecastID = "&sid&""
    >
    >
    > set RS2 = adoDataConn.Execute(sql)
    >
    >
    >
    > What is wrong with the above code ?
    >


    I can't tell. Here is a list of the things you did not tell us:
    1. database type and version
    2. Datatypes of the fields involved in that update statement
    3. The result of that string concatenation - we cannot debug a sql statement
    without knowing what it is. You need to find out what it is by using
    "response.write sql", running the page, and looking at the statement in the
    browser window. This is usually enough to determine the problem. If not, you
    should copy the statement from the browser window and use the query
    execution tool of whatever database you are using to attempt to run it - you
    will usually get a more informative error message. if your database's query
    execution tool provides a query builder, then use the query builder to
    create a statement that does what you want this statement to do, and compare
    the result with the statement you built in your vbscript code. If none of
    this helps, provide the information I requested in a followup post.

    Further points to consider:
    Your use of dynamic sql is leaving you vulnerable to hackers using sql
    injection:
    http://mvp.unixwiz.net/techtips/sql-injection.html
    http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23

    See here for a better, more secure way to execute your queries by using
    parameter markers:
    http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

    Personally, I prefer using stored procedures,
    SQL Server:

    http://groups.google.com/group/microsoft.public.inetserver.asp.general/msg/5d3c9d4409dc1701?hl=en&

    or saved parameter queries as they are known in Access:

    Access:
    http://www.google.com/groups?hl=en&lr=&ie=UTF-8&oe=UTF-8&selm=

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




    --
    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], Sep 3, 2007
    #2
    1. Advertising

  3. David

    Lasse Edsvik Guest

    David,

    Response.Write is your friend,


    put Response.Write(sql) above "Set Rs2 = ....." to see what the sql-query
    you're trying to execute looks like. I assume you see 2 things are wrong
    with that query (if I get the datatypes right from what the columns are
    called)

    you have a comma before WHERE, remove that, EntryDate (which I assume is a
    datetime datatype) should have ' around its value, i.e Entrydate = '" &
    v_date & "'"


    /Lasse


    "David" <> wrote in message
    news:...
    > Hi,
    >
    > I have an asp page for which I am trying to update a record, but keep
    > getting errors in my SQL:::::
    >
    > todate = request.form("todate")
    > notes = request.form("notes")
    > job = Session("JOB_ADJ")
    > sid = Session("SID")
    >
    >
    > dt=date()
    > yy = Year(dt)
    > mm = Month(dt)
    > dd = Day(dt)
    > v_date = yy & "/" & mm & "/" & dd
    >
    >
    > ShipD=todate
    > yy = Year(ShipD)
    > mm = Month(ShipD)
    > dd = Day(ShipD)
    > S_date = yy & "/" & mm & "/" & dd
    >
    > uSQL = "SELECT * FROM PCBForecast WHERE PCBForecastID = " &
    > Session("SID") & ""
    > Set RS = adoDataConn.Execute(uSQL)
    >
    >
    > sql = "UPDATE PCBForecast"
    > sql = sql & " SET ShipQty = " & RS("ShipQty") & ","
    > sql = sql & " ShipETA = " & S_date & ","
    > sql = sql & " Notes = '" & RS("Notes") & "',"
    > sql = sql & " Entrydate = " & v_date & ","
    >
    > sql = sql & " WHERE PCBForecastID = "&sid&""
    >
    >
    > set RS2 = adoDataConn.Execute(sql)
    >
    >
    >
    > What is wrong with the above code ?
    >
    > Thanks
    >
    > David
    >
     
    Lasse Edsvik, Sep 4, 2007
    #3
  4. Lasse wrote on Tue, 4 Sep 2007 10:57:58 +0200:

    > David,


    > Response.Write is your friend,



    > put Response.Write(sql) above "Set Rs2 = ....." to see what the
    > sql-query you're trying to execute looks like. I assume you see 2
    > things are wrong with that query (if I get the datatypes right from
    > what the columns are called)


    > you have a comma before WHERE, remove that, EntryDate (which I assume
    > is a datetime datatype) should have ' around its value, i.e Entrydate =
    > '" &
    > v_date & "'"


    ShipETA also requires quoting as it's a date too.

    Dan
     
    Daniel Crichton, Sep 6, 2007
    #4
    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. =?Utf-8?B?SnVzdGlu?=

    Retrieving Record Key while creating the record.

    =?Utf-8?B?SnVzdGlu?=, Oct 4, 2004, in forum: ASP .Net
    Replies:
    4
    Views:
    666
    =?Utf-8?B?SnVzdGlu?=
    Oct 5, 2004
  2. André
    Replies:
    0
    Views:
    3,784
    André
    Jun 25, 2006
  3. MRW
    Replies:
    0
    Views:
    400
  4. THurkmans
    Replies:
    2
    Views:
    624
  5. Jimmy Tran
    Replies:
    1
    Views:
    255
    Dan Brussee
    Oct 3, 2003
Loading...

Share This Page