473,703 Members | 2,512 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Update a row within a loop with "Order By"

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("I TEM_ID"))
...
...
updateStock = "update STORE_ITEMS set STR_ITEMS=" &NumInStock& " where
ITEM_ID='" &ITEM_ID& "'"
DB.execute(upda teStock)

DataRec.MoveNex t
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(upda teStock)

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("I TEM_ID"))
...
...
updateStock = "update STORE_ITEMS set STR_ITEMS=" &NumInStock& " where
ITEM_ID='" &ITEM_ID& "'"
DB.execute(upda teStock)

DataRec.MoveNex t
loop 'loop thru items
end if
----------------------------------------------------

It will be great if anyone can point me to the right direction. Thanks!!!
Dec 15 '06 #1
3 2697
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("I TEM_ID"))
...
...
updateStock = "update STORE_ITEMS set STR_ITEMS=" &NumInStock& " where
ITEM_ID='" &ITEM_ID& "'"
DB.execute(upda teStock)
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"
Dec 15 '06 #2
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("I TEM_ID"))
...
...
updateStock = "update STORE_ITEMS set STR_ITEMS=" &NumInStock& " where
ITEM_ID='" &ITEM_ID& "'"
DB.execute(upda teStock)

DataRec.MoveNex t
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(upda teStock)
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.CommandTimeo ut = 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.ScriptTi meout property to set this.

Dan
Dec 15 '06 #3
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("I TEM_ID"))
...
...
updateStock = "update STORE_ITEMS set STR_ITEMS=" &NumInStock& " where
ITEM_ID='" &ITEM_ID& "'"
DB.execute(upda teStock)

DataRec.MoveNex t
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(upda teStock)

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("I TEM_ID"))
...
...
updateStock = "update STORE_ITEMS set STR_ITEMS=" &NumInStock& " where
ITEM_ID='" &ITEM_ID& "'"
DB.execute(upda teStock)

DataRec.MoveNex t
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=209 6

Set DataRec = createobject("a dodb.recordset" )
DataRec.CursorL ocation = 3 '3=adUseClient
DataRec.Open SQL,DB,,,1 '1=adCmdText
Set DataRec.ActiveC onnection = nothing
do while not DataRec.EOF
SKU = trim (DataRec("SKU") )
ITEM_ID = trim(DataRec("I TEM_ID"))
....
....
updateStock = "update STORE_ITEMS set STR_ITEMS=" & _
NumInStock & " where ITEM_ID='" & ITEM_ID & "'"
DB.execute(upda teStock,,129)
'129=1, adCmdText + 128,adExecuteNo Records

DataRec.MoveNex t
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.
Dec 15 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

23
5673
by: ian justice | last post by:
Before i post actual code, as i need a speedyish reply. Can i first ask if anyone knows off the top of their head, if there is a likely obvious cause to the following problem. For the moment i've reduced my form request to a simple text string entry, instead of my desired optional parameters. As i have been stuck with a single unfathomable glitch for over a year. Basically, if i enter queries such as ; "select * from table" "select * from...
1
2365
by: Simon Wigzell | last post by:
Is it possible to retrieve data with ASP sorted on 2 fields? I've tried : SELECT * FROM ORDER By Field1 AND Field 2 But it gives an error. I've searched the internet on this topic to no avail. Is it possible? Thanks.
14
2029
by: Pedro Werneck | last post by:
Hi I have a class A, with metaclass M_A, and class B, subclass of A, with metaclass M_B, subclass of M_A. A class C, subclass of B must have M_B or a subclass of it as metaclass, but what if I need to 'disable' the code in M_B on C ? The correct way to do that seems to be with a M_C metaclass, subclass of M_B, implementing but not calling parent class methods, or calling 'type' methods.
3
2245
by: Ptbrady | last post by:
"Order By" fails in form for linked table. -------------- My A2K database has worked well for several years, but now has been split into front-end/back-end and has the following problem. I have a form to access a table which I'll call "NameTable". It has a simple structure: ------------ EmployeeID FirstName (Other fields) (autonumber, primary key) ---------------
9
9591
by: Frederik | last post by:
Hi all, I'm building a C# application that uses a M$ Acces database. In one of the queries I use something like the following: SELECT id FROM mytable WHERE id IN (20, 12, 21, 14) The result is fine, except that the order of the id's is not preserved. It seems to be ordered ascending. How can I keep the order I used as input?
8
7593
by: vijay | last post by:
Hello, As the subject suggests, I need to print the string in the reverse order. I made the following program: # include<stdio.h> struct llnode { char *info;
7
6335
by: Giles | last post by:
An ASP page outputs data from the query "Select ThisAndThat from comments WHERE pageURL='" & pageURL & "' ORDER BY threadID, datesent" (Access mdb) threadID is a string (OK, I know!), which means that 103 displays before 99. Is there a way to write the SQL query to order them numerically? This would be much easier for me than changing the data type and hunting down every page that INSERTS or UPDATES the db. Thanks, Giles
2
54014
by: Richard Hollenbeck | last post by:
I have the following query in a combo box: SELECT courses.courseCode, courses.courseDescription FROM courses ORDER BY courseDescription; I want to change the "ORDER BY" to display in descending order so the more recent courses appear first on the list. How do I modify this query to accomplish that end? Thanks!
16
2627
by: Michael Bell | last post by:
I did an the Open University course MT262. I got 3/4 the way through it and only really came unstuck at classes. Following the advice of many, on this list and elsewhere, and impressed by their statement that students were writing substantial programs within a day, I bought "Accelerated C++" by Koenig and Moo. I find I just can't get on with it. I have started at the beginning, and I just can't follow it. The first few chapters do...
0
8669
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9251
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9117
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8961
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7864
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6588
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4430
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
1
3123
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2069
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.