When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
ASPFAQs.com
Message Board
Related Web Technologies
User Tips!
Coding Tips

Sections:
Sample Chapters
Commonly Asked Message Board Questions
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Security
Stump the SQL Guru!
XML Info
Information:
Feedback
Author an Article
ASP ASP.NET ASP FAQs Message Board Feedback
Print this page.
Published: Saturday, December 12, 1998

Paging through Database Results N Records at a Time
By Scott Mitchell


A More Up-To-Date Paging Solution
For a more up-to-date look at efficiently paging through large resultsets, see Greg Hamilton's article A More Efficient Method for Paging Through Large Result Sets.

One of ASP's great assests is that it can connect to a database on the server and report the results of a query to a user. If the queries are small, the page loads quickly, and there isn't too much data to wade through. However, if there are hundreds of thousdands of results being returned by the database, attempting to display all that data on one page would not only be difficult for the reader to utilize, but would also be very slow to execute!

There is a solution, however. Using ADO you can page through data N records at a time. The records are retrieved N at a time, so the query time will be shortened considerably, plus the data will be easier to examine and use. We will be using ClientSide cursors (meaning that the recordset cursor will be stored on the client's side, as opposed on the server (which is the default), the CacheSize property (to specify how many records to cache on the cursor), and the paging properties: AbsolutePage, PageSize, and PageCount.

Let's start by looking at some code. Remember to include ADOVBS.inc. (If you don't have it, it can be downloaded here. This program assumes that ADOVBS is located in your web application's root directory and that the file is named "thisfile.asp".)

<%@ LANGUAGE="VBSCRIPT" %>
<% Option Explicit %>
<!--#include virtual="/ADOVBS.inc"-->
<%
    'Set how many records per page we want
    Const NumPerPage = 10

    'Retrieve what page we're currently on
    Dim CurPage
    If Request.QueryString("CurPage") = "" then
        CurPage = 1 'We're on the first page
    Else
        CurPage = Request.QueryString("CurPage")
    End If

    Dim conn
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.Open "DSN=MyDB"

    'Explicitly Create a recordset object
    Dim rs
    Set rs = Server.CreateObject("ADODB.Recordset")

    'Set the cursor location property
    rs.CursorLocation = adUseClient

    'Set the cache size = to the # of records/page
    rs.CacheSize = NumPerPage

    'Open our recordset
    Dim strSQL
    strSQL = "SELECT Name,Salary FROM Employee ORDER BY Name"
    rs.Open strSQL, Conn

    rs.MoveFirst
    rs.PageSize = NumPerPage

    'Get the max number of pages
    Dim TotalPages
    TotalPages = rs.PageCount

    'Set the absolute page
    rs.AbsolutePage = CurPage

    'Counting variable for our recordset
    Dim count
%>

<HTML>
<BODY>
<B>Name - Salary</B><BR>
<%
    'Set Count equal to zero
    Count = 0
    Do While Not rs.EOF And Count < rs.PageSize
        Response.Write(rs("Name") & " - " & rs("Salary") & "<BR>")
        Count = Count + 1
        rs.MoveNext
    Loop

    'Print out the current page # / total pages
    Response.Write("Page " & CurPage & " of " & TotalPages & "<P>")

    'Display Next / Prev buttons
    if CurPage > 1 then
        'We are not at the beginning, show the prev button
        Response.Write("<INPUT TYPE=BUTTON VALUE=PREV ONCLICK=""document.location.href='thisfile.asp?curpage=" & curpage - 1 & "';"">")
    End If

if CInt(CurPage) <> CInt(TotalPages) then
        'We are not at the end, show a next button
        Response.Write("<INPUT TYPE=BUTTON VALUE=NEXT ONCLICK=""document.location.href='thisfile.asp?curpage=" & curpage + 1 & "';"">")
    End If

%>

</BODY>
</HTML>

Let's quickly go over some of those ADO properties we used. Setting PageSize to an integer value tells ADO how many records per page you are wanting. PageCount, then, tells you how many pages total there are. Obviously you need to set PageSize before PageCount will yield a sensible result. (This is not strictly true, since PageSize defaults to 10, but always be sure to set PageSize explicitly if you plan on using paging!) AbsolutePage is a value you can set to tell ADO to get a certain page. So, if you have pages x through y with N records per page, you could say, AbsolutePage = M, and the recordset cursor will start at the M*Nth record (assuming M < y).

The CacheSize tells ADO to cache N number of rows on the cursor. Since we chose to have 10 records per page, I set the cache size to 10. ADO will grab the first 10 records, and cache those. It won't hit the database until you request any past the cached limit. Since we are only displaying 10 records per page, we are only having one quick database hit per page. The caching property is vital to the performance of the script above.

Hopefully you now understand how to page through data using ADO / ASP. Be forewarned, you can only page through certain cursor types. For example, you cannot page through a forward-only cursor (which is the default for server side cursors). Paging allows a quick and visually appealing way for you to display results from a query to your users!

Happy Programming!

  • By Scott Mitchell


  • ASP.NET [1.x] [2.0] | ASPMessageboard.com | ASPFAQs.com | Advertise | Feedback | Author an Article