ADO - ADO Recordset Paging with Client-Side Navigation
## 1. Introduction
ADO Recordset paging with client-side navigation is a technique used to display database records in smaller groups, called pages, instead of showing all records at once. ADO stands for ActiveX Data Objects, a Microsoft technology that allows applications to connect to databases, retrieve information, and manipulate records.
When a database contains thousands of records, displaying every record on a single screen can make an application slow, difficult to navigate, and confusing for users. Paging solves this problem by dividing the retrieved records into smaller sections. For example, an application containing 1,000 student records might display only 20 records on each page. Users can move between pages using navigation controls such as Next, Previous, First, and Last.
In client-side paging, the application manages page navigation using data available on the client side. Depending on the implementation, the application may retrieve the entire result set and display only the records belonging to the selected page. This reduces repeated database requests during navigation, although retrieving a very large result set can consume significant memory and network resources.
## 2. How Recordset Paging Works
ADO provides the `Recordset` object for working with rows returned by a database query. Its paging-related properties and methods can help applications organize records into manageable groups.
The `PageSize` property specifies the number of records that should appear on each page. The `PageCount` property indicates the number of pages in the Recordset when the provider supports the necessary paging information. The `AbsolutePage` property identifies the page containing the current record and can be used to move to a particular page.
For example, suppose a college management application retrieves 250 student records and sets the page size to 25. The application can display 25 records at a time, resulting in 10 pages. When a user selects page 3, the application can use the `AbsolutePage` property to navigate to that page, provided the Recordset and provider support this operation.
The number of pages can be calculated using the following formula:
\\[ \text{PageCount}=\left\lceil\frac{\text{TotalRecords}}{\text{PageSize}}\right\rceil \\]
For 250 records with 25 records per page, the result is 10 pages. If there are 260 records, the result is 11 pages, with 10 records on the final page.
## 3. Important ADO Properties Used in Paging
The following properties are useful when implementing Recordset paging in Classic ADO.
PageSize: This property specifies the number of records assigned to each page. For example, setting `PageSize = 25` means that each page contains up to 25 records.
PageCount: This property returns the number of pages in the Recordset when the provider can determine the page count. Its accuracy and availability depend on the Recordset and provider configuration.
AbsolutePage: This property identifies the page containing the current record. Setting it to a valid page number moves the current position to the beginning of that page in supported Recordsets.
RecordCount: This property reports the number of records in a Recordset when the provider and cursor type support an accurate count. Some cursor configurations may return `-1` when the record count cannot be determined.
AbsolutePosition: This property indicates the ordinal position of the current record in the Recordset when supported. It can help an application determine the record's position while navigating through data.
These properties do not guarantee that every database provider supports paging in the same way. Developers should select a suitable cursor type and verify provider capabilities before depending on page-related properties.
## 4. Example of Implementing Paging in Classic ADO
Consider a student management application that retrieves student names and identification numbers from a database. The application needs to display 10 students per page.
The following VBScript example demonstrates the basic idea of paging using an ADO Recordset. It assumes that the database connection has already been configured correctly and that the provider supports the required paging properties.
```
<%
Dim conn, rs, pageSize, pageNumber
Dim totalPages, studentName, studentID
pageSize = 10
pageNumber = 1
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open Application("StudentConnectionString")
Set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = 3 ' adUseClient
rs.Open "SELECT StudentID, StudentName FROM Students ORDER BY StudentID", _
conn, 3, 1
' Set the number of records per page
rs.PageSize = pageSize
' Check whether the Recordset supports paging
If rs.PageCount > 0 Then
totalPages = rs.PageCount
If pageNumber >= 1 And pageNumber <= totalPages Then
rs.AbsolutePage = pageNumber
Response.Write ""
Response.Write "
"
Response.Write "
Student Records
" Response.Write "| ID | Name |
|---|---|
| " & studentID & _ " | " & Server.HTMLEncode( _ CStr(studentName)) & " |
Page " & pageNumber & _ " of " & totalPages & "
" Else Response.Write "Invalid page number." End If Else Response.Write "Paging information is unavailable." End If rs.Close Set rs = Nothing conn.Close Set conn = Nothing %> ``` In this example, the application opens a Recordset containing student information and sets its page size to 10. It then checks the total page count and moves to the requested page using `AbsolutePage`. The loop displays no more than 10 records before stopping. The example fixes `pageNumber` at 1 to demonstrate the first page. In a complete application, the page number would come from a validated request parameter, and the application would generate navigation links for moving between pages. User input should be validated to ensure that the requested page number is an integer within the permitted range. The example also uses a client-side cursor location, but support for `PageCount` and `AbsolutePage` should be verified for the selected ADO provider and cursor configuration. The code is illustrative and may require provider-specific adjustments. ## 5. Implementing Navigation Controls Client-side paging becomes more useful when users can move between pages without manually changing the page number in the source code. Applications commonly provide four navigation controls. First Page: Moves the user to the first page of records. Previous Page: Moves the user to the page immediately before the current page, provided the current page is not the first page. Next Page: Moves the user to the page immediately after the current page, provided the current page is not the last page. Last Page: Moves the user to the final page of available records. For example, if a user is viewing page 4 of a 10-page student list, selecting Previous moves the user to page 3, while selecting Next moves the user to page 5. The application should disable or omit Previous on the first page and Next on the last page. In a Classic ASP application, the selected page number can be passed through a query-string parameter such as `?page=4`. The server validates this value and sets `AbsolutePage` accordingly. Although the navigation request may be sent to the server again, the application can navigate through an already-retrieved client-side Recordset without executing the original database query again, provided the data remains available and the application retains the relevant state. A normal HTTP request does not automatically preserve an ADO Recordset between requests, so an implementation must account for this limitation. ## 6. Advantages of Client-Side Recordset Paging Client-side paging improves the presentation of database information by displaying a limited number of records at a time. This makes tables easier to read and allows users to locate information without scrolling through a long list. It can also reduce repeated database queries during page navigation when the complete result set is already available on the client side. For small and medium-sized datasets, this approach can provide a straightforward way to implement navigation in legacy applications. Another advantage is improved user experience. Developers can display the current page number, total page count, record ranges, and navigation controls. For example, a page may show “Records 21–40 of 250,” helping users understand their position within the dataset. However, client-side paging does not automatically reduce the amount of data initially retrieved. If an application loads a million records into memory and then displays only 20 at a time, it can still consume excessive resources. ## 7. Limitations and Best Practices The main limitation of client-side paging is that the application may need to retrieve and retain the entire result set before navigation begins. This can increase memory consumption, network traffic, and initial loading time when working with large databases. Paging properties can also behave differently depending on the cursor type and OLE DB or ODBC provider. Developers should check whether `PageSize`, `PageCount`, and `AbsolutePage` behave as expected instead of assuming that every Recordset supports them identically. Another consideration is data consistency. If records are inserted, deleted, or reordered while a user navigates between pages, the displayed results may become inconsistent with the underlying database. Using a stable `ORDER BY` clause helps maintain predictable ordering. For large datasets, server-side paging is often more efficient. Instead of retrieving every record, the application requests only the rows needed for the current page. Depending on the database, this can be implemented using SQL features such as `OFFSET` and `FETCH`, or other provider-specific techniques. These approaches are distinct from using the ADO `AbsolutePage` property on a client-side Recordset. Developers should also validate page-number inputs, handle empty result sets, close database resources correctly, and encode database values before displaying them in HTML. ## 8. Conclusion ADO Recordset paging with client-side navigation is a useful technique for organizing database records into smaller, manageable pages. By using properties such as `PageSize`, `PageCount`, and `AbsolutePage`, developers can build navigation systems that allow users to browse database information more conveniently. This approach is particularly suitable for legacy applications and relatively small datasets when the selected provider supports the necessary paging features. For very large datasets, server-side paging is generally preferable because it retrieves only the records required for the selected page. Understanding the differences between these approaches helps developers create database applications that are easier to use, more efficient, and more reliable.Sign In
Sign Up
Forgot Password