creating variable names using a loop

[nimarii]

hello -

i need to create a dynamic number of recordsets each time the page loads.

i'm trying to do something like this:

for eCounter = 0 to ExtensionCounter
dim RS[eCounter]
set RS[eCounter] = "select * from table where id = eCounter"
next

what is the syntax for creating the name of the recordset to include a variable value?
I've tried things like:

dim RS & eCounter, dim "RS" & eCounter

but nothing seems to work.

is this even possible?

thanks!

 

[DotNetGnat]

you mean:

<%
dim myarray(10)

for i=Lbound(myarray) to Ubound(myarray)
myarray(i)="RS"&i
response.write myarray(i) & "<br>"
next

%>

thats in simple terms...what are you trying to do...

-DNG

 

[DotNetGnat]

forgot your sql statement:

<%
dim myarray(10), myarray1(10)

for i=Lbound(myarray) to Ubound(myarray)
myarray(i)="RS"&i
response.write myarray(i) & "<br>"
'response.write "Select * From mytable where id =" &i & "<br>"
myarray1(i)= "Select * From mytable where id =" &i & "<br>"
response.write myarray1(i) & "<br>"
next

%>

-DNG

 

[nimarii]

well - i think i could use arrays also, but here is what i mean.

i need to create recordsets with the following name formats:

dim rs0
dim rs1
dim rs2
dim rs3

I need the integer number at the end of the name, so i can reference it using the loop. for example:

set rs0 = "select * from table where id=0

but, i cant use the actual integers when declaring the recordset name, bc the number of recordsets to be created changes each time. thats why i was using the variable eCounter instead.

i hope i'm not being too confusing...

 

[DotNetGnat]

once you create two array, you can set the values of one equal the other...

so you have RS0="your query where id=0" and similarly others...

am i missing something here..

-DNG

 

[nimarii]

sorry for being so muddled...

so i should use 2 arrays instead of creating recordsets?

 

[DotNetGnat]

yeah...but i am not sure what actually you are trying to do..i am sure that there should be an elegant approach to whatever you are trying to do...

why not just say...

For i=0 to i=10
strsql="select * from mytable where id=" & i
rs.open strsql, conn
'do whatever you want to do here with the rs object and close it...
'create a section that separates the next rs display
next

why do u need so many recordsets

-DNG

 

[nimarii]

well its a bit of a mess...i'm trying to find an elegant solution, and i think arrays are probably the way to go. thanks for your help!

if you're interested in the full issue, keep reading...

i have an application that displays the payment schedule for mortgage notes. sometimes, these notes have multiple extensions, and i need to display these extensions in separate "tables" in addition to the main schedule(all on the same page). each note has a different number of extensions (if any). i'm currently displaying the main schedule using a recordset, so i thought i would do likewise with the extensions. things are also complicated in the way the extension data is stored in the tables. bah - its just a mess

 

[DotNetGnat]

no problem...glad to help...

-DNG

 

[Tarwn]

I would think you could pull back the data in one recordset, which would cut down a lot on overhead. Basically convert your SQL statement to pull back all values in the posible range you want (not sure where this comes from), something like:

Dim sql_ext
sql_ext = "SELECT * from table WHERE id >= " & lowerLimit & " AND id <= " & higherLimit

I don't know a whole lot about your page layout, othe than the fact that you wanted these in seperate tables. You could easily output this in several tables by just keeping track of the id field:

Dim last_id
If Not MyRS.EOF Then MyRS.MoveFirst
Do Until MyRS.EOF
   If MyRS("id") <> last_id Then
      'if there is a value for last_id ten we already have a table started, so end the previous table
      If last_id <> "" Then Response.Write "</table>"

      'start a table for this new id
      Response.Write "<table>"

      'keep track of this id so we know when it changes
      last_id = MyRS("id")
   End If

   'output the row of data we are in
   Response.Write "<tr><td>" & MyRS("someField") & "</td><td>" & MyRS("someOtherField") & "</tD></tr>"

   MyRS.MoveNext
Loop

'if last_id is set then we still have the last table open
If last_id <> "" Then Response.Write "</table>"

And there you have it, all your data in only one recordset but output in multiple tables. You culd make this more efficient by not using a * in your SQL statement and could also use MyRS.GetRows() to dump the data into a 2-dimensional array for better efficiency and more speed.

-T

 

[Bullschmidt]

And you may want to check this out:

Using the Eval and Execute Functions - 3/3/2000

http://www.4guysfromrolla.com/webtech/030300-1.shtml

Best regards,
J. Paul Schmidt, Freelance Web and Database Developer

http://www.Bullschmidt.com

Access Database Sample, Web Database Sample, ASP Design Tips