When needing to display a large number of records a useful technique is to use recordset paging to present the data as a number of pages showing X number of records per page. Although most sites that do this allow you to select the record count per page before you submit the search I often miss the ability to dynamically adjust the record count and simultaneously jump to a certain page. Okay, I lie, I missed it once while using Dejanews. But the minor inconvenience coupled with the general fustration that comes with trying to find anything on Deja these days pushed me into coding a solution, so here goes...
I'll go through the code a section at a time, firstly demonstrating the basic paging technique. If you're only after the basics you can stop at that point and your code will work fine, however if you choose to read on I'll show you how we can use a little Javascript to provide some rarely seen flexibility.
Basic recordset paging
<% @ LANGUAGE="VBScript" %>
<%
' Good Things To Do ™ Option Explicit Response.Buffer = True
' Constants we'll use for opening the recordset. Const adOpenForwardOnly = 0 Const adLockReadOnly = 1 Const adUseClient = 3 Const adCmdText = 1
' connection object, recordset, connection string, SQL string. I'll leave you to guess which... Dim conn, rs, connString, qString
' Open the database connection connString = "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("demo.mdb") Set conn = Server.CreateObject("ADODB.Connection") conn.Open connString
%>
<HTML> <HEAD> <TITLE> Database paging example </TITLE> </HEAD> <BODY>
The next bit of code allows us to pass page and record count requests via form elements or as part of the URL (eg paging.asp?page=5). We'll be using the URL method for our page links and later, a form for submitting the desired record count per page.
<% ' Variables for the page number we want to show, and the total number of pages Dim mypage, numpages ' Variables for the total number of records, and the number of records we should show on one page Dim numrecs, pagesize
' Get the page number we need to display from Form or QueryString mypage = CInt( Request("page") ) ' If we aren't given one display page 1 If mypage=0 Then mypage=1
' Get the requested number of records per page pagesize = CInt( Request("recs") ) ' Change 10 to whatever you want the default display to be If pagesize = 0 Then pagesize = 10
Next, the few lines of code it takes to make paging work. Setting CacheSize is optional, though the default value is 1 line so it's good to change it.
' The code we need to set up a recordset object for paging Set rs = Server.CreateObject("ADODB.RecordSet") rs.PageSize = pagesize rs.CacheSize = pagesize rs.CursorLocation = adUseClient
We're opening the recordset as read only, pass through once for efficiency. The adCmdText constant gives another slight performace boost by saving the system having to figure out which type of command we're using.
' Open the recordset qString = "SELECT * FROM Table1" rs.Open qString, conn, adOpenForwardOnly, adLockReadOnly, adCmdText
The recordset automatically processes the records into pages for us, all we have to do is tell it which page we want (using rs.AbsolutePage)
' Get the number of pages and records numpages = rs.PageCount numrecs = rs.RecordCount
' Just in case we have a bad request If mypage > numpages Then mypage = numpages If mypage < 1 Then mypage = 1
' This line sets the current page rs.AbsolutePage = mypage
Now we output a few variables and begin writing our records.
' Output the total number of records in the recordset Response.Write("<P> <B>" & numrecs & " records found.</B>" ) ' Write the current page number and number of pages(eg "Page 4 of 7") Response.Write("<BR> Page " & mypage & " of " & numpages & "</P>")
' Center align the display of records and links Response.Write("<CENTER>")
' Loop through the records for this page writing each on a new line Dim i For i=1 To pagesize ' Make sure we don't go past the last record If NOT rs.EOF Then ' Write a couple of fields from each record separated with a | Response.Write(rs("Name") & " | " & rs("Data") & " <BR>") ' Point to the next record rs.MoveNext End If Next
Response.Write("<P>")
And now a little menu with links to the other pages (eg 1-10 | 11-20 | 21-30 etc). Each link will refer back to our file, passing values for "page" and "recs" to be picked up by the code we wrote earlier. You could easily modify this section to produce Back and Next links if desired.
' Loop through the number of pages writing a link for each Dim x, lb, ub For x=1 To numpages ' lb is the lowest record number on that page, ub is the highest lb = (x-1) * pagesize + 1 ub = x * pagesize ' Makes sure the final link doesn't extend past to the # of records If ub > numrecs Then ub = numrecs ' Don't display the current page as a hyperlink If x <> mypage Then ' Example output: <A HREF=paging.asp?page=4;recs=25>75-100</A> Response.Write("<A HREF=paging.asp?page=" & x & "&recs=" & pagesize & ">" & lb & "-" & ub & "</A>") Else ' The active page Response.Write(lb & "-" & ub) End If ' Writes | as a separator if we're not at the last link If x <> numpages Then Response.Write(" | ") Next
Response.Write("</CENTER>")
' Clean-up rs.Close conn.Close Set rs = Nothing Set conn = Nothing
%>
At this point you can finish the code with </BODY> </HTML> and you'll have a working paging system. However if you carry on I'll demonstrate how we can improve the functionality.
Selecting the record count per page
We can do this easily with a simple form:
<FORM NAME=myform METHOD=post ACTION=paging.asp>
<!-- A drop down box for selecting the number of records to display --> Show <SELECT NAME=recs> <OPTION VALUE=5> 5 <!-- Change values to suit. --> <OPTION VALUE=10> 10 <!-- You can add/remove options --> <OPTION VALUE=25> 25 <!-- if required, just make sure --> <OPTION VALUE=50> 50 <!-- each has a numeric VALUE --> <OPTION VALUE=100> 100 <!-- parameter. --> </SELECT> records per page
<BR>
<INPUT TYPE=submit VALUE="GO!">
</FORM>
When the form is submitted it passes the VALUE parameter of the option selected to our paging file (paging.asp), keyed to the name "recs". We already have the code in place to get the value:
pagesize = CInt(Request("recs"))
It's for this reason we're using Request() rather than Request.QueryString(), which wouldn't be able to find values coming from the form.
Selecting the page number
As above we'll be using another <SELECT> element to allow the user to change the page being displayed. The complication here is that the number of pages available needs to change on the fly as the user selects various record per page values. For this we can use a little Javascript to dynamically calculate and fill the second drop down box based on the value in the first. Again, you can alternatively finish off here by ending the file with </BODY> </HTML> and skipping the rest of the code.
To start with we'll need to add a new select box to our form.
<!-- A dropdown box for selecting the page to display --> Display page <SELECT NAME=page> <OPTION> <OPTION> <OPTION> <OPTION> <OPTION> <OPTION> <OPTION> <OPTION> </SELECT>
Why the empty <option> tags? Well we need those to get around a bug in Netscape Navigator (a bug in Navigator? Neverrrrr). The number of tags you put in here determines the number of options displayed when you click on the box, even after we later add/remove/replace the contents. Take that as a general hint to use more than one...
Next, the script:
<SCRIPT LANGUAGE="JavaScript">
// The total number of records in the recordset NUM_RECORDS = <%=numrecs%>; // Note how we generate the value
// A function that populates the 'page' box based on the 'recs' box function updateBoxes(theFormObj) { // Get the records per page selected from the dropdown box var selectedRecs = theFormObj.recs.options[theFormObj.recs.selectedIndex].value; // Calculate the number of pages available for the value selected var numpages = Math.ceil(NUM_RECORDS / selectedRecs);
// Wipe the current contents of the page box var numOptions = theFormObj.page.length; for(var i=0 ; i<numOptions ; i++) { theFormObj.page.options[0] = null; }
// Fill the box with successive page numbers for(var j=0 ; j<numpages ; j++) { theFormObj.page.options[j] = new Option(j+1,j+1); } // Select the first option in the box theFormObj.page.selectedIndex = 0; }
</SCRIPT>
Now we must go back and modify the first select element so that it calls the function every time the user selects a new option:
<SELECT NAME=recs OnChange="updateBoxes(myform)">
Finally, we call the function manually to fill the page box the first time the page is loaded.
<SCRIPT> updateBoxes(document.myform); </SCRIPT>
</BODY>
</HTML>
That's it! All the code for the file is listed above, but you can get the cleaner (ie without my waffle) version here. Feel free to use it as you wish; it's a pretty simple matter to incorporate the javascript into existing pages, you just need to change a few variables/element names and you're all set.
Database Code 
ADO Recordset Paging
