Using Multiple RecordsetsBy Hendry Cahyadi
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
(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
Categories table, and do some processing with that Recordset:
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.
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
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
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:
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).
Now it's time to create our multiple Recordset:
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:
And then create the multiple Recordset as follows:
At this point, the
oRs variable is an ADO multiple Recordset. When we call the
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
Back again to our example: after calling the
Open method of ADO Recordset object, we have
our Recordset from
Categories table to work with:
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
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
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:
So, to process both Recordsets, our end code would look something like:
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:
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