Update a row within a loop with "Order By"

Discussion in 'ASP General' started by Vanessa, Dec 15, 2006.

  1. Vanessa

    Vanessa Guest

    Here is my loop and it runs fine:

    ----------------------------------------------------
    sSQL = "SELECT * FROM STORE_ITEMS"
    Set DataRec = DB.execute(sSQL)
    if not DataRec.EOF then
    do while not DataRec.EOF
    SKU = trim (DataRec("SKU"))
    ITEM_ID = trim(DataRec("ITEM_ID"))
    ...
    ...
    updateStock = "update STORE_ITEMS set STR_ITEMS=" &NumInStock& " where
    ITEM_ID='" &ITEM_ID& "'"
    DB.execute(updateStock)

    DataRec.MoveNext
    loop 'loop thru items
    end if
    ----------------------------------------------------

    But when I added "order by" clause to my query, and it gave me time out
    error on this line:
    [Microsoft][ODBC SQL Server Driver]Timeout expired
    --> DB.execute(updateStock)

    Here is the code that is not working:
    ----------------------------------------------------
    sSQL = "SELECT * FROM STORE_ITEMS ORDER BY SKU"
    Set DataRec = DB.execute(sSQL)
    if not DataRec.EOF then
    do while not DataRec.EOF
    SKU = trim (DataRec("SKU"))
    ITEM_ID = trim(DataRec("ITEM_ID"))
    ...
    ...
    updateStock = "update STORE_ITEMS set STR_ITEMS=" &NumInStock& " where
    ITEM_ID='" &ITEM_ID& "'"
    DB.execute(updateStock)

    DataRec.MoveNext
    loop 'loop thru items
    end if
    ----------------------------------------------------

    It will be great if anyone can point me to the right direction. Thanks!!!
    Vanessa, Dec 15, 2006
    #1
    1. Advertising

  2. Vanessa wrote:
    > Here is my loop and it runs fine:
    >
    > ----------------------------------------------------
    > sSQL = "SELECT * FROM STORE_ITEMS"
    > Set DataRec = DB.execute(sSQL)
    > if not DataRec.EOF then
    > do while not DataRec.EOF
    > SKU = trim (DataRec("SKU"))
    > ITEM_ID = trim(DataRec("ITEM_ID"))
    > ...
    > ...
    > updateStock = "update STORE_ITEMS set STR_ITEMS=" &NumInStock& " where
    > ITEM_ID='" &ITEM_ID& "'"
    > DB.execute(updateStock)


    Where does NumInStock come from? it seems to me that this could be
    accomplished by a single UPDATE statement without the loop. The syntax of
    course depends on the database type (and version). Never ask a
    database-related question without providing this information.


    --
    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], Dec 15, 2006
    #2
    1. Advertising

  3. Vanessa wrote on Thu, 14 Dec 2006 16:25:01 -0800:

    > Here is my loop and it runs fine:
    >
    > ----------------------------------------------------
    > sSQL = "SELECT * FROM STORE_ITEMS"
    > Set DataRec = DB.execute(sSQL)
    > if not DataRec.EOF then
    > do while not DataRec.EOF
    > SKU = trim (DataRec("SKU"))
    > ITEM_ID = trim(DataRec("ITEM_ID"))
    > ...
    > ...
    > updateStock = "update STORE_ITEMS set STR_ITEMS=" &NumInStock& " where
    > ITEM_ID='" &ITEM_ID& "'"
    > DB.execute(updateStock)
    >
    > DataRec.MoveNext
    > loop 'loop thru items
    > end if
    > ----------------------------------------------------
    >
    > But when I added "order by" clause to my query, and it gave me time out
    > error on this line:
    > [Microsoft][ODBC SQL Server Driver]Timeout expired
    > --> DB.execute(updateStock)


    Default timeout for ADO is 30 seconds. Your query is taking more than 30
    seconds to run, possibly due to the extra work needed to be done ordering
    the data. You will need to use your database's tools to see if you can
    optimise the query to run quicker, or a better solution would be as
    suggested by Bob to do all the work in the database using a single update
    query if possible. In the short term, you could increase the ADO timeout
    using the CommandTimeout property on the Connection object, eg.

    DB.CommandTimeout = 120 'set timeout to 120 seconds

    put this line before you execute the SQL. You may also need to look at
    increasing the ASP script timeout to ensure that IIS does not stop the
    script before it has chance to finish running, you can use the
    Server.ScriptTimeout property to set this.

    Dan
    Daniel Crichton, Dec 15, 2006
    #3
  4. Vanessa wrote:
    > Here is my loop and it runs fine:
    >
    > ----------------------------------------------------
    > sSQL = "SELECT * FROM STORE_ITEMS"
    > Set DataRec = DB.execute(sSQL)
    > if not DataRec.EOF then
    > do while not DataRec.EOF
    > SKU = trim (DataRec("SKU"))
    > ITEM_ID = trim(DataRec("ITEM_ID"))
    > ...
    > ...
    > updateStock = "update STORE_ITEMS set STR_ITEMS=" &NumInStock& " where
    > ITEM_ID='" &ITEM_ID& "'"
    > DB.execute(updateStock)
    >
    > DataRec.MoveNext
    > loop 'loop thru items
    > end if
    > ----------------------------------------------------
    >
    > But when I added "order by" clause to my query, and it gave me time
    > out error on this line:
    > [Microsoft][ODBC SQL Server Driver]Timeout expired
    > --> DB.execute(updateStock)
    >
    > Here is the code that is not working:
    > ----------------------------------------------------
    > sSQL = "SELECT * FROM STORE_ITEMS ORDER BY SKU"
    > Set DataRec = DB.execute(sSQL)
    > if not DataRec.EOF then
    > do while not DataRec.EOF
    > SKU = trim (DataRec("SKU"))
    > ITEM_ID = trim(DataRec("ITEM_ID"))
    > ...
    > ...
    > updateStock = "update STORE_ITEMS set STR_ITEMS=" &NumInStock& " where
    > ITEM_ID='" &ITEM_ID& "'"
    > DB.execute(updateStock)
    >
    > DataRec.MoveNext
    > loop 'loop thru items
    > end if
    > ----------------------------------------------------
    >
    > It will be great if anyone can point me to the right direction.
    > Thanks!!!


    Wait a minute ... an ORDER BY on a SELECT statement causes a timeout on
    an UPDATE statement??? This makes no sense, unless the SELECT statement
    is causing the table to be locked ...

    If you really need to retrieve all the records in the table in order to
    construct that update statement (and I still believe you don't have to
    .... why haven't you replied with the answers to my previous questions?),
    then you should probably use either
    1. a GetRows array - my preference, but since you used selstar, I cannot
    show you how to implement it, so ...
    2. a client-side disconnected recordset:

    sSQL = "SELECT * FROM STORE_ITEMS ORDER BY SKU"
    'http://www.aspfaq.com/show.asp?id=2096

    Set DataRec = createobject("adodb.recordset")
    DataRec.CursorLocation = 3 '3=adUseClient
    DataRec.Open SQL,DB,,,1 '1=adCmdText
    Set DataRec.ActiveConnection = nothing
    do while not DataRec.EOF
    SKU = trim (DataRec("SKU"))
    ITEM_ID = trim(DataRec("ITEM_ID"))
    ....
    ....
    updateStock = "update STORE_ITEMS set STR_ITEMS=" & _
    NumInStock & " where ITEM_ID='" & ITEM_ID & "'"
    DB.execute(updateStock,,129)
    '129=1, adCmdText + 128,adExecuteNoRecords

    DataRec.MoveNext
    loop 'loop thru items

    Again, if you explain where NumInStock comes from, I can show you how to
    eliminate this recordset loop entirely.

    --
    Microsoft MVP -- ASP/ASP.NET
    Please reply to the newsgroup. The email account listed in my From
    header is my spam trap, so I don't check it very often. You will get a
    quicker response by posting to the newsgroup.
    Bob Barrows [MVP], Dec 15, 2006
    #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. Chip

    Selecting a row within a row

    Chip, Feb 10, 2006, in forum: ASP .Net
    Replies:
    0
    Views:
    361
  2. takayuki
    Replies:
    2
    Views:
    277
    Calvin Spealman
    Jun 16, 2008
  3. D
    Replies:
    0
    Views:
    205
  4. Sameeksha
    Replies:
    0
    Views:
    117
    Sameeksha
    Apr 7, 2005
  5. Isaac Won
    Replies:
    9
    Views:
    369
    Ulrich Eckhardt
    Mar 4, 2013
Loading...

Share This Page