 |
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
In this chapter we will build on the foundation
laid down in Chapter 5, by looking at some of the cool advanced features of the Recordset
object. The Recordset object offers many features – some help our programs to handle
data more efficiently, while the others (e.g. Supports and Transactional ASPs) help us to
manage our data. In this chapter, we will look at:
- Cloning recordsets
- Paging through data
- How to tell what a recordset supports
- Applying filters to data after it is retrieved
- Indexing, searching and sorting
- Updating data in batches
- Capturing multiple recordsets in one trip to the server
Cloning Recordsets
Suppose you want to create an exact copy of a recordset. How
would you do it? You could:
- Create and open a second Recordset object using the same SQL statement as the original
– but it's not the most efficient way to do it, and changes to one recordset would
not be reflected in the other.
- Set a newly-dimensioned recordset pointer to the existing recordset – but then you
wouldn't be working with a copy of the original recordset.
The good news is that ADO gives you an easier way to copy a
Recordset object: it's called cloning. The Clone method allows you to create an exact,
synchronized copy of an existing Recordset object. It is the most efficient way you can
use to get a copy of an existing recordset.
How to Create a Clone
There are a number of ways to clone a recordset. This first
example creates a clone, called objMyClone, whise lock type is the same as the clone's
progenitor, objRS:
set objMyClone = objRS.Clone
This second example also creates a clone with the same lock type
as its progenitor:
set objMyClone = objRS.Clone adLockUnspecified
This third example creates a read-only clone:
set objMyClone = objRS.Clone adLockReadOnly
What Types of Recordsets are Cloneable?
It's worth noting that you can only clone bookmarkable
recordsets. You can find out whether your recordset supports bookmarks by using the
recordset's Supports method (covered later in this chapter). For example, a recordset with
a forward-only cursor type is not a candidate for cloning – as we'll see in our first
code sample.
The listing below demonstrates a common error that many of us run
into when we try to clone for the first time. That is, we take the ADO default values
– or worse, in our quest for efficiency, our own settings betray us. The code example
below begins normally: we open an ADO connection to the pubs database (one of the sample
databases provided with SQL Server) using our SQL Server OLE DB provider, and then we
select a recordset from the authors table:
<%@ LANGUAGE="VBSCRIPT" %>
<HTML><HEAD><TITLE>Bad Clone</TITLE></HEAD>
<BODY>
<H1>Example of Illegal Cloning-- Forward Only Cursor</H1>
<%
Dim objConn ' as ADO conection
Set objConn = CreateObject("ADODB.Connection")
Dim objRS ' as ADO Recordset
Dim objClone ' as ADO Recordset
Dim strSQL ' as string
strSQL = "SELECT * FROM authors" 'Initialize the string
objConn.Open "Provider=MSDASQL;" & _
"User ID=sa;" & _
"Data Source=winbook;" & _
"Initial Catalog=pubs" 'Connect to database
Set objRS = CreateObject("ADODB.Recordset") 'Create the recordset object
Set objRS.ActiveConnection = objConn
objRS.Open strSQL 'Open the recordset
The code for this sample – and all the samples in the
book – is available for download from http://webdev.wrox.co.uk/books/1649.
So far so good. Now we'll call the Clone method on our
recordset. As I'll explain in a moment, it's this call that will cause a run-time error:
'Attempt to perform Cloning of a record set with forward only
cursor
'Ah, but remember that the defualt cursor type is forward only:
'This statement will result in a confusing and ambiguous error message
set objClone = objRS.Clone
%>
</BODY></HTML>
Here's the error message that we will receive when the code
sample above is executed:
Remember that a clone inherits the cursor type of its creator.
Since we've tried to clone a non-bookmarkable recordset (the original recordset, objRS,
has the default cursor type, forward-only), we receive a runtime error.
The forward-only cursor is the default cursor type of any
recordset (and in many cases is the most desirable and fastest). However, since we can't
clone a recordset that's opened with the forward-only cursor, you will need to
specifically request an alternate cursor type when you create the clone. In the next code
listing, we'll see an example of cloning that works!
Creating our first Clone
We'll refine the sample above, explicitly specifying a static
cursor for the original recordset. The static cursor, like the keyset and dynamic cursors,
is a bookmarkable scrolling cursor and therefore a legal type for cloning:
Set objRS = CreateObject("ADODB.Recordset") 'Create the
recordset object
'Use the static cursor type to clone with this provider
'You could also use a keyset cursor
objRS.CursorType = 3 'adOpenStatic
Set objRS.ActiveConnection = objConn
objRS.Open strSQL 'Open the recordset
Once the recordset is open, we can clone it and see that it does
provide some output:
Set objClone = objRS.Clone 'Perform the legal clone
Do While Not objClone.EOF 'Display the data to prove it
Response.Write objClone(1).Value & "<br>"
objClone.MoveNext
Loop
%>
</BODY></HTML>
The second field of the cloned recordset (i.e. objClone(1))
contains the surnames of the authors in the authors table, as shown in the screenshot:
The Record Pointer of the Cloned Recordset
It's fair to ask how the cloned recordset's record pointer (i.e.
the internal mechanism that points to the 'current row' of the recordset) behaves in
relation to the original recordset's record pointer. We know that the cloned recordset is
synchronized with its progenitor (and we'll get into the nitty-gritty of synchronized
recordsets shortly) – but does that mean that the clone's record pointer follows the
record pointer of the master recordset?
In fact, 'synchronized' does not mean 'in-step'. That is, the two
record pointers move independently of one another. The example below shows this. After
declaring all our variables, we set the query string to query the authors table; we open a
data connection to the pubs database; and then open the recordset:
strSQL = "SELECT * FROM authors" 'Initialize the string
objConn.Open "Provider=MSDASQL;" & _
"User ID=sa;" & _
"Data Source=winbook;" & _
"Initial Catalog=pubs" 'Connect to database
Set objRS = CreateObject("ADODB.Recordset") 'Create the recordset object
'Use the static cursor type to clone with this provider
'You could also use a keyset cursor
objRS.CursorType = 3 'adOpenStatic
Set objRS.ActiveConnection = objConn
objRS.Open strSQL 'Open the recordset
Notice that we're using a static cursor again. Then we create the
clone, just as we did in the previous example:
Set objClone = objRS.Clone 'Perform the legal clone
Now we'll loop through the cloned recordset, displaying the fact
that when we move the clone's record pointer, the original recordset's pointer remains
firmly on the first record:
Response.Write "I don't follow my clone<BR>"
'Display the data to prove it
Response.Write "<TABLE BORDER=1>"
Do While Not objClone.EOF
Response.Write "<TR>"
Response.Write "<TD>My Clone is on record:" & objClone(0).Value
& "</TD>"
Response.Write "<TD>I am on record:" & objRS(0).Value &
"</TD>"
Response.Write "</TR>"
objClone.MoveNext
Loop
Response.Write "</TABLE>"
objClone.MoveFirst
Response.Write "<P>My Clone does not follow me<BR>"
Response.Write "<TABLE BORDER=1>"
Do While Not objRS.EOF
Response.Write "<TR>"
Response.Write "<TD>I am on record:" & objRS(1).Value &
"</TD>"
Response.Write "<TD>My clone is on record:" & objClone(1).Value
& "</TD>"
Response.Write "</TR>"
objRS.MoveNext
Loop
Response.write "</TABLE>"
%>
</BODY></HTML>
Here, we've compared the first field of each the
recordset (objRS(0) and objClone(0)) as we move the record pointer through the cloned
recordset – to show that objRS's record pointer doesn't follow objClone's record
pointer. Then we do a similar comparison – but the other way round – to show
that objClone's pointer doesn't follow objRS's pointer either: |
 |
