Hi there,
It looks like you are trying to create dynamic variables, which is not going to work.
values = "values" & recordSetCount
set values = conn.execute(sql)
In the above code, you are creating a variable (the second values) by appending a number to a string, which is not allowed. Variables need to be defined at development time.
What you need is an array. You can store each recordset in an array, like this:
Dim myArray(1)
Set myArray(0) = MyFirstRecordset
Set myArray(1) = MySecondRecordset
What's important with this scenario is that you disconnect the recordset from the initial datasource. For this to work, you'll need to set the ActiveConnection of the recordset to Nothing. And that, in turn, requires a client side cursor on the connection.
Here's a quick example that demonstrates what I mean. Basically this code loops through an array of table names, executing a SELECT * FROM for each table. The recordset is disconnected from the connection, and saved in the array using Set. At the end, the code loops through the array of recordset. For each recordset, all the first fields for all records are displayed at the page:
Code:
<%
' Modify previous line, so it points to a valid adovbs.inc file
Dim arrRecordsets(2)
Dim oField ' As ADODB.Field
Dim oRecordset
Dim oConn
Dim iLoop
Dim sSQLBase
Dim arrTableNames(2)
arrTableNames(0) = "Events"
arrTableNames(1) = "Users"
arrTableNames(2) = "Categories"
sSQLBase = "SELECT * FROM "
' Create connection
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.Open "YourConnectionString"
' Explicitly set a cllient side cursor. Required to disconnect the recordset
oConn.CursorLocation = adUseClient
For iLoop = 0 To UBound(arrTableNames)
' Create the recordset
Set oRecordset = oConn.Execute(sSQLBase & arrTableNames(iLoop))
' Disconnect Recordset from source
oRecordset.ActiveConnection = Nothing
' Put the Recordset in the array
' The sett command is very important here; otherwise
' you'll just assign the default property of the recordset
' to the array
Set arrRecordsets(iLoop) = oRecordset
Next
' Clean up
oConn.Close
Set oConn = Nothing
' At this point, arrRecordset should contain three recordsets.
' Let's see if that is true.
For iLoop = 0 To UBound(arrTableNames)
Set oRecordset = arrRecordsets(iLoop)
If Not oRecordset.EOF Then
Response.Write("<h1>Recordset " & iLoop & "</h1>")
Do While Not oRecordset.EOF
Response.Write("First field is " & _
oRecordset.Fields(0).Value & "<br />")
oRecordset.MoveNext()
Loop
Else
Response.Write("Recordset is empty")
End If
Next
%>
To test this code, you'll need to modify the parts in bold. I.e.: change the array with table names so it holds the names of some tables in your database, and modify the connection string.
As I put in my comments, it's important to use Set when you assign the recordset to the array.
Although this scenario will work pretty well, it may not be recommended in terms of performance. What may be better is to store the contents of the recordset in its own array using
GetRows, and store that in the main array. Not really sure if that works or not. It could work, but I am not sure how VBScript handles jagged arrays.
A third alternative may be to change your business logic. Not really sure what you're trying to accomplish with this code, but maybe you can skip it altogether, changing things so all this looping isn't really necessary.
Hope this helps,
Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
dynamicly name a record set
