Batch Update Statement Help

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

  1. bcap

    bcap Guest

    Hi,

    I have written the below update statement and it works fine if there
    are multiple records in my recordset, but if there is just one single
    record it will not update that one record. Any ideas to what I am
    doing wrong or need to do right? =)

    dim id
    dim xx
    if uBound(DID) = 0 then
    xx=1
    else
    xx=0
    end if
    for id=xx to uBound(DID)
    sql_update = "update " & t_maintable & ""
    sql_update = sql_update & " SET "',"
    sql_update = sql_update & "ConLetDt='" & trim(ConLetDt(id)) &
    "',"
    sql_update = sql_update & "PID='" & trim(PID(id)) & "',"
    sql_update = sql_update & "SID='" & trim(SID(id)) & "',"
    sql_update = sql_update & "RespDt='" & trim(RespDt(id)) & "',"
    sql_update = sql_update & "TVal='" & trim(TVal(id)) & "',"
    sql_update = sql_update & "PSExportDt='" & trim(PSExportDt(id)) &
    "',"
    sql_update = sql_update & "dShipInd='" & trim(dShipInd(id)) &
    "',"
    sql_update = sql_update & "ShipRefNo='" & trim(ShipRefNo(id)) &
    "'"
    sql_update = sql_update & "WHERE DID =" & DID(id)
    conn1.Execute sql_update,,129

    Thank you in advance!!!!!!!
    bcap, Aug 28, 2009
    #1
    1. Advertising

  2. Gazing into my crystal ball I observed bcap <> writing
    in news:4e7eddfd-efa3-439f-87ce-555013ce0721
    @o21g2000vbl.googlegroups.com:

    > Hi,
    >
    > I have written the below update statement and it works fine if there
    > are multiple records in my recordset, but if there is just one single
    > record it will not update that one record. Any ideas to what I am
    > doing wrong or need to do right? =)
    >
    > dim id
    > dim xx
    > if uBound(DID) = 0 then
    > xx=1
    > else
    > xx=0
    > end if
    > for id=xx to uBound(DID)
    > sql_update = "update " & t_maintable & ""
    > sql_update = sql_update & " SET "',"
    > sql_update = sql_update & "ConLetDt='" & trim(ConLetDt(id)) &
    > "',"
    > sql_update = sql_update & "PID='" & trim(PID(id)) & "',"
    > sql_update = sql_update & "SID='" & trim(SID(id)) & "',"
    > sql_update = sql_update & "RespDt='" & trim(RespDt(id)) & "',"
    > sql_update = sql_update & "TVal='" & trim(TVal(id)) & "',"
    > sql_update = sql_update & "PSExportDt='" & trim(PSExportDt(id)) &
    > "',"
    > sql_update = sql_update & "dShipInd='" & trim(dShipInd(id)) &
    > "',"
    > sql_update = sql_update & "ShipRefNo='" & trim(ShipRefNo(id)) &
    > "'"
    > sql_update = sql_update & "WHERE DID =" & DID(id)
    > conn1.Execute sql_update,,129
    >
    > Thank you in advance!!!!!!!
    >


    Are you getting an error message? Put a response.write sql_update before
    the execute and see what it says. Post back here.

    --
    Adrienne Boswell at Home
    Arbpen Web Site Design Services
    http://www.cavalcade-of-coding.info
    Please respond to the group so others can share
    Adrienne Boswell, Aug 28, 2009
    #2
    1. Advertising

  3. bcap

    bcap Guest

    Hi,

    Thank you for your relpy. No, I do not get an error message. It just
    seems like it does not want to update the record if it is only one
    record.

    If I have 50 records it could batch 49 of them or even all 50 at one
    time, but if I have only 1 record, it seems like it just ignores the
    update.

    When the record has multiple updates at a time the

    uBound = 1
    xx = 0

    When it is a single record being updated the

    uBound = 0
    xx = 1

    Could this be the cuase of the problem?
    bcap, Aug 28, 2009
    #3
  4. bcap

    Bob Barrows Guest

    bcap wrote:
    > Hi,
    >
    > Thank you for your relpy. No, I do not get an error message. It just
    > seems like it does not want to update the record if it is only one
    > record.
    >
    > If I have 50 records it could batch 49 of them or even all 50 at one
    > time, but if I have only 1 record, it seems like it just ignores the
    > update.
    >
    > When the record has multiple updates at a time the
    >
    > uBound = 1
    > xx = 0
    >
    > When it is a single record being updated the
    >
    > uBound = 0
    > xx = 1
    >
    > Could this be the cuase of the problem?


    We can't tell without seeing the sql statement being executed.

    --
    Microsoft MVP - ASP/ASP.NET - 2004-2007
    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, Aug 28, 2009
    #4
  5. bcap

    bcap Guest

    Here is the error message:

    Microsoft VBScript runtime error '800a0009'
    Subscript out of range: '0'





    I realize that if works OK if all fields are populated, but this error
    produces if at least one of these fields is NULL
    bcap, Aug 28, 2009
    #5
  6. bcap

    Evertjan. Guest

    bcap wrote on 28 aug 2009 in microsoft.public.inetserver.asp.general:

    > dim xx
    > if uBound(DID) = 0 then
    > xx=1
    > else
    > xx=0
    > end if
    > for id=xx to uBound(DID)
    >


    Why not make it simple:

    dim id, xx
    if uBound(DID) > 0 then
    for id = 0 to uBound(DID)
    .........
    next
    end if



    --
    Evertjan.
    The Netherlands.
    (Please change the x'es to dots in my emailaddress)
    Evertjan., Aug 28, 2009
    #6
  7. bcap

    Bob Barrows Guest

    bcap wrote:
    > Here is the error message:
    >
    > Microsoft VBScript runtime error '800a0009'
    > Subscript out of range: '0'
    >
    >
    >
    >
    >
    > I realize that if works OK if all fields are populated, but this error
    > produces if at least one of these fields is NULL


    I'm lost. I thought you originally said there was no error message. Let's
    see ... yes, you did say this: "No, I do not get an error message"
    The symptom I was dealing with was the one where a sql statement was
    generated that did not produce the intended results. The only way to deal
    with that situation is to discover what the failing sql statement looks like
    using Response.Write.

    Now you tell me that you are getting an error message. What line of code
    raises that error?

    And what "fields" are you talking about? All we have seen is a partial block
    of vbscript that supposedly loops through what appears to be an array and
    generates a string that should contain a sql statement to be executed. More
    details please.

    --
    Microsoft MVP - ASP/ASP.NET - 2004-2007
    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, Aug 28, 2009
    #7
    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. Edward
    Replies:
    1
    Views:
    304
    =?Utf-8?B?UmF2aW5kcmE=?=
    Jun 10, 2004
  2. ML
    Replies:
    1
    Views:
    835
  3. andy6
    Replies:
    2
    Views:
    529
    andy6 via DotNetMonster.com
    Jun 15, 2006
  4. Replies:
    2
    Views:
    3,801
    Jared
    Jun 23, 2006
  5. rockdale

    Batch update in gridview?

    rockdale, Sep 12, 2006, in forum: ASP .Net
    Replies:
    0
    Views:
    1,944
    rockdale
    Sep 12, 2006
Loading...

Share This Page