Published: Friday, August 31, 2001
Using Multiple Recordsets
By Hendry Cahyadi
Introduction
Multiple Recordsets - the ability to stuff multiple SQL query results into one Recordset - is an ADO feature
that's few developers are aware of. In this article, we are going to take a look on what a Mutliple Recordset
is, exactly, and how it can make your development life easier (and your code more efficient). But before we
start examining Multiple Recordsets, let's look at a common task in ASP and how it is handled without
using Multiple Recordsets.
When developing ASP pages, there are times when our ASP programming logic requires us to retrieve multiple
recordsets that originate from multiple tables that have no direct relationship between them. (Retrieving
multiple recordsets means that we use more than one SQL SELECT statement. )
But that is not too difficult a task for an intermediate or experienced ASP programmer. Usually
the common solution would be to:
- Get the first recordset...
- Doing some processing with it...
- Close it and get the second recordset...
- Process it, close it, get the third, ... and so on...
Let's take a look at the code that we'd need to use to implement such a solution.
The Typical Way to Retrieve Multiple Recordsets
In this case, we are going
to use Northwind database. For this example we'll assume that we'll need to have our first Recordset populated
with records that from the Categories table having a CategoryID greater than 3;
assume that our second Recordset requires records from the Region table.
(These two tables are from SQL Server's Northwind
database.) Afterward, We will apply some unique processing for the records in each recordset. Unfortunately,
there's no any relationship between those tables so we can't grab it once using one SQL SELECT statement.
Using the most common way to accomplish that using ASP can be done as follows...
First, create an instance of ADO Recordset and populate the first Recordset with the appropriate data from
the Categories table, and do some processing with that Recordset:
'adjust this to your own
sConnectString = "DRIVER={sql server};SERVER=localhost;" & _
"DATABASE=northwind;UID=sa;PWD="
'Populate the Recordset
set oRS = Server.CreateObject("ADODB.Recordset")
oRS.Open "SELECT categoryId, categoryName FROM Categories " & _
"WHERE categoryId > 3", sConnectString
'Now we've got the recordset, and do some processing with its records
Do While Not oRs.EOF
'......some processing here
oRs.MoveNext
Loop
oRS.Close
|
Once we've finished processing the Categories table Recordset, we're ready to populate the
second Recordset from Region table, and also do some processing with it.
oRS.Open "SELECT RegionId, RegionDescription FROM Region", sConnectString
'Now we've got the recordset, and do some processing with its records
Do While Not oRs.EOF
'......some processing here
oRs.MoveNext
Loop
oRS.Close
|
Okay, at this point, we're 100% done with our Recordset objects, so we need to do clean up.
Yes, we've accomplished what we want! Before I knew about Multiple Recordsets, I thought this approach was the
elegant solution. Notice that while there's many ways for populating a Recordset (like using the
GetRows method,
the GetString method, etc, but using either of those, you still have
to open and close the Recordset object each time we want to retrieve another Recordset from another table.
This also means you create and destroy ADO connection object in the background (ADO do this implicitly and
it's not transparent to you). Experienced ASP programmers may do it better by creating a single ADO
Connection object explicitly and specifying it, instead of a connection string, in each of the Recordset's
Open methods. We can do much better if we use multiple Recordsets to accomplish this kind of
task.
Using Multiple Recordsets
So what are multiple Recordsets? A multiple Recordset is not much different from the typical ADO Recordset
object that we always use. It's just ADO Recordset object that holds more than one Recordset. To create a
multiple Recordset, go through the same steps as creating a typical Recordset, except, when specifying your
SQL SELECT statement, put all of the SQL SELECT statements that you need in that one Recordset, separating
each SQL SELECT statement with a semicolon. (You can also generate a multiple Recordset by populating a Recordset
with the results of a stored procedure that returns multiple SQL queries.)
Back to our earlier example, but now we will recode what we've done using multiple Recordsets! The first step
to using multiple Recordsets is to making our SQL SELECT statements all appear as one, semicolon delimited
string:
SELECT categoryId, categoryName FROM Categories WHERE categoryId > 3;SELECT RegionId, RegionDescription FROM Region
|
In order to make our code more readable and easy to maintain, it's better we declare our SQL SELECT
statements like the one below (don't forget to put semicolon between SQL SELECT statements).
sSQL = "SELECT categoryId, categoryName FROM Categories WHERE categoryId > 3"
sSQL = sSQL & ";SELECT RegionId , RegionDescription FROM Region"
sSQL = sSQL & ";SELECT ShipperID, CompanyName, Phone FROM Shippers"
|
Now it's time to create our multiple Recordset:
sConnectString = "DRIVER={sql server};SERVER=localhost;" & _
"DATABASE=northwind;UID=sa;PWD="
set oRS = Server.CreateObject("ADODB.Recordset")
oRs.Open sSQL, sConnectString
|
If you like to use SQL stored procedures, you can utilize multiple Recordsets by creating a stored procedure
that returns multiple SQL queries, like so:
Create Procedure MyResults
AS
SELECT categoryId, categoryName FROM Categories WHERE categoryId > 3
SELECT RegionId, RegionDescription FROM Region
SELECT ShipperID, CompanyName, Phone FROM Shippers
|
And then create the multiple Recordset as follows:
sConnectString = "DRIVER={sql server};SERVER=localhost;" & _
"DATABASE=northwind;UID=sa;PWD="
set oRS = Server.CreateObject("ADODB.Recordset")
oRs.Open "MyResults", sConnectString
|
At this point, the oRs variable is an ADO multiple Recordset. When we call the Open
method of ADO Recordset Object, we have had the first Recordset's results ready to access. The order of our
Recordsets will be the same as the order of our SQL SELECT statements that we declare first.
As you can see, nothing's much different from ordinary ADO recordset object, but this time we can use the
NextRecordset method to move on to the next Recordset. This method moves forward to the next
available Recordset from our compound SQL SELECT statment. If we've reached the last Recordset then calling
this method will return Nothing.
Back again to our example: after calling the Open method of ADO Recordset object, we have
our Recordset from Categories table to work with:
'We've got the Categories results... do some processing with its records
Do While Not oRs.EOF
'......some processing here
oRs.MoveNext
Loop
|
Once we have processed these results, it's time to move forward to the next Recordset. To accomplish this, we
just call the NextRecordset method. You can set the return value of the NextRecordset
method to another variable (another Recordset object). In order to conserve system resources, we should always set
the return value of NextRecordset function to the same Recordset variable - in our case oRS.
However, keep in mind that you can't move backwards to access the former Recordset (there is no
PreviousRecordset method), so if you really need to reference the previous Recordset,
have the return value of the NextRecordset method saved to another Recordset object, so that you
can access the first Recordset anytime. Use the NextRecordset method like so:
'To set the next Recordset to the same Recordset (encouraged)
Set oRS = oRS.NextRecordset()
'To set the next Recordset to some other Recordset object, in order
'to still be able to access the contents of oRS
Set oSomeOtherRS = oRS.NextRecordset()
|
So, to process both Recordsets, our end code would look something like:
'Create the SQL string
sSQL = "SELECT categoryId, categoryName FROM Categories WHERE categoryId > 3"
sSQL = sSQL & ";SELECT RegionId , RegionDescription FROM Region"
sSQL = sSQL & ";SELECT ShipperID, CompanyName, Phone FROM Shippers"
'Define our connection string
sConnectString = "DRIVER={sql server};SERVER=localhost;" & _
"DATABASE=northwind;UID=sa;PWD="
'Retrieve the multiple Recordsets
set oRS = Server.CreateObject("ADODB.Recordset")
oRs.Open SQL, sConnectString
'Work with the first Recordset (the Categories table)
Do While Not oRs.EOF
'......some processing here
oRs.MoveNext
Loop
'Move to the next Recordset
Set oRS = oRS.NextRecordset()
'Work with the second Recordset (the Region table)
Do While Not oRs.EOF
'......some processing here
oRs.MoveNext
Loop
'Clean up...
oRS.Close
Set oRS = Nothing
|
Caveats
There are some things to be wary of when using Multiple Recordsets. Unfortunately, you can't use
the NextRecordset method with an ADO disconnected Recordset. Also not all data
providers support multiple Recordsets. I've successfully used multiple Recordsets with MS SQL-Server, using either
the OLEDB or ODBC data providers. I have yet to try it to other RDBMS products, but it didn't work at all
with MS Access Database. When I tried to implement multiple Recordsets using an Access database, I received
the following error message:
ADODB.Recordset (0x800A0CB3)
Current provider does not support returning multiple recordsets from a single execution
|
Despite that, I hope you can get the most from using multiple Recordsets; for more
information, look for the NextRecordset method as a keyword in ADO API Reference.
Also, be sure to check out this article - Getting the ID of
the Just Inserted Database Record - it shows how to use multiple Recordsets to grab the ID of the
just-inserted record!
Happy Programming!
By Hendry Cahyadi
