You have almost reached the end of your journey in preparing for Active Server Pages development. After one more topic and a case study, we cut you loose.
In this chapter, you will explore the vast world of the RecordSet Object. Everything in ADO leads you to this place; now you will learn what to do when you get there. Fully 50 percent of the ADO documentation that ships with Active Server Pages is devoted to the RecordSet Object and its collections, methods, and properties. This is not surprising, because that's where all the data is. Sure, you can access limited cursors with the Connection Object and the Command Object, and you've got the Parameter objects. But in all these cases, data is aloof. In this chapter, you will get into the thick of the bits and bytes of your databases. Roll up your shirtsleeves and take a deep breath.
Concurrency control is the only database theory you will be exposed to in this book. This is necessary, because an extremely important part of ADO deals explicitly with cursors and certain kinds of cursors create circumstances that harbor potential concurrency conflicts.
RecordSet Objects create recordset cursors. Careful creation of recordset cursors is a key performance factor. Learn how to do it properly.
The main advantage of recordsets is that you can wander around in them; you're not limited to a single pass as you are with the Connection Object and the Command Object.
Using standard ADO record-manipulation methods can get you up and running quickly. Discover the methods' pros and cons.
One advantage of a well-designed desktop application like Microsoft Access is that it shields the user from most of the really arcane aspects of database management. The most trouble Access ever caused its users was by refusing to update tables accessed with queries that couldn't modify data; for example, when two tables were joined on a field with a one-to-many relationship. In ADO, the developer has a finer level of control over issues normally handled behind the scenes by an application such as Access. But with this new power comes new responsibility, and responsibility is always served by knowledge.
In this section, you delve deeper into database management theory. The result should be an understanding that lets you create recordsets consistent with your needs. Spend a little time here, and you will save a lot of time at your computer. You will understand how cursor types, lock types, and isolation levels all affect your RecordSet Object's behavior.
ADO enables you to create robust client/server database applications quickly. By definition, this means that more than one person will have access to your databases at the same time. This feature of client/server systems is called concurrency. Concurrent access to data means a large organization can have one repository of data and many people reading and changing it as if they were the sole owner. That is, the database management system assumes responsibility for the accuracy of the data stored in its files. This is a defining difference between manual systems and automated ones. With an increase in power of this order of magnitude come other issues of at least another order of magnitude. Database theory, then, must solve a problem called interference.
Interference introduces corruption into your database and or delays in data access. Quantum mechanics has its fabled "Uncertainty Principle" (namely, one cannot know the precise position and momentum of a subatomic particle at the same time). Database management systems have their own version of this principle: You cannot maximize concurrency and minimize interference at the same time. Unlike physics, however, we have some latitude in our Uncertainty Principle. In physics you choose one or the other property to observe; in database management you move between a range of tradeoffs. To the extent that you minimize interference by maximizing locks on database records, you lose some concurrency and, therefore, some performance. To the extent you make the cursors as fast as possible by using no locks at all, you open yourself to the threat of interference. To design ADO applications that meet your expectations with the fewest downstream surprises, you must understand concurrency and interference issues and how they play off one another.
As an ADO developer, you do not have to be concerned with the concurrency control implementation strategy used by your data provider. You do need to know what your options are when you define your RecordSet Object.
Optimistic Locking
Some data providers give you, the developer, a major say in how a database application operates. After all, you're the one who understands the operating environment of your application. If your ASP application is running on a small intranet, it's unlikely that concurrency and interference keep you up nights. Optimistic locking mechanisms assume that this is true, so database processing continues with updates stored in a temporary buffer until an order to update is given by the user. The base table is locked the instant before the update is applied to its records, and the lock is released immediately after the update succeeds. If there are conflicts (e.g., someone else has a record lock on the record being updated), the transactions are rolled back and the conflicts are resolved separately from further processing.
Sometimes, data providers permit more than one record to be edited at the same time, and the data providers update all records virtually simultaneously. This is like a second layer of concurrency, but it usually raises no additional concurrency issues, because the records usually don't interact with each other. When two people access the same record at the same time, however, this might not be true; for example, one person changes the value of Field B based on the value of Field A, but at the same time, someone else changes Field A.
At any rate, in batch updating, this is not an issue. Under conditions of high concurrency, however, permitting batch updates might aggravate the problem of interference, because one person is changing more than one record at the same time. It's up to you to balance the advantage of increased speed due to batch processing (arising from the relatively lower disk access required for batch updates) with the increased risk of interference.
Pessimistic Locking
Optimistic locking might work well on an intranet, but an application running on the Internet might have thousands of concurrent accesses and edits. To ensure data integrity under these conditions, data processing might need to obtain locks on records as soon as editing begins. Performance might suffer under heavy loads, but if data integrity is important, this might be a price you have to pay. Optimistic locking, by contrast, suffers from none of the problems discussed next.
Care must be exercised when using pessimistic locks. Because whole records, or a 2k page of records in Microsoft Access, are locked before the update occurs, concurrency is seriously curtailed. If there are inordinate delays during the edit process (e.g., someone starts an edit then goes to lunch before they finish editing their record) or a catastrophe at the user's end (e.g., a power outage or General Protection Fault), there can be serious consequences to the usability of the affected record(s).
Of course there are times when all records in a database must be locked pessimistically. You can't have people updating a table when you need to execute a batch operation on the table's records. Table updates such as marking a given field in all records with given value, massive deletes such as removing all records for a departing employee, or any other operation that must look at all records in a file all require this kind of lock. This is usually not a problem because these batch operations usually only take seconds to complete, and such maintenance work is usually scheduled for the least busy hours of the day.
There is one other caveat worth noting about pessimistic locking. On an intranet, database processing is not that much different from traditional client/server processing. Intranets are stateless, but there are relatively few things that interfere with routine HTTP transactions. On the Internet, however, almost anything can happen; and at any given time, somewhere on the network, it is happening. Pessimistic locking in these circumstances is even less attractive on the Internet than it is on intranets.
There is one other issue beyond the threat of network interruption: latency, the natural delays inherent in a packet-switched network. For ADO development this means that any temporal issues attached to pessimistic locking are heightened when used on the Internet. Until we realize the promise of the all-fiber network, this is a fact of network life.
Strictly speaking, the topic of isolation levels belongs to a discussion of transactions, such as you read about in the "Transactions With a Huge Return on Investment" section of Chapter 16, "Working with ADO's Connection and Command Objects." Because isolation levels also are related to locking policy, however, we include the topic here. Isolation levels combine lock types with lock duration, and they give tacit recognition to the needs of the cursor.
You will read more about cursors when you learn about the fine points of opening recordsets in the section "Creating RecordSet Objects." In the context of isolation levels, cursors come into play when the data provider makes transaction decisions that minimize disruption of the data contained in existing cursors during concurrent data access.
An important objective in concurrency control is serializable interleaved execution. The term might sound a little pretentious, but it accurately describes what every good database administrator wants to see: two transactions operating on the same data whose individual outcomes leave the other's outcomes unaffected. In other words, the results of concurrent processing was the same as if the two (or more) transactions had been executed serially (instead of concurrently).
There are three kinds of problems that interfere with creating serializable transactions:
When you create a Connection Object, you also define its level of isolation. If you don't do this explicitly by assigning the value from to the Connection Object's IsolationLevel property, ADO assigns the constant for you. The default constant is about midrange and is set at adXactCursorStability. This means that transactions processed on a default connection can view changes made on other connections only after those changes have been committed. As the name adXactCursorStability implies, this policy ensures that cursors capable of viewing other cursors (set independently with the CursorType property in the RecordSet Object's Open method) don't change until they need to.
| Constant | Value | Description |
| adXactUnspecified | -1 | If the provider is using a different IsolationLevel than specified but which cannot be determined, the property returns this value. |
| adXactChaos | 16 | Indicates that you cannot overwrite pending changes from more highly isolated transactions. |
| adXactBrowse | 256 | Indicates that from one transaction you can view uncommitted changes in other transactions. |
| adXactReadUncommitted | 256 | Same as adXactBrowse. |
| adXactCursorStability | 4096 | Indicates that from one transaction you can view changes in other transactions only after they've been committed. (Default.) |
| adXactReadCommitted | 4096 | Same as adXactCursorStability. |
| adXactRepeatableRead | 65536 | Indicates that from one transaction you cannot see changes made in other transactions, but that requerying can bring new recordsets. |
| adXactIsolated | 1048576 | Indicates that transactions are conducted in isolation of other transactions. |
| adXactSerializable | 1048576 | Same as adXactIsolated. |
The information in Table 17.1 was taken from the documentation for the Supports method in ADO. This method interrogates the data provider to determine if a given function is supported. Care should be exercised in its use; sometimes support is indicated in general but might fail in particular circumstances. For example, a RecordSet Object might support the Update method, but the particular update query might not be updateable; e.g., a crosstab query.
Other transactions can be made more or less isolated. A personal favorite (due to the avocation of chaos theory) is adXactChaos, which is the lowest level of isolation. From here, you can see everything that's going on, but you can change nothing (in more highly isolated transactions). The highest level of isolation is adXactSerializable. At this level, your transactions are so isolated (and concurrency is so low) that none of the errors listed previously can occur. This is the most pessimistic kind of locking. (For more information about isolation levels and transactions, see Chapter 10, "Inserting, Updating, and Deleting Rows" in the book "Special Edition: Using ODBC2" by Robert Gryphon, et al. beginning on p. 181.)
As a point of clarity: recordsets are merely data, RecordSet Objects give us access to data through recordset cursors they create. Strictly speaking, recordsets have no properties and no methods, they are merely data. RecordSet Objects, on the other hand, encapsulate data and programming in their properties and methods, respectively.
Recordsets are important for two reasons: they move data from a storage system like Microsoft Access or SQL Server, and they accept changes we make to that data so that the data store can be updated for subsequent use. This seemingly simple functionality masks a complexity with few peers. A Database management system (DBMS) is among the most complex software systems ever devised. As you can see from our all too brief discussion of concurrency control, there is much that goes on with recordsets that we take for granted.
Figure 17.1, the same figure as Figure 16.1 in Chapter 16, "Working with ADO's Connection and Command Objects," puts the RecordSet Object in context. This chapter is devoted to the activity in box 3 of Figure 17.1, "Manage RecordSet Object," and all the children of box 3; viz., all the other activities modeled by the figures in this chapter.
Figure 17.1 depicts the inter-relationships between the three primary activities of ADO programming. The inputs to the activity we focus on in this chapter, "Manage RecordSet Object," can be either data directly fetched from a data provider, or it can be data marshaled by one or both of the other intrinsic ADO objects, the Connection Object or the Command Object.
The primary constraints on RecordSet Object management are the Connection Object and the features present in the data provider. The Connection Object is independent of any recordset that might have been created by a Connection Object and passed as input to this activity. In terms of constraints, the Connection Object tells the RecordSet Object we are managing where its data comes from. Data provider feature sets are a constraint to managing RecordSet Objects because not all features included in ADO are supported by all data providers.
Implementing RecordSet Object management tasks is the function of the object's methods and its only collection, the Fields collection. Output is always tabular data. A special kind of data is error data returned by the data provider, and this data is input for the "Manage Connection Object" activity in box 1 of Figure 17.1.
The context of managing the RecordSet Object is defined by its interaction with the other ActiveX Data Objects.
As you can see from Figure 17.2, data provided from a source is packaged as a RecordSet Object using the Server.CreateObject method. However, if you don't have an open Connection Object before you invoke this method, you produce an Error object at the data provider. Barring this oversight, you must assign the Source and ActiveConnection properties with appropriate values, and you might need to override default values of the other properties to produce the kind of cursor that you need. Once the recordset exists, a tabular representation of base table data is created for you to edit, browse, and navigate using user interface controls of your choice.
Manage RecordSet Objects.
Your choice of data controls has the same implications as all the other choices you face in your Internet development efforts. The same spectrum is there in all cases. For example, once you have a recordset and a cursor, you must choose between the intrinsic HTML FORM controls and HTML TABLEs at one end of the spectrum and data-bound ActiveX controls at the other end. Again, these choices boil down to the primary goal: reach the broadest audience or provide the richest experience.
The tabular nature of the cursor is emphasized
because ADO is capable of accessing non-ODBC data, such as text files and e-mail messages. There, semi-structured data stores are not inherently tabular. For ADO's purposes, they must be represented to your ASP application in tabular form.
Take a look at Listing 17.1, a code snippet from the Phone Message application available for download from the Working Active Server Pages Web site. The technique used in the listing requires a lot of coding but also makes the code virtually self-documenting. For the purposes of this discussion, it helps highlight important nuances that would be lost in denser code. For example, we deliberately created a rich cursor (i.e., one that you can navigate and can edit cf. the "firehose" cursor of Chapter 16, "Working with ADO's Connection and Command Objects") to demonstrate several important features of the RecordSet Object. Had we used more elegant code, you might have missed our specifically choosing a cursor type and lock type that gave us the richness we needed.
Listing 17.1 ch17.asp-Creating a RecordSet Object
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "intranet"
Response.Write(objConn &"<P>")
Set objCmd = Server.CreateObject("ADODB.Command")
Set objCmd.ActiveConnection = objConn
objCmd.CommandText = "tblPhoneMessage"
objCmd.CommandType = adCmdTable
Set objRst = Server.CreateObject("ADODB.RecordSet")
' Type mismatch if you forget to use the Set statement when assigning
' an object to the Source property
Set objRst.Source = objCmd
objRst.CursorType = adOpenKeyset
objRst.LockType = adLockOptimistic
objRst.Open
| ON THE WEB |
|
http://www.quecorp.com/asp—You can download the entire Phone Message Application from the Active Server Pages Web site. |
Remember, if you decide to use a Command Object for the RecordSet Object's Source property, be sure to assign this property a value with the Set statement. Failure to do so results in a "Type mismatch" error. (Do we sound like the voice of experience?)
Before we continue with a detailed look at cursors, we must compare them to recordsets. To help clarify the relationship, consider a Select query in Microsoft Access. Specifically, when you execute a query in Access, you see as many records as will fit inside the query window. You might see 30 records, but there might be 3,000 records in the recordset. In one sense, the 30 records out of 3,000 available represent the cursor of the recordset. To be sure you get this, think about the table that the query was built on; imagine that the table had 30,000 records in it. In all three cases the same essential thing is happening: we reduce available data. The query fetched only those records of interest; the cursor displayed only those records that would fit in a window.
Sometimes a cursor is intended to mean a pointer to the current row of a recordset. To avoid confusion, think of this interpretation of the meaning of cursor to be the end result of the process we just described. That is, this minimalist cursor is the last reduction of data, from the set of rows that fits in a window to the single row of data we want to read or edit.
All right then, you're now ready to dive into the details of what makes cursors tick and how to create the kind of cursor that will meet your needs as you master the art of ADO programming.
Because you just created your first industrial-strength cursor in Figure 17.1, now is a good time to reflect on what you did. So you can appreciate the significance of your accomplishment, we quickly review the so-called "fire hose."
See "Managing Connection Objects" for more information about "firehose" cursors, in Chapter 16.
Both Connection Objects and Command Objects have primary responsibilities in the world of ADO. Creating cursors is a secondary duty for them. Cursors created from those two objects are meant to be used for purposes that require neither record edits nor recordset navigation. Reporting is the chief function of such cursors; use them frequently when that's all you need to do. In the PhoneMessage system, for example, a lot of reporting is done, so the Command Object is ideal for those purposes. It creates a compact cursor, and its Parameters collection makes quick work of filtering the base table to display only specific individuals' messages.
What about editing an existing record-or, for that matter, new ones? If you want, you can use a keyset cursor and the AddNew method of the RecordSet Object. For low-volume applications, that's acceptable. For high-volume Internet applicationsyou need to do a better job of working with your data provider. When performance is at stake, use the Command Object to send a SQL INSERT INTO statement to the server, altogether bypassing the need for a cursor. You saw an example of this technique in Chapter 16, "Working with ADO's Connection and Command Objects," Listing 16.6. In that listing you take memory variables from a filled out on-line form and use their values in the Command Object's Parameters collection. Because the form was originally blank, there was no cursor needed to fill in the form. To edit an existing record, however, you do need a cursor because you need to know what the old data is before you can change it.
Taking an Inventory of Cursors
Table 17.2 provides an inventory of available ADO cursor types and lists their features. Depending on what you need your application to do, you can select the correct cursor type to meet your needs.
Table 17.2 Cursor Types and Features
| CursorType | Supports Method Constants |
| adOpenForwardOnly | None (this is CursorType's default value) |
| adOpenKeyset | adBookmark, adHoldRecords, adMovePrevious, adResync |
| adOpenDynamic | adMovePrevious, |
| adOpenStatic | adBookmark, adHoldRecords, adMovePrevious, adResync |
A Library of Cursors Dynamic cursors can see everything: Edits, additions, deletions made by other users are all visible. This is because, if they are supported by the data provider, they automatically re-fetch data from base tables at specified intervals. All movement through dynamic cursors is supported. Bookmark movement (viz., the capability to move to specific records and jump over intervening records) is supported by dynamic cursors if the data provider supports bookmarks.
Keyset cursors are almost as powerful as dynamic cursors. For example, keyset cursors can see changes made by others, and navigation is as flexible. However, records added and deleted by other users are not visible to the user in keyset cursors.
Static cursors are snapshots of data. Navigation is forward and backward, and by bookmark if supported by the data provider, but unlike the keyset cursor, static cursors cannot see changes to data made by other users.
The default cursor-the forward-only kind-behaves just like the static cursor except that it can't go backward. Because this cursor doesn't have to keep track of other users' added, deleted, or changed records, and because it has to "drive" only on a one-way street, this cursor is optimized for reporting. That's why it's the cursor of choice for the Connection Objects and Command Objects.
You might find that more than one cursor type is needed for any given recordset in a single ASP application. Once a recordset is open, you cannot change the CursorType property. However, you can invoke the Close method on the RecordSet Object, change the CursorType property, and then reopen the RecordSet Object, without error.
Some data providers, such as SQL Server 6.5, support server-side cursors. Chapter 15 of Special Edition: Using Microsoft SQL Server 6.5 (Branchek et al., Que, 1996) provides an excellent discussion on this topic. As you might guess, the key advantage to server-side cursors is the absence of networking overhead. In addition, server-side cursors can take advantage of the power of the server hardware and database software.
Moving Through Cursors with Bookmarks
In the old days, the database management system kept track of record numbers. With graduation to Microsoft Access came giving up record numbers for bookmarks. Bookmarks support the same functionality as record numbers, but each exists in a different world. Indexed Sequential Access Method (ISAM) file managers have physical rows that they can reference (that's why they're called sequential); ODBC data stores don't. Structured Query Language is a set-based language, with no rows in a set. Cursors and bookmarks were invented to transform sets into rows and columns. So, whereas the order of record numbers in an ISAM file is the order in which the records were entered, cursors are in the order created by the WHERE and ORDER BY clause of the SELECT statement that created the recordset.
You have seen how cursor types define the behavior of the SQL cursor and that implementing the level of concurrency control dictated by the cursor type is not trivial. Concurrency control is such a complex process that volumes are written about it. Database designers needed flexibility in defining how this concurrency control was exercised, so that resource deployment more closely matched business needs.
Cursors are to behavior as bookmarks are to position. Keeping track of a record's position is no small matter; consequently, not all data providers support bookmarks. When they do, ADO returns a Variant array of Byte data to uniquely identify each record in the recordset. Bookmarks are a column in the recordset, column 0, and their value is usually in the key of the keyset cursor. This key is usually taken from a key field in the underlying table or from a unique index if one exists.
Setting and retrieving bookmarks is similar to doing the same thing with Session properties and Cookies. However, because not all recordsets support bookmarks, it's a good idea to wrap some error prevention code around the use of the Bookmark property, as is done in . This listing first tests for support of the Bookmark property for a given RecordSet Object.
When properties or methods are discussed, the RecordSet Object is the appropriate focus of the discussion. When values in columns and rows are of interest, the recordset is germane.
Note that this RecordSet Object variable could be using any cursor type. You might have an .asp file that uses a firehose cursor in one use and a keyset cursor in another. Both cursors could define the behavior of objRst.
If bookmarks are supported, the value of the bookmark for the current record is assigned to a memory variable. Later, say after you have enumerated all the records in your recordset, you can return to the specific record whose bookmark is stored in varBookMark simply by assigning the Bookmark property of the RecordSet Object to the value of that variable.
In this way you can navigate around the recordset at will; you don't have to simply move one record at a time, either forward or backward. You can skip any number of records in the recordset and go directly to a record of interest.
Listing 17.2 ch17.asp-Using the Bookmark Property
fBookMarkOK = objRst.Supports(adBookmark)
If fBookMarkOK Then
Response.Write("CursorType " & objRst.CursorType & " <B>supports</B>
Bookmarks<BR>")
varBookMark=objRst.Bookmark ' this stores the bookmark
Response.Write("<B>Currently on ID: " & objRst("id") & "</B><P>")
Else
Response.Write("CursorType " & objRst.CursorType & " does <B>not</B>
support Bookmarks<P>")
End If
[Other recordset programming goes here…]...
If fBookMarkOK Then
objRst.Bookmark = varBookMark ' this resets the current record
Response.Write("<B>Returned to ID: " & objRst("id") & "</B><BR>")
End If
This section describes the basic mechanism in ADO that manage recordsets one page at a time. That is, any given recordset can be partitioned into any arbitrarily sized group of records called pages. The most common use of recordset pages is for displaying data, much like a spreadsheet. Buttons are provided on the form so the user can page up or page down in the recordset as necessary.
is responsible for managing the recordset of data to be displayed by . The cursor created is the keyset variety, which means your display can be updated with values entered by other people. The updating will happen automatically as you move from page to page; or if you know someone else has changed data, or you want to confirm that the page you see has only the most current data, then you can select the Requery button to manually refresh the cursor.
The property that controls the position within the cursor is the AbsolutePage property. Changing this value tells ADO which page to go to, and it will display data beginning on whatever record is first on the indicated page. The other important property is the PageSize property. You specify it on the form, and whatever value you use becomes the denominator of a fraction that returns the number of pages that will fit with your specified page size. If a recordset has six records and you specify a PageSize of two, you will have three pages to display. If you selected a page size such as four, you would still get two pages in the PageCount property (the PageCount property is also displayed on the form). The PageSize property can be changed at any time during the life of the RecordSet Object.
Listing 17.3 CH17ABSOLUTE.ASP-Managing the recordset
<%
strAction = Request.Form("cmdMove")
intPageSize = CInt(Request.Form("txtPageSize"))
If intPageSize=0 Then
intPageSize = 3
End If
If strAction="Requery" Then
Set objRst = Session("objRst")
objRst.Requery
ElseIf Not (strAction = "PgUp" Or strAction = "PgDn") Then
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "guestbook","sa",""
Set objRst = Server.CreateObject("ADODB.Recordset")
objRst.Open "qryGuestbookListingDesc", objConn, adOpenKeyset,
adLockReadOnly, adCmdStoredProc
objRst.PageSize=intPageSize
objRst.AbsolutePage = 1
Set Session("objRst") = objRst
' Store page in session var because AbsolutePage is Write-only
Session("pg") = 1
Else
Set objRst = Session("objRst")
objRst.PageSize = intPageSize
Select Case strAction
Case "PgUp"
If Session("pg") > 1 Then
Session("pg") = Session("pg") - 1
objRst.AbsolutePage = Session("pg")
Else
Session("pg") = 1
objRst.AbsolutePage = Session("pg")
End If
Case "PgDn"
If objRst.AbsolutePage < objRst.PageCount Then
Session("pg") = Session("pg") + 1
objRst.AbsolutePage = Session("pg")
Else
Session("pg") = objRst.PageCount
objRst.AbsolutePage = Session("pg")
End If
Case Else
Session("pg") = 1
objRst.AbsolutePage = Session("pg")
End Select
End If
%>
Listing 17.4 has two sections. The top section is a FORM used to specify the size of the recordset's page. It is put at the top of the window so that it stays in place as the size of the display area changes with the PageSize property of the RecordSet Object. This FORM also includes a command button that calls the Requery method on the underlying database table. The second section displays the data for a specified page. Note the enumerating loop to display field names and later field values for all records on the current page.
Listing 17.4 CH17ABSOLUTE.ASP-Displaying the data from the recordset
<BODY>
Page Count: <%= objRst.PageCount %> Record Count: <%= objRst.RecordCount %><BR>
Current Page: <%= Session("pg") %>
<P>
<FORM ACTION=ch17absolute.asp METHOD="POST">
Enter number of records per page: <INPUT TYPE="TEXT" NAME="txtPageSize" VALUE="<%= intPageSize %>" SIZE=5>
<%
If Session("pg") > 1 Then
'Only show buttons that are appropriate %>
<INPUT TYPE="Submit" Name="cmdMove" Value="PgUp">
<%
End If
If Session("pg") < objRst.PageCount Then %>
<INPUT TYPE="Submit" Name="cmdMove" Value="PgDn">
<% End If %>
<INPUT TYPE="Submit" Name="cmdMove" Value="Requery">
</FORM>
<TABLE BORDER=1 CELLSPACING=0 CELLPADDING=3>
<TH>REC#</TH>
<%
For Each x in objRst.Fields %>
<TH><%= UCase(x.Name) %></TH>
<%
Next
' Enumerate the fields collection
' within a loop that increments through the recordset's rows.
For j = 1 to objRst.PageSize %>
<TR>
<TD VALIGN="Top"><%= j %></TD>
<% For Each x in objRst.Fields %>
<TD VALIGN=TOP><%= x.Value %></TD>
<% Next %>
</TR>
<%
objRst.MoveNext
If objRst.EOF Then
' Don't try to print the EOF record.
Exit For
End If
Next
%>
</TABLE>
</BODY>
</HTML>
ADO is alluring. It packs an incredible amount of functionality in a comparatively simple language. It does several things for you without your even asking; e.g., it updates current edits as soon as you move the current record pointer with the Move method. Its methods and properties are elementary and straightforward. And it's a lot of fun to code.
If you're waiting for the other shoe to drop, here it comes: If you're a programmer, ADO could lure you into complacency. If you're not a programmer, ADO could mislead you into forgetting that performance still comes from solid understanding-not from taking shortcuts.
In some respects, programming in ADO is too easy, at least at first. That is, if you're looking for the mythic Edit method, stop-it doesn't exist (although that didn't stop two of this book's authors from searching for it!). If you expect to call the Update method every time you call the AddNew method, don't bother (unless you're passing arguments to the Update method, in which case you need to call UpdateBatch instead). ADO is smart. If you start editing a record and then move that record's pointer, ADO doesn't abandon the edit (as Microsoft Access does); it calls Update for you to complete the transaction. After a while, you start feeling like Colonel Blake from "Mash" when his orderly, Corporal "Radar," would always finish the Colonel's commands before the Colonel could; that is, Radar would have the results for the Colonel before the Colonel was finished asking for it.
At the other extreme, you might be beguiled by the Filter property-there's just so much you can do with it, and you never have to get near SQL Server. But just try using a Filter on a client/server data provider (even the AddNew method is problematic in this regard). Again, you have something so easy and direct; when it even calls the Update method for you, well, it's almost like the program is coding itself. But when used on a busy Internet site, the true cost of this kind of effortless coding comes back to haunt you. ADO's methods can choke under pressure.
Finally, you might be tempted to rely on a method like Supports. It's wonderful when you need information such as what a data provider can do for your program and which features the data provider supports, but this doesn't reduce your responsibility for knowing two things at design time: your database schema and your data provider's capabilities. What we call this design-time design means design your program at design time, not at runtime. If you data provider supports bookmarks, it always will do so. Once you know that, don't test for it in your code.
Having warned you of their dangers, we now advise you to capitalize on the assets just described. Use these features to get to know your data provider and your new program's design. But when you go into production, strip out the fluff and get ruthless with bandwidth. Until we're all 100 percent fiber, we have no choice. We have a moral obligation to be as efficient as we possibly can.
Now let's get on with navigating recordsets. Figure 17.3 expands on the activity depicted in box 2 of Figure 17.2. It consists in three activities, reducing, caching, and moving records. Filtering records is one way to reduce a recordset to a smaller number of records. Filtering the recordset of a RecordSet Object is constrained by the value of the RecordSet Object's Filter property.
Caching records is only germane when you are dealing with a forward-only cursor. The only constraint on this activity is the value of the CacheSize property of the RecordSet Object. Updating the cursor with the new number of records is accomplished by the Requery method. This new cursor can be an input to the last activity in Figure 17.3.
These are the activities involved in navigating a recordset.
Movement within a recordset is constrained only by the beginning and end of the recordset. That is, you cannot move to a record previous to the beginning of the recordset nor past the end of the recordset. You can, however, write VBScript code to sense the BOF and EOF properties of the RecordSet Object and then move to the last record (when BOF is true) or the first record (when EOF is true) accordingly.
When the technology is appropriate (for example, a small intranet hosted by Microsoft Access), few ADO methods can match the protean power of the Filter property. The Delete, UpdateBatch, Resync, and CancelBatch methods can use it. You can filter a recordset to include only those records that were edited but uncommitted. You can filter recordsets for records that you have never seen or touched, and it can filter records you can see in your Web client based on their Bookmark property. It's fast and easy to use. It just can't be used with impunity.
There are two different groups of filters you can use in your programs. First, there are filters that use a criterion constant. These filters are used to reduce a recordset by some current attribute; e.g., records you have edited in batch mode but have not yet sent to the server. For instance, you might need to delete dozens of detail records from an invoice. You will probably want to confirm that those you deleted are those that need to be deleted, so you could filter your customer's invoice recordset to show only deleted records. That way, if you see a mistake, you can fix it before the server updates the underlying table. ADO offers you three filter constants, and they are listed in Table 17.3; you can only select one criterion at a time.
The second group of filters use a criteria string. These filters are used to reduce a recordset to one that matches one or more attributes. For example, you can filter for all invoice records whose stock status is "back-ordered" or all records whose quantity is greater than 3 and whose value exceeds $100. You can select as many attributes as necessary.
Keep these two kinds of Filter properties separate. Though you can filter filtered recordsets successively, don't expect to do that if, for example, you filter a recordset with a criteria string and then filter that cursor (namely, the result of a filter) using a criterion constant. You won't get the result you expect.
Filtering with Criterion Constants
Filtering recordsets with constants usually is done in the context of one of the methods included in the Comment column of Table 17.3. In this section, you will see the code snippets that set up the filter. The code that uses it is listed in each of the following sections related to each method.
Table 17.3 Criterion Constants for the Filter Property
| Constant | Value | Comment |
| adFilterNone | 0 | Removes all filters from a recordset. Equivalent to a zero-length string. |
| adFilterPendingRecords | 1 | Views only records changed in batch mode and not yet sent to the server. |
| adFilterAffectedRecords | 2 | Views only records changed with CancelBatch, Delete, Resync, or UpdateBatch methods. |
| adFilterFetchedRecords | 3 | Views only records in local memory from the cache of the last database fetch. |
The first new property you need to become familiar with when filtering is the recordset's CacheSize property. The unusual thing about this property is that it is read/write even when the recordset is open. Most properties can be set only on a closed object.
Setting the CacheSize property affects only the next filter. That is, the contents of the current filter are unaffected by changing CacheSize.
What Is a Cache? When you open a RecordSet Object, the data provider retrieves data from the data source and buffers this data in an area of memory called a cache. This is the local memory available to ADO that contains the actual cursor of data. The current record pointer tells the data provider when it's time to go back to the RecordSet Object and get more data for the cursor. Forward-only cursors restrict this fetch to one direction.
All this happens behind the scenes. The process becomes important, however, when you're navigating a recordset, because the cache size determines how far you can roam.
Changes made concurrently by other users are invisible to your cache (the other user has his or her own cache). That is, if someone else changes data in the table that your cache came from, you won't see it automatically. To update your cache, tell the data provider to refresh the underlying RecordSet Object's contents with a call to Resync or Requery.
Note that Lisitng 17.5 is interesting for several reasons. The cursor returned by the Connection Object-the fire hose cursor-is forward/read-only, and its default CacheSize property is equal to 1. However, as with a statistical sample, a filter of 1 is no filter at all-you need more records. You get them by increasing the CacheSize property. Because you can't set the CacheSize property of a Connection Object recordset before it is opened, you set it afterward and then Requery the database to get the records that you want.
Listing 17.5 ch17AddNew.asp-The CacheSize and Filter Properties
<%
set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "driver ={Microsoft Access Driver (*.mdb)};dbq=c:\data\intranet\intranet.mdb"
set objRst = objConn.Execute("tblPhoneMessage")
objRst.CacheSize = 10
objRst.Requery
objRst.Filter = adFilterFetchedRecords
%>
The point of this exercise is to show how the filter that is set using the adFilterFetchedRecords gets its contents. You can see that whatever is in the last batch of fetched records is represented by the adFilterFetchedRecords criterion constant. Remember, this makes sense only in the context of a batch operation such as deleting or updating records. For the rest of the story, tune in to the next section.
Another interesting feature of Lisitng 17.5 is that it uses what Kyle Geiger calls a "DSN-less" connection. All that's required to get ADO to talk to a database is using the two arguments included on the listing's second line. It's better to go to the trouble of setting up system DSNs-but it's nice to know how to avoid the trouble.
Each of the other criterion constants in can replace the one in Listing 17.5, but the filter for them is set after a batch operation such as deleting records, not after fetching records from a data provider, as is the case in Listing 17.5.
If you try to use the adFilterAffectedRecords or adFilterPendingRecords constants with the Update method, you'll raise an error. These criterion constants can be used only with the UpdateBatch method, and then only when the Filter property is assigned one of the criterion constants listed in .
Filtering with a Criteria String
Filtering for a given set of records using a criteria string is really no different than specifying an SQL statement. It is a bit more limited, though. You can use any or all of the following comparison operators: =, <>, >=, <=, like
If you use the LIKE operator, you must limit yourself to the asterisk (*) and percent sign (%) wild cards, and they must be used at the end of the string. Field names that contain spaces must be surrounded by square brackets([]). Surround text with single quotes ('') and dates with pound signs (##).
To filter a recordset to include only phone messages to mpc received today, you would use the following criteria string:
objRst.Filter = "for = 'mpc' AND on = " & Date & "#"
Filtered recordsets can be filtered even further by applying another filter to the recordset. For example, if the filter that you just applied had too many records, you could use the criteria string "caller = 'Katy'" to reduce the set to only those calls made today by Katy.
You also can use the GetRows method to fetch records. Records fetched by GetRows go into an array, not a recordset. Therefore, you "navigate" the recordset indirectly. Each row and column in the array represent a record and field. This technique can be especially handy when you want to grab and display a lot of records from a database. Another advantage of the GetRows method is that the "records" are more persistent than a cursor. You can pass them around between VBScript functions or subroutines, for example. By using the optional Fields argument, you can select a given set of fields, as well as records. Lisitng 17.6 is an example from the PhoneMessage ASP application, which you can download from the Working with Active Server Pages Web site.
Listing 17.6 msgupd8.asp-An Example of the GetRows Method
Dim aFields(6)
aFields(0)="ID"
aFields(1)="FOR"
aFields(2)="ON"
aFields(3)="CALLER"
aFields(4)="OF"
aFields(5)="PHONE"
aFields(6)="MESSAGE"
aMessages = objRst.GetRows(adGetRowsRest, ,aFields)%>
<TABLE BORDER=0 CELLSPACING=2 CELLPADDING=3>
<TR BGCOLOR="#800000">
<% For Each col in aFields %>
<TH><FONT SIZE="2" COLOR="#FFFFFF" FACE="Arial">
<%= col %>
</FONT></TH>
<% Next %>
</TR>
<%For row = 0 to UBound(aMessages,2)%>
<TR>
<%For col = 0 to UBound(aMessages,1)%>
<TD VALIGN="Top">
<FONT SIZE="2" FACE="Arial">
<% If col=0 Then%>
<A HREF="phonemsg.asp?ID=<%= aMessages(col,row) %>">
<%= aMessages(col,row) %></A>
<% Else %>
<%= aMessages(col,row) %>
<% End If %>
</FONT>
</TD>
<% Next %>
</TR>
<% Next %>
</TABLE>
The Move method family completes this section's discussion on methods. These methods all are simple. Here's the basic syntax:
objRst.Move Delta StartBookmark
The Delta argument can be any positive or negative number. If you move past the beginning or end of a recordset, you generate an error. Remember that the beginning of the recordset is one position before the first record, and the end of the recordset is the one position after the last record. These positions return the BOF and EOF properties, respectively.
The StartBookmark argument tells the Move method where to begin the move operation. You can only pass this argument if the RecordSet Object supports the Bookmark property. If it does, then the Move method first goes to the record whose bookmark equals the current value of the RecordSet Object's Bookmark property. From there, the Move method goes forward or backward the number of records given with the Delta argument.
Forgetting to append the object variable to the Move method generates a "Type mismatch" error.
The other Move methods are simplified versions of the general case. In their cases, instead of passing arguments, you append the qualifier to the name of the method. For example, objRst.MoveFirst puts you on the first record. You can guess for yourself where the MoveLast, MovePrevious, and MoveNext methods take you.
Don't let these descriptors mislead you into thinking that there are record numbers in ADO. The best way to emulate the function of a record number here is with bookmarks (if the data provider and cursor type support them).
Astute use of the CacheSize property on a recordset gives you the cached cursor that you read about in the previous section. Even a forward-only cursor can move backward if the Move -n method is called. That is, you can use a negative delta on a fire hose cursor to move backward.
In this section, you'll learn about the easy-to-program features that ADO offers the ASP developer, making recordset modification as simple as possible. Given the demands of client/server programming and the virtues of the Command Object's Parameters collection, we contend that using these features without careful thought does not serve your long-term programming interests. Once you've read this book, you'll know enough about the ASP programming and Internet development environments to make this judgment on your own.
With that disclaimer made, it's time to complete your lessons in ADO database programming. Figure 17.4 provides the details of activity box 3 in Figure 17.2. The task of modifying the recordset falls into three activities: adding, editing, deleting, and updating records. These activities are all fairly straightforward, being constrained by their respective Filter property and the feature set of the data provider.
These are the activities used to modify a recordset.
If you come from almost any other programming environment, you're accustomed to having some command or method that gets a recordset ready to accept new values. Before the idea for writing this book came about, one of its authors sent an e-mail to another one saying, "Hey, David, we're both working too hard! ADO simply forgot to include an Edit method!" If you want to change a field in a record, don't just sit there-change it!
But how? Once again, your first decision is whether to use data-bound controls or not. Intrinsic FORM controls like the TEXT and SELECT tags are not linked to any data source. The contents of these and other controls must be matched up with fields in the recordset. Each field, then, is modified directly in code like this:
objRst("message")=Request.Form("message")
One way to automate the updating of a recordset is to enumerate the recordset's fields, assigning their value from the Form collection of the Request object. The code snippet in Listing 17.7 shows you how.
Listing 17.7 MSGUD8.ASP-Automatically Updating a recordset with Form Variables
<%For Each x in objRst.Fields ' This test assumes an autoincrement field is named "ID" If x.Name<>"ID" Then x.Value=Request.Form(x.Name) End If Next%>
There is one requirement, and one warning about Listing 17.7. In order for it to work, the name of the FORM element must equal the name of the Field object. The warning is that you cannot change an AutoIncrement number in a Microsoft Access database. If you exploit that feature of Access, be sure you know the name of the field and insert it in place of "ID" in Listing 17.7. Only fields with names other than the primary index will be edited.
A related warning is if you have some other kind of field acting as a unique index, you need to add a function that tests for the uniqueness of field values for new records. If this field is also serving as a primary key, you will not be able to change it without violating the referential integrity of your database. This restriction is relaxed if you have a data provider that uses cascading deletes and edits and you have constructed your database schema to take advantage of those features.
Luckily for die-hard Access programmers, ADO retained the AddNew and Update methods. The AddNew method has the following syntax:
objRst.AddNew Fields, Values
The Fields and Values arguments have nearly the same form and exactly the same function as the INSERT INTO statement in Listing 16.6 (Chapter 16, "Working with ADO's Connection and Command Objects"). Read about using these arguments in the next section.
One difference between ADO's and Access's implementations of AddNew is that Access abandons any new record edit if the current record changes before the Update method is called. ADO calls the Update method for you to complete the transaction.
Another new twist on the AddNew method is that if you pass the Fields and Values arguments to the AddNew method, you don't need to invoke the Update method. ADO does this automatically (provided you have not set the LockType property of your RecordSet Object to adLockBatchOptimistic, in which case you would have to explicitly call the UpdateBatch method to commit the edits to the database).
When arguments are passed to AddNew, be sure that, if you have defined your RecordSet Object's LockType property with the adLockBatchOptimistic constant, you update your new records with the UpdateBatch method, not the Update method.
Once successfully added, a new record remains the current record until the record pointer is deliberately moved. If the recordset supports bookmarks, the new record remains visible and is placed at the bottom of the recordset. Without bookmarks the new record might be invisible. If so, the record can be brought back into the cursor with the Requery method or by invoking the Close and Open methods in succession (clearly, Requery is easier).
EditMode is an interesting recordset property related to the AddNew method. When you add a new record, this property is set to adEditAdd and indicates that the current record is in the copy buffer only and has not been added to the database. EditMode is important when the editing operation is interrupted (by a fire drill, lunch, a meeting) and your code needs to know if the Update method should be called.
The record-updating operation relates to the earlier discussion of concurrency control and record locking in the "Understanding Concurrency Control" section at the beginning of this chapter. Some data providers support batch updates of data to improve throughput or minimize network traffic. The choice of using the Supports method to test for data provider support versus knowing your software's capabilities in advance dictates how you implement this feature.
Except for using Update instead of AddNew, the syntax for the Update method is identical to that of the AddNew method:
objRst.Update Fields, Values
The two arguments passed to this method (and to the AddNew method) are Variants. They can be single variables or Variant arrays of data containing field names and values, respectively. Listing 17.8 is a code block that demonstrates how to create two arrays, populate them with values, and pass them as arguments to the Update method.:
Listing 17.8-Using Arrays with the Update Method
Dim aVarFields(2), aVarVals(2) aVarFields(0)="for" aVarFields(1)="caller" aVarFields(2)="on" aVarVals (0)="mpc" aVarVals (1)="Katy" aVarVals (2)=Date objRst.AddNew objRstUpdate aVarFields, aVarVals
Note the size with which the arrays were declared and the absence of parentheses when these arrays were included as Update arguments in Listing 17.8. An array of size two has three elements because all arrays in VBScript are currently zero-based. A common mistake when using arrays with the Update and AddNew methods is to include their parentheses (as you do in the Dim statement in Listing 17.8). If you include the parenthesis in Listing 17.8 you will raise an error.
If you use the UpdateBatch method instead of the immediate Update method, be sure that you have specified the adLockBatchOptimistic LockType property for your RecordSet Object, and use the UpdateBatch syntax; it's different than the immediate Update method of Listing 17.8:
objRst.UpdateBatch AffectedRecords
You cannot use the Fields and Values arguments with the UpdateBatch method.
The AffectedRecords argument can take one of the three constant values shown in Table 17.4.
Table 17.4 The UpdateBatch Constants
| Constant | Value | Comment |
| AdAffectCurrent | 1 | Sends pending changes from the copy buffer to the server. |
| AdAffectGroup | 2 | Updates only records currently filtered. |
| AdAffectAll | 3 | (Default) writes all records to the base table, including any that might be filtered. |
Issues with Updating If you need to update a record, be sure you do not use the default value for a recordset's LockType property, which is read-only. If appropriate, use adLockOptimistic. If you have the LockType set properly and still have an error when attempting to update the record, be sure you are not trying to update an AutoNumber field in Microsoft Access.
Finally, you also might encounter a "legitimate" update failure; that is, your data provider has found something wrong with your new data. Usually, this is caused by referential integrity or index violations. If you suspect this, move to your database program and try doing the update there. At the least, the error messages you get there probably will be more helpful.
By default, the Delete method affects the current record in a recordset. Alternatively, you can instruct the Delete method to remove all currently filtered records in the recordset. The default argument is adAffectCurrent, and the other option is adAffectGroup.
Here is an example code block that deletes a filtered group of records.
StrFilter = "for = 'mpc' And on = #" & Date & "#" objRst.Filter = strFilter obj.Rst.Delete adAffectGroup
You also can use the Delete method on the Parameters collection. Delete is the opposite of the Parameters collection's Append method, so the syntax looks familiar. The difference is that the Delete method accepts only the name of the parameter. For example:
objCmd.Parameters.Delete "fax"
The name of the parameter is the first argument that you gave the method when you appended it to the Parameters collection.
Remember that the deleted record remains the current record until you call one of the Move methods. If you delete a record, be sure that you move off the current record before attempting to read any fields. Moving off the current record causes the deleted record to disappear. Attempting to read data in a deleted record raises an error.
If you get nervous when writing code that deletes records, there are two ways to hedge your bets: use transactions or use batch updates. Of course, your data provider must support at least one of these techniques (Microsoft Access and SQL Server support both). If it does, you can use the Connection Object's RollbackTrans method to undo the deletes; to change your mind using batch updates, you can use the RecordSet Object's CancelBatch method. Because both techniques work in the copy buffer, it's no big deal to them to undelete a record. The original wasn't changed in the first place.
Using the CancelBatch method works even if you have moved the pointer to the current record after the delete. After you call CancelBatch, immediately move the pointer to the current record to some specific location in the recordset; for example, call objRst.MoveFirst. This way, the recordset doesn't lose its bearings.
You made it through the chapter (and probably the book), so pat yourself on the back-you've covered a lot of material.
In Part I you took the lay of the development land; you saw the highlights of almost all the different kinds of software development currently in use. From HTML to component programming to client/server development, they all come together in Active Server Pages, and ASP brings something important and unique to each of those disciplines.
In Part II you were introduced to the basics of VBScript and HTML. You learned how to construct dynamic Active Server Pages replete with functions and subroutines. You learned how to improve the performance of your HTML forms with VBScript on the client side, and how to extend that power and performance with VBScript on the server side.
In Part III you learned about all the objects and components that make up the Active Server. You saw how on the one hand, these components extended your reach with whatever programming language you felt best met your programming needs; and on the other hand, how those components pointed the way to the future of Internet programming. That way is marked by the Component Object Model (COM) and Distributed COM (DCOM). You saw how you could follow that example and extend the Active Server with components of your own design.
In Part IV you met perhaps the most challenging feature of this new world of Internet development: database programming. A whole book could easily be devoted to this material alone, but Part IV served to introduce you to the issues involved in bringing database technology to the Web and to the basics of one of the most important innovations ever to come out of Microsoft: ActiveX Data Objects. You learned how to exploit the power of the intrinsic objects in ADO: the Connection Object, Command Object, and the RecordSet Object, and how to implement the features of common database applications.
Believe it or not, there still are things we had to leave out of this book. Please refer to the fine print in the ASP/ADO documentation if you need more information.
| ON THE WEB |
|
http://www.quecorp.com/asp—In the meantime, we suggest that you add the Web site, "Working With Active Server Pages," to your Internet Explorer Favorites folder. The Web is the best source for new material (and corrections to errors you might have found in this book). As the authors move from writing a book about ASP to doing full-time ASP development, we will find things we might have done differently and better here. The Web gives us the distribution channel we need to keep this reference work as up-to-date as possible. |
Please participate in this process by sending e-mail and telling us your pioneering stories, as you move from the pages of this book to a land of new opportunity. For that's what you are, a pioneer, and we don't know a better way to describe what awaits developers as they bring HTML, good design, and component-based client/server database programming into reality as fourth-generation Web sites.
Good luck.
© 1997, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.