Professional ADO RDS Programming with ASP by Matt Brown, Charles Caison, John Papa, Peter DeBetta, Eric Wilson Published by Wrox Press 1999; $49.99US; ISBN: 1861001649 500 pages Sample Chapter 6: Building on the Recordset Foundation Copyright 1999. Wrox Press, reprinted with permission. The text format was modified to fit the format of this web site.

Related Links
• In-depth review of the book

Paging

Well, how do you like the book so far? No matter what your opinion of the book is, I bet you like the fact that it has many pages instead of just one big page. Because the book's information is broken into multiple pages, it makes the book easier to read and reference – the pages provide us with manageable units to work with. Now: wouldn't it be great if we had a method for looking at data one 'page' at a time?

Suppose we want to look through an employee database on the web. If we had to read down a long, seemingly interminable list of employees, it would be no fun. We'd prefer to see a nice orderly presentation of pages. It would take some custom programming to implement this – if the ADO recordset didn't give it to us for free.

There are five recordset properties that are used to implement recordset paging:

• CursorLocation: You must use a client-side cursor to enable paging (CursorLocation = adUseClient)
• PageSize: After you open your recordset, tell it a page size. How many records do you want on a page – 5, 10, 20, more? Choose how many you want. The last page will have the balance of the left-over records.
• PageCount: After you've set the page size, you can get the number of pages that your recordset makes up.
• AbsolutePage: This tells you what page you are on. You can set this number to turn to a specific page.
• AbsolutePosition: This tells you what record you are on in the recordset. You can set this number to go to a specific record or you can read it to see which record you are on.

An Example

You can use this code to view to list the people in the authors table of the pubs database – in a number of pages. We've coded three-per-page here. When the code executes the first time, we test the Session object – MyConnection – for an existing connection. If none exists, then the connection is established, the recordset is created, and the page size is set:

if not isObject(Session("MyConnection")) then
  set objConn = CreateObject("ADODB.Connection")
  'Initialize the string
  strSQL = "select * from authors"
  'Connect to database
  'You must use the Client side cursor
  objConn.CursorLocation = 3 'adUseClient
  objConn.Open "Provider=MSDASQL;" & _
    "Persist Security Info=False;" & _
    "User ID=sa;" & _
    "Data Source=winbook;" & _
    "Initial Catalog=pubs"

  'Create the recordset object
  Set objRS = CreateObject("ADODB.Recordset")
  objRS.CursorType = 3 'adOpenStatic
  Set objRS.ActiveConnection = objConn
 
  'Get the recordset and set the page size
  objRS.Open strSQL
  objRS.PageSize = 3
  set Session("MyConnection") = objConn
  set Session("MyResults") = objRS
  'The Initial Display
  Display
Else
  <deal with subsequent calls to this page, after the first>
  <we'll look at this in a moment>
End If

After setting the active connection and opening the recordset, we set the page size for three records per page. Then we cache the connection and the recordset. Most of the time you would refrain from caching a connection when possible, but for our example it's OK. Now we call the Display subroutine, and the user is shown the first page of data.

Let's have a quick look at the Display subroutine:

sub Display()
  dim intRecord
  Response.Write "<A HREF=""PageMe.asp?PrevOrNext=Prev"">Prev Page</A>"
  Response.Write "<A HREF=""PageMe.asp?PrevOrNext=Next"">Next Page</A>"
  Response.Write "<P>Page: " & objRS.AbsolutePage & "<P>"

  Session("Page")=objRS.AbsolutePage
  For intRecord = 1 To objRS.PageSize
    Response.Write objRS(2).value & " " & objRS(1).value & "<BR>"
    objRS.MoveNext
    If objRS.EOF Then Exit For
  Next
end sub
%>

This displays the user interface. The user is told what page is being displayed (on the first invocation the first page is displayed). The author's names are displayed; and the user is also provided with a couple of links that allow forward and backward direction through the pages. Of course, they can never page further than the beginning or end of the recordset.

When the page is executed subsequently (via the Next Page or Prev Page links), the request object (PrevOrNext) is read. If this equates to Next then we check to see whether we're on the last page (i.e. current page equals page count). If not, then the AbsolutePage property is set to the next page; otherwise we display an 'end' page:

if not isObject(Session("MyConnection")) then
  <deal with first execution of thispage – see code above>
Else
  set objConn = Session("MyConnection")
  set objRS = Session("MyResults")
  intCurrentPage = Session("Page")
  if Request("PrevOrNext") = "Next" then
    'If we are not at the end of the pages then
    'go to the next page and display
    if not (intCurrentPage = objRS.PageCount) then
      objRS.AbsolutePage = intCurrentPage + 1
      Display
    else
      ' Display that we are at the end of the records and set the
      ' page count up so that the Prev will calculate the last page
      Response.Write "<P>No More Records"
      Session("Page") = objRS.PageCount + 1
    end if
  else
    'Walking backwards through the pages works very much the same way:
    'If "Next" is not explicitly chosen, then assume "Prev"
    'If we are not on the first page then turn back the page and display
   
    if intCurrentPage <> 1 then
      objRS.AbsolutePage = intCurrentPage - 1
      Display
    else
      'Display that we are at the start of the pages. Set the page number
      'to zero so that the next will increment to one
      Response.Write "<P>At Start of Records"
      Session("Page") = 0
    end if
  end if
end if

As you can see in the screenshots, paging output is simple and does not overwhelm the user with too much information.