 |
Professional ADO RDS
Programming with ASP
by Matt Brown, Charles Caison, John Papa, Peter DeBetta, Eric Wilson
Published by Wrox Press 
1999; $49.99US; ISBN: 1861001649
500 pages
Sample Chapter 6: Building on the Recordset Foundation
Copyright 1999. Wrox Press, reprinted with permission. The text format was modified to
fit the format of this web site.
|
Related Links
• In-depth review of the book
Multiple Recordsets
Many times, we need to get data in a static form (by this, I mean
data that we can use for look-ups). Just as a database may have look-up tables, often we
present our users with combo or list boxes that allow them to view options that we store
in the database. These options are not subject to change and especially not subject to
change by our users. What if we have a lot of data that we are using to populate lists and
combos? What is the most efficient way of getting a lot of disparate data? The answer is
to use multiple recordsets.
What are Multiple Recordsets?
Multiple recordsets are what I like to call 'stacked recordsets'.
You issue multiple select statements at the same time, delimited by semicolons or spaces,
and you are returned multiple recordsets in a stack. Like this:
strSQL = <SELECT ….> ; <SELECT …..>…
objRS.Open strSQL
After we call the recordset's Open method in the above lines of
code, we now have a pop-only stack of recordsets. The stack is returned in order, so the
first SQL SELECT is the first recordset immediately available.
Popping the Stack – the NextRecordset Method
To get at the next available recordset we use the NextRecordset
method. The NextRecordset method returns a Recordset object. When you call NextRecordset
your recordset object variable will drop its pointer to the current recordset and point to
the next one. If no more recordsets are available, then NextRecordset returns Nothing.
Can I use Multiple Recordsets with Any Database?
Not every database, driver, and provider supports multiple
recordsets. I've never been able to get them to work with Oracle. SQL Server works like a
champ with them (in some cases – for example, when server-side cursors are used with
ODBC, you get an unpleasant error message).
This screenshot shows a typical use for multiple
recordsets. This is a display of some look-up tables from the pubs database in SQL Server.
In this example we've populated some HTML SELECT boxes that the user might use to make
static selections. Note that the data we retrieve in this way is data that the user needs
for information purposes, not information that the user will necessarily be editing. |
 |
Let's look at the code. The first interesting thing is the
instantiation of the Recordset object. We will give the recordset a connect string so that
it can connect itself; however, notice the strSQL variable. This contains our three SQL
statements for the retrieval of the multiple recordset. There is nothing special about the
statements and they are concatenated into one space-delimited string:
<%@ LANGUAGE="VBSCRIPT" %>
<HTML><HEAD><TITLE>Multiple Recordset Example</TITLE></HEAD>
<BODY>
<H1>Multiple Recordsets</H1>
<%
Dim objRS ' as ADO Recordset
Dim strSQL ' as string
Set objRS = CreateObject("ADODB.Recordset") 'Create the recordset object
'Initialize the string with 3 space delimited SQL statements
strSQL = "SELECT Title='Jobs', job_desc FROM jobs " & _
"SELECT Title='Publishers', pub_name FROM publishers " & _
"SELECT Title='Stores', stor_name FROM stores "
objRS.ActiveConnection = "Provider=MSDASQL;" & _
"Persist Security Info=False;" & _
"User ID=sa;" & _
"Data Source=winbook;" & _
"Initial Catalog=pubs" 'Connect to database
objRS.CursorType = 0 'adOpenForwardOnly
objRS.Open strSQL
Once we've opened the recordset, we have immediate access to the
results returned by the first SQL string. Notice that entry to the While loop is
dependent on the condition that our Recordset object, objRS, is not equal to Nothing
– i.e. it hasn't gone past the last of our multiple recordsets. The loop depends on
the call to NextRecordset. At the end of each run through the loop, we make the call to
NextRecordset, to replace the exhausted recordset with the next one (always in the same
order in which we issued the select statements). We populate three SELECT tags with
varying amounts of disparate data. When we've looped through all the recordsets, the next
call to NextRecordset returns Nothing, and the loop ends:
Dim intCount
'Loop through the multiple result sets
Do While not (objRS is Nothing)
intCount = 1
Response.Write objRS(0).Value & "<BR>"
Response.Write "<SELECT MULTIPLE>"
Do While Not objRS.EOF
Response.Write "<OPTION VALUE=""" & intCount
& """>"
Response.Write objRS(1).Value
objRS.MoveNext
intCount = intCount + 1
Loop
Response.Write "</SELECT><P>"
'When we are out of recordsets we are set to nothing
set objRS = objRS.NextRecordset
Loop
%>
</BODY></HTML>
Summary
The sun is setting on this chapter. We have seen a lot of things
going on with the Recordset object – the subjects that we've covered here help us to
make better and faster use of ADO. Among other things, we've seen how:
- By cloning recordsets, we can take advantage of an efficient way of using ADO when we
need to copy the Recordset object
- Paging makes good use of the data for our own code and for our users, by giving us a
programmatically simple way of presenting data (which also aids user efficiency by giving
them ways to look at logical data chunks)
- We can create an index on a recordset, provided it supports the Optimize property. The
Find and Sort methods – which we can use to search and sort our recordset – use
this property to create a temporary index if necessary
- The Filter property helps us to make more incisive, focused queries on an existing
recordset (without having to requery the database)
- Multiple recordsets allow us to retrieve data with less network access
ADO recordsets offer us all these features and more (don't forget
batch updating!). In the next chapter, we'll move onto look at the heady world of data
shaping…
|
