The Developer's Resource & Community Site

COM	XML	ASP	Java & Misc.	NEW: VS.NET
International	This Week	Forums	Author Central	Find a Job

A response to a question to the Gurus

Author: David Sussman
Level of Difficulty:
Languages covered: ASP
Pre-required reading: Ask the Gurus
Date: January 21st, 2000

Introduction

As part of iDevResource.com's commitment to our users, we make our Guru's available to answer your programming questions, and offer a little help towards solving your problems. You can find the ASP gurus here.

A question was posted to the Gurus recently that asked for help with a problem that turned out to have a very useful solution. Therefore, we have turned it into an ASP Byte Size article for all to read..

Question

Posted by Dylan Snopkowski:

I am trying display more records than I can fit on one page. I have seen several websites where search results are returned with the first ten displayed and links to records 11-20, 21-30, etc. How is this done?

Answer

This can be done in a variety of ways, but by far the easiest is to use an ADO Recordset and use its intrinsic paging abilities. This revolves around the ability of the Recordset to define a page as a set number of rows, and moving between pages is simply a matter of setting the page number.

The Recordset properties you need to deal with are:

In ASP you can do this in the following way:

<%
Set rsData = Server.CreateObject("ADODB.Recordset")
strConn = "Provider=SQLOLEDB; Data Source=your_server; " & _
          "Initial Catalog=pubsl User Id=sa; Password="
' set the page size and use the client cursor library
rsData.PageSize = 5
rsData.CursorLocation = adUseClient
' open the data
rsData.Open "authors", strConn, _
adOpenForwardOnly, adLockReadOnly, adCmdTable
' set the current page number
' get the requested data
If Request.QueryString("PAGE") = "" Then
  iPage = 1
Else
  ' protect against out of range pages, in case
  ' of a user specified page number
  iPage = CInt(Request.QueryString("PAGE"))
  If iPage < 1 Then
    iPage = 1
  Else
    If iPage > rsData.PageCount Then
      iPage = rsData.PageCount
    End If
  End If
End If
' start building the table
Response.Write "<TABLE BORDER=1><THEAD><TR>"
For Each fldF In rsData.Fields
  Response.Write "<TD>" & fldF.Name & "</TD>"
NextResponse.Write "</TR></THEAD><TBODY>"
' now loop through the records
For iRec = 1 To rsData.PageSize
  If Not rsData.EOF Then
    Response.Write "<TR>"
    For Each fldF In rsData.Fields
      Response.Write "<TD>" & fldF.Value & "</TD>"
    Next
    Response.Write "</TR>"
    rsData.MoveNext
  End If
Next
Response.Write "</TBODY></THEAD></TABLE><P>"
' now some paging controls
sMe = Request.ServerVariables("SCRIPT_NAME")
' only give active first page if we aren't already on it
If iPage = 1 Then
  Response.Write " <SPAN>First Page</SPAN>"
Else
  sRecs = " (1-" & rsData.PageSize & ")"
  Response.Write " <A href=" & sQuote & sMe & "?PAGE=1" & _
                 sQuote & ">First Page " & sRecs & "</A>"
End If
' only give an active previous page if there are previous pages
If iPage = 1 Then
  Response.Write " <SPAN>Previous Page</SPAN>"
Else
  iRec = rsData.PageSize * iPage - 1
  sRecs = " (" & iRec - rsData.PageSize & "-" & iRec & ")"
  Response.Write " <A href=" & sQuote & sMe & "?PAGE=" & iPage - 1 & _
                 sQuote & ">Previous Page" & sRecs & "</A>"
End If
' only give an active next page if there are more pages
If iPage = rsData.PageCount Then
  Response.Write " <SPAN>Next Page</SPAN>"
Else
  iRec = rsData.PageSize * iPage + 1
  sRecs = " (" & iRec & "-" & iRec + rsData.PageSize & ")"
  Response.Write " <A href=" & sQuote & sMe & "?PAGE=" & iPage + 1 & _
                 sQuote & ">Next Page" & sRecs & "</A>"
End If
' only give active last page if not already on it
If iPage = rsData.PageCount Then
  Response.Write " <SPAN>Last Page</SPAN>"
Else
  iRec = rsData.PageSize * rsData.PageCount
  sRecs = " (" & iRec - rsData.PageSize & "-" & iRec & ")"
  Response.Write " <A href=" & sQuote & sMe & "?PAGE=" & rsData.PageCount & _
                 sQuote & ">Last Page" & sRecs & "</A>"
End If
' and clear up
rsData.Close
Set rsData = Nothing
%>

(The above code is available for download in zip format here.)

This is pretty simple, although it looks quite complex. It relies on the page number being passed into the ASP script as part of the QueryString. This page number sets the current page (AbsolutePage) of the Recordset. You can then loop through the number of records defined by the size of a page (PageSize) displaying the table. The most complex looking part is the bit that displays the paging controls, but the complexity is really just building up a string to display the rows shown in the various pages.

---

Contribute to IDR:

To contribute an article to IDR, a click here.

To contact us at IDevResource.com, use our feedback form, or email us. To comment on the site contact our webmaster.

All content © Copyright 2000 IDevResource.com, Disclaimer notice