Paging Through Records from ASP

From Mynoteswiki.com

J.D. Meier

Posted May 22, 2000

Contents

[edit]

Introduction

Displaying a large recordset in an Active Server Pages (ASP) application is a problem you probably are familiar with. This article provides insight into that problem, a solution, and sample code that can be modified easily to work for your particular situation. The sample code is designed to be a server-side solution that is browser independent. Along the way, I will point out issues that you need to consider when designing your solution.

[edit]

The Problem

Your query returned a big recordset. You need to provide a simple way to browse through the results, displaying only a subset of the results on each page. Doing this efficiently requires insight into how ActiveX® Data Objects (ADO) and your database work together.

[edit]

The Solution

How do you partition your recordset into "pages" rather than one large result? A page is basically a number of records that you specify should be displayed together. For example, if you have 100 records in your recordset, you might show 10 records per page.

ADO provides two methods, PageSize and AbsolutePage. These methods allow you to specify the number of records you want per page, and to position the cursor at the beginning of a page. Once you have opened your recordset, the basic steps are: 1. Specify a PageSize for the recordset. This will indicate the number of records to display per page. 2. Specify the AbsolutePage for the recordset. This will move the record pointer to the start of a given page, in a sequence of pages. 3. Display the page of records. To do this, you loop through your recordset as many times as you set your PageSize to, or until you hit the end of the file.

[edit]

Sample Code

The following sample code illustrates paging in action. You can use it to help you build a prototype of your own solution. In your own code, be sure to do the following:

  • Add error handling.
  • Add a limit on the number of records returned by your query.
  • Filter records using criteria (for example, build a WHERE clause).
  • Use a stored procedure or view.

Be sure to modify my sample code to point to your database by changing the connection string and SQL statement. Because the code uses ADO constants, such as adUserServer, be sure to reference the ADO TypeLibrary in your Global.asa file, or to include the ADOVBS.INC file in your ASP page. Note that Visual InterDev® generates the TypeLibrary reference for you automatically when you set a project reference to Microsoft ADO.

Note that the sample has two methods for providing a navigation bar:

  • ShowNavBar. This method provides a way for users to jump to specific pages along with the record count (see Figure 1). To do so, it uses the RecordCount and PageCount properties.
  • ShowNavBarFast. This method does not provide the ability to jump to specific pages, and it does not provide a record count, but you have control over the number of records fetched through the CacheSize property (see Figure 2).

PageThroughRs.Asp 

  <%@ Language=VBScript %>
  <% Option Explicit %>
  <SCRIPT LANGUAGE=VBScript RUNAT=SERVER>
  'Be sure to reference ADO Typelib or use ADOVBS.Inc
  Dim iPageNum, iRowsPerPage

  Main
     Sub Main()
  Dim rst
  Dim sSQL, sConnString

     If Request.QueryString("iPageNum") = "" Then
        iPageNum = 1
     Else
        iPageNum = Request.QueryString("iPageNum")
        iPageNum = CInt(iPageNum)
     End If

     iRowsPerPage = 10



     sConnString = "Provider=SQLOLEDB.1;password=Xyz123;user id=WebUser;" & _ 
        "Initial Catalog=NorthWind;Data Source=MySQLServer;" & _ 
        "network=dbmssocn;" 

     'The following SQL retrieves all columns from a SQL View.
     'To optimize performance:
     '- use a stored proc, a view, or specify columns in the SELECT
     '- use criteria to limit records returned (e.g. a WHERE clause)
     sSQL = "SELECT CategoryName, ProductName, QuantityPerUnit,"
     sSQL = sSQL & "UnitsInStock, Discontinued"
     sSQL = sSQL & " FROM [Products By Category]"

     Set rst = GetRecords(sConnString, sSQL)

     WriteTableHeader rst
     WriteTableBody rst, iRowsPerPage, iPageNum
     ShowNavBar rst

     'The ShowFastNavBar method does not use RecordCount
     'or PageCount so it only retries as many records
     'as dictated by the CacheSize of the Recordset

     'ShowFastNavBar rst

     CleanUp rst
  End Sub

  Function GetRecords(sConnString, sSQL)
  Dim cnn
  Dim rst

     set cnn = Server.CreateObject("ADODB.CONNECTION")
     cnn.ConnectionString = sConnString
     cnn.Open

     Set rst = Server.CreateObject("ADODB.RECORDSET")

     Set rst.ActiveConnection = cnn

     'CursorLocation of adUseClient would retrieve
     'all records when the Recordset is opened.
     'adUseServer allows us to leverage the CacheSize
     rst.CursorLocation = adUseServer

     'CacheSize limits rows fetched when using a 
     'server-side cursor. We'll only grab the number
     'of records we are displaying - iRowsPerPage
     rst.CacheSize = iRowsPerPage

     rst.Open sSQL,,adOpenStatic, adLockReadOnly    

     Set GetRecords = rst
  end Function

  Sub WriteTableHeader(rst)
  Dim fld
Response.Write "" Response.Write "" 'Build column titles for the table For Each fld in rst.Fields Response.Write "" 
     Next