What are Synchronized Recordsets?
What exactly does it mean to say that a recordset and its clone
are synchronized? We'll look more closely at that now. In this next example, we'll clone a
recordset and then make some changes in an attempt to watch the synchronization at work.
We'll specify the SQL string (to query the authors table of the
pubs database), and open the connection to the pubs database, in just the way that we've
done in previous examples. Then we'll open a recordset against the authors table of the
pubs database in SQL Server, and set the clone:
strSQL = "SELECT * FROM authors" 'Initialize the string
objConn.CursorLocation = 3 'adUseClient
objConn.Open "Provider=MSDASQL;" & _
"User ID=sa;" & _
"Data Source=winbook;" & _
"Initial Catalog=pubs" 'Connect to database
objConn.BeginTrans ' Use a transaction – you'd need to call the
' CommitTrans method in order to set changes
' to the database
Set objRS = CreateObject("ADODB.Recordset") 'Create the recordset object
'Use the static cursor type to clone with this provider
'You could also use a keyset cursor
objRS.CursorType = 3 'adOpenStatic
objRS.LockType = 4 'adLockBatchOptimistic
Set objRS.ActiveConnection = objConn
objRS.Open strSQL 'Open the recordset
Set objClone = objRS.Clone 'Perform the legal clone
Note that we've used the BeginTrans method to start a
transaction. I'll explain why in a moment.
Now we'll compare a couple of corresponding entries in the
original and cloned recordsets (the au_fname and au_lname fields of the first record in
the recordset):
Response.Write "<P>Compare data in the two
recordsets:<BR>"
Response.Write "The clone is looking at: " &
objClone("au_fname").Value & _
" " & objClone("au_lname").Value &
"<BR>"
Response.Write "The original is looking at: " &
objRS("au_fname").Value & _
" " & objRS("au_lname").Value & "<BR>"
Now let's edit these data items – but only in the
original recordset, objRS:
Response.Write "<P>Make a change to the original
recordset... "
objRS("au_lname").Value = "Waters"
objRS("au_fname").Value = "Tim"
objRS.Update
We call the Update method to update the changes to the database.
This also has the effect of forcing the cloned recordset to follow the same changes as the
original – as we'll see in the next part of the code. Now we'll compare those
corresponding values again:
Response.Write "Now the clone has changed too!<P>"
Response.Write "<P>Compare data in the two recordsets:<BR>"
Response.Write "The clone is looking at: " &
objClone("au_fname").Value & _
" " & objClone("au_lname").Value &
"<BR>"
Response.Write "The original is looking at: " &
objRS("au_fname").Value & _
" " & objRS("au_lname").Value & "<BR>"
'I cancel the batch save here -- you don't have to
objRS.CancelBatch
%>
</BODY></HTML>
The output of this script looks like this:
We can see that the change in the progenitor recordset is now
reflected in the clone. Pretty cool, huh?
Recall that we called the BeginTrans method – so all the
code following the BeginTrans call is actually part of a transaction. We'd have to call
the CommitTrans method in order to commit all the changes. However, you've probably
noticed that we didn't call CommitTrans – therefore the transaction is rolled back at
the end of the script, and none of the changes are persisted. I took this step to protect
the pubs database from the changes that would otherwise have been enforced by the Update
method in the script. Thus, you can run this sample as often as you like, without
ultimately changing the underlying data in the pubs database.
When the Clone gets Out of Sync with the Original Recordset
You need to be careful, because the clone doesn't remain
synchronized with its original unconditionally. This next example shows how your original
and clone can become out-of-sync, for example, if you requery the original recordset
against the original database.
This sample builds onto the end of the previous example. Having
shown the original and the clone in sync (the final Response.Write statement in the code
above), we'll add some code to make the original recordset, objRS, requery the database:
Response.Write "<P>Now Requery the original and make a
change to it...<BR>"
objRS.Requery
Note that we can't call the Requery method for a recordset
that is currently being edited. Therefore we must either cancel the edits that we'd made
to objRS, or submit them to the database. In this case, you'll recall that we submitted
them using the Update method.
Once we've re-queried, we'll make some new edits to objRS,
and update the changes to the database:
objRS("au_lname").Value = "Brown"
objRS("au_fname").Value = "Erin"
objRS.Update
Recall, as we mentioned at the end of the previous section,
that the update is part of a transaction – we'll roll back the transaction at the end
to protect the pubs database from any actual changes.
Now, we'll compare the data in objRS and objClone again:
Response.Write "...now the clone is out of synch and still has
its old value!<P>"
Response.Write "<P>Compare data in the two recordsets:<BR>"
Response.Write "The clone is looking at: " &
objClone("au_fname").Value & _
" " & objClone("au_lname").Value &
"<BR>"
Response.Write "The original is looking at: " &
objRS("au_fname").Value & _
" " & objRS("au_lname").Value & "<BR>"
'I cancel the batch save here -- you don't have to
objRS.CancelBatch
%>
</BODY></HTML>
You can see from the results that the Requery method throws the
original and the clone out of sync – the changes made in the original recordset are
not reflected in the clone:
The lesson is this: Requerying
breaks the link between the original and the clone. However, it's also worth noting that,
if you have multiple clones and you requery the original recordset, the clones all remain
synchronized with each other (though obviously not with the original).
|
