queries within loops 1

[snix1]

Hi! I would like to perform a set of queries within a loop and then reference each foundset later.

for i = 1 to 12
strSQL = "SELECT * from myTable WHERE some condition"
set rst = dbConnection.execute(strSQL)
next

The problem with the above is that I need another name for rst for each query to keep the foundsets separate. Note that the WHERE clause will change for each query, although my example doesn't show this. I ideally would like to concatenate the loop variable, i, on to the rst variable in some way, but am unclear how to do this in ASP.

Later on, I want to access each foundset in a separate loop.

Any way to do this?

Thanks!

Later on,

 

[bduke]

rst = rst & i
this should give you what you want

 

[snix1]

Thanks!
Do you mean rst = "rst" & i?
I tried that and get an object required error when I try to access the foundset.
for i = 1 to 12
rst = "rst" & i
IF NOT (rst.EOF) THEN
WHILE NOT rst.EOF
...etc.

The IF NOT (rst.EOF) THEN gives the error and I am unclear as to why.

 

[onpnt]

you could use the Execute statement also to dynamically set the name of the rs or a variable name.

also the getrows method may be something to look into. Then you wouldn't have to execute a sql every interation instead running through a array

1H 1K 10 3D 3F 3E 3K 38 3J 10 1T 10 3G 3L 3I 35 10 35 3O 33 35 3C 3C 35 3E 33 35

2 more days

 

[pwherman2]

You could try doing your loop and load the results into an array:

Dim arrNew
for i = 1 to 12
strSQL = "SELECT * from myTable WHERE some condition"
set rst = dbConnection.execute(strSQL)
arrNew = rst
next

or use a 2 dimensional array if you are returning more than one piece of data through the recordset.

 

[clarkin]

if this is all for reporting (ie, you don't want to update the recordsets, just display them) you want to change them to 2d arrays using .getRows(). Example:

Dim arrRst
..
set rst = dbConnection.execute(strSQL)
arrRst = rst.getRows()
rst.Close

arrRst(0,0) will hold the top-left result of your SELECT, and so on.

Further to this you could create an array of 12 2D arrays. This will get a little confusing.. but lets try it.

Dim arrRst(11) '1D array with 12 spaces
..
  set rst = dbConnection.execute(strSQL)
  arrRst(i) = rst.getRows() 'assign SELECT results into i'th spot
  rst.Close
next

That should work fine - you can reuse the same recordset each time you go through your for loop. the arrRst array should hold 12 2D arrays once that is finished..
I think this is how you would refer to your results..

Response.Write arrRst(0)(0,1) '1st SELECT, 1st row, 2nd column
Response.Write arrRst(11)(0,1) '12th SELECT, 1st row, 2nd column

Let us know how you get on


Posting code? Wrap it with code tags: [ignore]

[/ignore][code]CodeHere

[ignore][/code][/ignore].

 

[snix1]

Thanks to everyone for your help. I finally got this to work and learned more about ASP in the process, especially how getrows works better than the usual way records are retrieved. By the way, this is a calendar function that displays a whole year in a 3 X 4 table, with the first group of months containing the current month: (You can click on links to get more information about the event)

July August September
event1 event2 event3
event 4

October November December
event 5 event 6 event 7
event 8 event 9

January February March (NEXT YEAR)
event 10 event 11 event 12

April May June
event 13 event 14 event 15
event 16

Here's code fragments that are relevant:

for CalendarLoop from 1 to 12

(Set up start_date, end_date for each month )


strSQL = "SELECT start_date, end_date, event FROM Calendar " &_
            "WHERE start_date >= #" & first_day & "# AND " &_
			&quot;      start_date <= #&quot; & last_day & &quot;# &quot; &_
			&quot;ORDER BY start_date&quot;
			
			
   set rst = dbConnection.execute(strSQL)	
   if not rst.EOF then
   arrRst(CalendarLoop) = rst.getRows()
   end if

next

Then, later on, retrieve each foundset:

for Monthloop from 1 to 3

if not isempty(arrRst(Monthloop)) then 'make sure add this line!
   numrows=ubound(arrRst(Monthloop),2)
   fld_start_date = 0
   fld_end_date = 1
   fld_event = 2
   fld_location = 3
   fld_description = 4
   fld_link = 5
   fld_directions = 6
   fld_time = 7
   fld_cal_id = 8
   for rowcounter = 0 to numrows
   cal_start_date = arrRst(Monthloop)fld_start_date,rowcounter) 

   cal_end_date = arrRst(Monthloop)(fld_end_date,rowcounter)

   cal_event = arrRst(Monthloop)(fld_event,rowcounter)

   cal_location = arrRst(Monthloop)(fld_location,rowcounter)

   cal_description = arrRst(Monthloop)(fld_description,rowcounter)

   cal_time = arrRst(Monthloop)(fld_time,rowcounter)

   cal_id = arrRst(Monthloop)(fld_cal_id,rowcounter)  

(Do the HTML here, etc)

Again, thanks everyone!