When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
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.

- continued -

'

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:

  1. Get the first recordset...
  2. Doing some processing with it...
  3. Close it and get the second recordset...
  4. 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.

Set oRS = Nothing

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



  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article