Data Access and Transactions
Data Access and Transactions |
|
After browsing the top ten results, users may want to view the remaining records. In this case, you don’t necessarily want to limit the records. You just want to stop after the first page is filled, and wait for the signal to continue. It’s easy enough to stop after a certain number of records. But what’s the best way to pick up where you left off?
The Recordset object exposes three properties to assist you in your task: PageSize, PageCount, and AbsolutePage. Once you have set the page size, your database provider can calculate how many pages of data it will return to you, and let you jump to locations at the beginning of each page. (If you set the CacheSize equal to PageSize, only the records you display will be cached in the RecordSet.) Note that you must use a client-side cursor to enable absolute positioning in ODBC data sources.
The following Visual Basic example uses the AbsolutePage, PageCount, and PageSize properties to display names and hire dates from the Employee table (of the Pubs database), listing five records at a time in a message box:
Public Sub AbsolutePageExample()
Dim rstEmployees As New ADODB.Recordset
Dim strCnn As String
Dim strMessage As String
Dim intPage As Integer
Dim intPageCount As Integer
Dim intRecord As Integer
strCnn = "driver={SQL Server};server=(local);" & _
"uid=sa;pwd=;database=pubs"
'Use client cursor to enable AbsolutePage property.
rstEmployees.CursorLocation = adUseClient
rstEmployees.CacheSize = 5
rstEmployees.Open "employee", strCnn
rstEmployees.MoveFirst
'Display names and hire dates, five records at a time.
rstEmployees.PageSize = 5
intPageCount = rstEmployees.PageCount
For intPage = 1 To intPageCount
rstEmployees.AbsolutePage = intPage
strMessage = ""
For intRecord = 1 To rstEmployees.PageSize
strMessage = strMessage & _
rstEmployees!fname & " " & _
rstEmployees!lname & " " & _
rstEmployees!hire_date & vbCr
rstEmployees.MoveNext
If rstEmployees.EOF Then Exit For
Next intRecord
MsgBox strMessage
Next intPage
rstEmployees.Close
End Sub
Recordset paging gets a little more complex when you are using an ASP page. Because you can return only a single page of data at a time, you’ll have to decide what to do with the open Recordset object while waiting for the next client request. You have two options—throw away the results and keep just the current page number, or stow the Recordset object in the user’s Session object for later retrieval. Which approach you choose will be based on the needs of your user and your application. You must then decide whether you want to save time by caching the results, or if you would rather release the memory for another application to use.
Note An example of Recordset paging is included on the Microsoft® Windows® 2000 Server Resource Kit companion CD, as part of the Feedback application.