Response.Write "" End Sub Sub WriteTableBody(rst, iRowsPerPage, iPageNum) Dim iLoop Dim fld iLoop = 1 rst.PageSize = iRowsPerPage rst.AbsolutePage = iPageNum 'Write out the current page of records Do While (Not rst.EOF) and (iLoop <= iRowsPerPage) Response.Write "" For Each fld in rst.Fields Response.Write "" 
           Next
        iLoop = iLoop + 1
        rst.MoveNext
Response.Write "" Loop Response.Write "
" & fld.Name & "
" & fld.value & "
" 
  End Sub

  Sub ShowNavBar(rst)
  Dim iPageCount
  Dim iLoop
  Dim sScriptName

     'This version provides richer user navigation but
     'relies on RecordCount and PageCount, which negate
     'the benefits of specifying a CacheSize for a
     'Server-side cursor.

     Response.Write "

"
     sScriptName = Request.ServerVariables("SCRIPT_NAME")

     If iPageNum > 1 Then
        Response.Write " <a href=" & sScriptName & "?iPageNum="
        Response.Write (iPageNum -1) & "><< Previous</a>"
     End If

     iPageCount = rst.PageCount
     Do Until iLoop > iPageCount
        If iLoop = iPageNum Then
           Response.Write " " & CStr(iLoop) & ""
        Else
           Response.Write " <a href=" & sScriptName & "?iPageNum=" & _
           Cstr(iLoop) & ">" & iLoop & "</a>"
        End If
        iLoop = iLoop + 1
     Loop

     If Not rst.EOF Then
        Response.Write " <a href=" & sScriptName & "?iPageNum="
        Response.Write (iPageNum +1) & "> Next >></a>
"
     Else
        Response.Write "
"
     End If

     Response.Write "Page " & iPageNum & " of " & iPageCount & "
"
     Response.Write rst.RecordCount & " Records"     
  End Sub

  Sub ShowFastNavBar(rst)
  Dim iPageCount
  Dim iLoop
  Dim sScriptName

     'This method is very efficient when specifying a CacheSize
     'and using a Server-side cursor, because it does not
     'use RecordCount and PageCount. User experience is sacrificed.

     Response.Write "

"
     sScriptName = Request.ServerVariables("SCRIPT_NAME")

     If iPageNum > 1 Then
        Response.Write " <a href=" & sScriptName & "?iPageNum="
        Response.Write (iPageNum -1) & "><< Previous</a>"
     End If

     If Not rst.EOF Then
        Response.Write " <a href=" & sScriptName & "?iPageNum="
        Response.Write (iPageNum +1) & "> Next >></a>
"
     Else
        Response.Write "
"
     End If

     Response.Write "Page " & iPageNum

  End Sub

  Sub CleanUp(rst)
     If Not rst Is Nothing then
        If rst.state = adStateOpen then rst.close
        set rst = nothing
     End If
  End Sub

</SCRIPT>

[edit]

Analysis

There are several issues to keep in mind when designing a paging solution:

  • Cursor location matters. If you use a client-side cursor, all the records will be read each time you open the recordset. Consequently, because all the records are read, accessing the RecordCount or PageCount property later is fast. If you use a server-side cursor, records are retrieved as they are needed. You can specify the number of records read at a time through the CacheSize property to improve performance. However, if you use the RecordCount or PageCount properties with a server-side cursor, all the records will be read, canceling the performance gain. You must balance having a user interface with more information and richer navigation against having the performance impact of retrieving all your records.
  • When you use a server-side cursor, the CursorType property needs to be adOpenStatic or adOpenKeyset to use paging.
  • Paging is not always an appropriate user interface. Appropriate scenarios might be situations in which users are scanning results from a search engine or browsing a product catalog.
  • Try to sort records so that more relevant records appear in the first few pages (for example, use SQL's ORDER BY clause). Users can wade through only so much.
  • Retrieve only the columns you need to display (e.g., avoid SELECT *).
  • Retrieve only the records you need to display. Be sure to filter criteria (e.g., use a WHERE clause).

Here are a few additional tips to remember:

  • Encapsulate your logic in methods. Using methods has allowed me to separate presentation logic from data access logic, which simplifies porting the code to Windows Script Components, Visual Basic Scripting Edition (VBScript) classes, or components. Changes to functionality are easier, and code maintenance is improved. Testing and debugging are also improved, because I can comment and uncomment method calls.
  • Referencing ADO's TypeLibrary is a better solution than including ADOVBS.INC. This is because ASP reads the entire file into memory when it processes includes, not just what it needs.
[edit]

Conclusion

Paging is a common technique that many Web applications use to provide a nice way to browse a large number of records. When you design a paging solution, you need to consider a number of issues, such as how records are retrieved and what type of user navigation you need to provide. While the best solution depends on factors of your particular application, applying the techniques in this article will help you to make better design decisions.

J.D. Meier was born and reared on the U.S. East Coast. Since heeding Horace Greeley's advice, he has worked as a Developer Support Engineer specializing in server-side components and Windows DNA applications involving MTS and ASP technology.

Retrieved from "http://www.mynoteswiki.com/wiki/Paging_Through_Records_from_ASP"