Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > ASP .Net > ASP General > Update a row within a loop with "Order By"

Reply
Thread Tools

Update a row within a loop with "Order By"

 
 
Vanessa
Guest
Posts: n/a
 
      12-15-2006
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!!!
 
Reply With Quote
 
 
 
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      12-15-2006
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"


 
Reply With Quote
 
 
 
 
Daniel Crichton
Guest
Posts: n/a
 
      12-15-2006
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


 
Reply With Quote
 
Bob Barrows [MVP]
Guest
Posts: n/a
 
      12-15-2006
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.


 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Triple nested loop python (While loop insde of for loop inside ofwhile loop) Isaac Won Python 9 03-04-2013 10:08 AM
newbie question: for loop within for loop confusion takayuki Python 17 06-17-2008 09:49 PM
newbie: for loop within for loop question takayuki Python 2 06-16-2008 03:22 AM
Selecting a row within a row Chip ASP .Net 0 02-10-2006 10:03 PM
ok I can do a totals row but how about a percentage row after each data row D ASP .Net Datagrid Control 0 05-23-2005 04:10 PM



Advertisments