Multi Update with If Else Statement ... error

Discussion in 'ASP General' started by bcap, Aug 4, 2009.

  1. bcap

    bcap Guest

    Hi,

    I am trying to create an update statement that works well until I try
    to make a IF THEN ELSE decision to determine the status update. Below
    is my code. I would sincerely appreciate any thoughts and/or
    suggestions ...



    DID = Split(Request.Form("DID"),",")
    CongratsLetDt = Split(Request.Form("CongratsLetDt"),",")
    ShipRefNo = Split(Request.Form("ShipRefNo"),",")
    dshipmentdate = Split(Request.Form("dshipmentdate"),",")


    StatusA = Split("0",",")
    StatusB = Split("1",",")
    StatusC = Split("2",",")

    Dim id
    For id=0 to uBound(DID)

    sql_update = "UPDATE DetailRecs"
    sql_update = sql_update & " SET "',"
    sql_update = sql_update & "CongratsLetDt='" & trim(CongratsLetDt(id))
    & "',"

    If trim(dshipmentdate(id))="" then
    sql_update = sql_update & "dshipmentdate='00000000',"
    Else
    sql_update = sql_update & "dshipmentdate='" & trim(dshipmentdate(id))
    & "',"
    End If

    If trim(ShipRefNo(id))="" then
    Else
    sql_update = sql_update & "dshipmentrefno='700900800000645" & trim
    (ShipRefNo(id)) & "',"
    End if

    sql_update = sql_update & "ShipRefNo='" & trim(ShipRefNo(id)) & "',"

    ' Status Types
    ' 0 - New
    ' 1 - Working
    ' 2 - Pending Finance
    ' 3 - Approved
    ' 4 - Fulfilled

    If ((trim(CongratsLetDt(id))="" AND (trim(dshipmentdate(id))
    ="00000000" OR trim(dshipmentdate(id))="") AND trim(ShipRefNo(id))
    ="")) then
    sql_update = sql_update & "Status='" & StatusA(id) & "'"
    Else
    End If

    If (trim(CongratsLetDt(id))="" OR trim(ShipRefNo(id))="" OR (trim
    (dshipmentdate(id))="00000000" OR trim(dshipmentdate(id))="")) then
    sql_update = sql_update & "Status='" & trim(StatusB(id)) & "'"
    Else
    End If

    If (trim(CongratsLetDt(id))<>"" AND trim(ShipRefNo(id))<>"" AND (trim
    (dshipmentdate(id))<>"" OR trim(dshipmentdate(id))<>"00000000")) then
    sql_update = sql_update & "Status='" & StatusC(id) & "'"
    Else
    End If

    sql_update = sql_update & "WHERE DID =" & DID(id)

    Set rs = conn.Execute(sql_update)
    bcap, Aug 4, 2009
    #1
    1. Advertising

  2. bcap

    Bob Barrows Guest

    bcap wrote:
    > Hi,
    >
    > I am trying to create an update statement that works well until I try
    > to make a IF THEN ELSE decision to determine the status update. Below
    > is my code. I would sincerely appreciate any thoughts and/or
    > suggestions ...
    >


    It would help to know what the failure is. Error message? What is
    telling you what you have isn't "working well"?

    >

    Snip>
    > sql_update = "UPDATE DetailRecs"


    You cannot troubleshoot a sql statement without knowing what it is.

    Response.Write sql_update
    Response.End

    comment out those two lines when finished debugging.
    Run the page and look at the statement. does the error stand out? If
    not, copy and paste it into the query execution environment for whatever
    database you are using and try to run it. You maight get a more
    informative error message.
    Still stuck? Show us the statement.

    >
    > Set rs = conn.Execute(sql_update)


    Why in the world are you opening a recordset on a query that does not
    return records?!? Simply do this:

    conn.Execute sql_update,,129

    The 129 tells ADO that the sql statement won't return records which
    makes it skip the step of creating an implicit recordset to receive the
    resultset.

    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 (tokens):
    http://groups-beta.google.com/group/microsoft.public.inetserver.asp.db/msg/72e36562fee7804e

    Personally, I prefer using stored procedures, 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=

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



    --
    HTH,
    Bob Barrows
    Bob Barrows, Aug 4, 2009
    #2
    1. Advertising

  3. bcap

    bcap Guest

    Thank you Bob!

    Here is what my query looks like when doing the Response.Write. It
    does run when I copy and paste it into Access to do the query. What
    appears to be the problem is it is not looping past the first record
    for some reason ...

    UPDATE DetailRecs SET
    CongratsLetDt='07/04/09',dshipmentdate='08/04/09',ShipRefNo='',Status='1'
    WHERE DID =37162887

    Here is the error message I get:

    Microsoft VBScript runtime error '800a0009'

    Subscript out of range: '1'

    index.asp, line 148

    Line 148 in my earlier query is: sql_update = sql_update & "Status='"
    & StatusA(id) & "'"


    Sincere Thanks!
    bcap, Aug 4, 2009
    #3
  4. bcap

    Bob Barrows Guest

    bcap wrote:
    > Thank you Bob!
    >
    > Here is what my query looks like when doing the Response.Write. It
    > does run when I copy and paste it into Access to do the query. What
    > appears to be the problem is it is not looping past the first record
    > for some reason ...
    >

    Now see? Simply showing us the error message would have kept me from
    focussing on irrelevencies (you should still address the issues I
    raised)

    >
    > Here is the error message I get:
    >
    > Microsoft VBScript runtime error '800a0009'
    >
    > Subscript out of range: '1'
    >
    > index.asp, line 148
    >
    > Line 148 in my earlier query is: sql_update = sql_update & "Status='"
    > & StatusA(id) & "'"
    >

    Well, if that is really line 148, then it seems that the StatusA array
    (I'm not really sure it is an array, btw) only contains a single
    element. You're expecting it to contain as many elements as contained in
    the DID array?
    So let's look at the code where you defined the array:

    StatusA = Split("0",",")

    Hmmm, what do you think the purpose of the split function is? Are you
    trying to create an array with 3 elements, the first containing 0, and
    the others containing empty strings? If so, you should be using the
    Array() function, not the Split() function. Split() is used to break up
    a string containing multiple characters, putting the resulting
    substrings into an array.
    s = "break this up"
    ar = split(a, " ")
    ar now contains 3 elements, each containing one of the words.

    Your statement above, since the string to be split only contains a
    single character ("0") causes a single-element array to be created. I
    think you wanted to say:
    StatusA=Array("0",",")

    But this is fraught with danger as well. How are you guaranteeing that
    the DID array will only ever contain 3 elements?


    --
    HTH,
    Bob Barrows
    Bob Barrows, Aug 4, 2009
    #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. Roedy Green
    Replies:
    1
    Views:
    784
    Aquila Deus
    Aug 22, 2005
  2. Replies:
    25
    Views:
    650
    MonkeeSage
    Oct 1, 2006
  3. Replies:
    21
    Views:
    1,037
    Giannis Papadopoulos
    Aug 2, 2005
  4. kj
    Replies:
    15
    Views:
    540
    Lawrence D'Oliveiro
    May 23, 2009
  5. A
    Replies:
    8
    Views:
    784
    James Kanze
    Aug 28, 2010
Loading...

Share This Page