Update a row within a loop with "Order By"

[Vanessa]

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

 

[Bob Barrows [MVP]]

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.

 

[Daniel Crichton]

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

 

[Bob Barrows [MVP]]

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

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.