ASP 101 - Active Server Pages 101 - Web06
The Place ASP Developers Go!

Please visit our partners


Windows Technology Windows Technology
15 Seconds
4GuysFromRolla.com
ASP 101
ASP Wire
VB Forums
VB Wire
WinDrivers.com
internet.commerce internet.commerce
Partners & Affiliates














ASP 101 is an
internet.com site
ASP 101 is an internet.com site
IT
Developer
Internet News
Small Business
Personal Technology

Search internet.com
Advertise
Corporate Info
Newsletters
Tech Jobs
E-mail Offers

ASP 101 News Flash ASP 101 News Flash



 Top ASP 101 Stories Top ASP 101 Stories
Migrating to ASP.NET
Getting Scripts to Run on a Schedule
The Top 10 ASP Links @ Microsoft.com

QUICK TIP:
Make Your Scripts Filename Independent
Show All Tips >>
ASP 101 RSS Feed ASP 101 Updates


Recordset Paging with ADO 2.0

Recordset Paging with ADO 2.0

by Michael Qualls

Recordset paging is the process of breaking up a recordset into multiple "pages" of information for display. You've probably noticed that most well designed sites implement this feature, allowing you to navigate through a recordset to view a certain number of records at a time. Breaking the recordset data into pages allows for easy browsing by the user of the application and it also adds a professional touch. When I first started encountering this feature on the web a few years back, I immediately began working on how to implement it for myself. The leisure of being able to casually browse and navigate through the results of a query was wonderfull and a heck of a lot better than having a few hundred records dumped on my browser all at once.

ADO 2.0 makes this a very easy feature to implement.

Recordset Properties
There are five different recordset properties that are used to implement recordset paging:
  • CursorLocation - In order to use recordset paging, you will need to set this value to "adUseClient".
  • PageSize - Sets the number of records that the recordset will display on each page. A neat use of this is to allow the client to configure this setting so that they can tailor the recordset to their tastes.
  • PageCount - After setting the recordset's PageSize, you can read this property to check how many pages are in the recordset.
  • AbsolutePage - This property tells you what page you are on. You can set this property on the fly to jump to a different page in the recordset.
  • AbsolutePosition - This property will tell you what record you are on. You can read this property, or write to it to jump to a specific record.

Recordset Paging in Action

The most common use of recordset paging is to break up the results of a query submitted to a search engine. I have put together a simple example using the biblio Access database that accompanies Visual Studio 6.0. The sample presents the user with a form that allows a user to submit a year in order to return a listing of the titles of books published during that year.

This example requires that you have at least the following items installed on your computer:

  • Personal Web Server
  • Front Page Server Extensions
  • ADO 2.0+ object Library
  • biblio.mdb - One of the sample Access databases that comes with Visual Studio 6.0.

First we will build the search page. The search page is a simple HTML page that contains a single text box in which you can enter a year and a submit button to post the form's input to the ASP page that actually performs the search and returns the result.

Book Search Page Code Listing
	<BODY>
	<!-- Create an HTML form that posts its results to bk_searchresult.asp  -->
	<form name=frmYear id=frmYear action=bk_searchresult.asp method=post>
	<h1>Book Search</h1>
	<h4>Enter a year to return a listing of books published for that year.</h4>
	<p><b>Year: </b><input type=text size=20 id=txtYear name=txtYear></p>
	<input type=submit name=btnSubmit id=btnSubmit>
	</form>
	</BODY>
	

The next step is to build the ASP page that will perform the search based on the value posted from the search page and return the results to the browser.

Book Search Results Page Code Listing
<%
	' Declare all of the variables that will be used in the page.
	Dim objConn			' ADO Connection Object
	Dim objRst			' ADO Recordset Object
	Dim strYear			' The year that we are searching for.
	Dim Sql				' Our SQL statement
	Dim intPageCount		' The number of pages in the recordset.
	Dim intRecordCount		' The number of records in the recordset.
	Dim intPage			' The current page that we are on.
	Dim intRecord			' Counter used to iterate through the recordset.
	Dim intStart			' The record that we are starting on.
	Dim intFinish			' The record that we are finishing on.
	' Check to see if there is value in the NAV querystring.  If there
	' is, we know that the client is using the Next and/or Prev hyperlinks
	' to navigate the recordset.
	If Request.QueryString("NAV") = "" Then
		intPage = 1	
	Else
		intPage = Request.QueryString("NAV")
	End If
	' Create the Connection Object
	Set objConn = Server.CreateObject("ADODB.Connection")
	' Set the connection string.  I am connecting to the biblio database
	' using the OLEDB Provider for version 3.51 of the Jet Engine.  The
	' Data Source is set to path of the database on my computer.  You will,
	' of course, set this to the appropiate path for your computer.
	objConn.ConnectionString = "Provider=Microsoft.jet.OLEDB.3.51;" & _
	"Data Source = E:\Program Files\Microsoft Visual Studio\VB98\biblio.mdb"
	objConn.Open
	' Check to see if this page is loading as the result of a new search parameter 
	' being submitted.  If so, set the strYear varable equal to the form submission.
	' If not, set it equal to the value of the YEAR querystring.
	If Request.Form("txtYear") <> "" Then
		strYear = Request.Form("txtYear")
	Else
		strYear = Request.QueryString("YEAR")
	End If
	' Query the book titles from the Titles table where they match 
	' the strYear variable.
	Sql = "Select Title From Titles Where Titles.[Year Published] = " & strYear 
	' Create you Recordset Object
	Set objRst = Server.CreateObject("ADODB.Recordset")
	' The CursorLocation and the CursorType must be set as they are here
	' in order for Recordset Paging to work properly.
	objRst.CursorLocation = 3	'adUseClient
	objRst.CursorType = 3		'adOpenStatic
	objRst.ActiveConnection = objConn
	' Open the recordset.
	objRst.Open Sql
	' Set the PageSize, CacheSize, and populate the intPageCount and 
	' intRecordCount variables.
	objRst.PageSize = 10		
	' The cachesize property sets the number of records that will be cached 
	' locally in memory.
	objRst.CacheSize = objRst.PageSize
	intPageCount = objRst.PageCount 
	intRecordCount = objRst.RecordCount 
	' Now you must double check to make sure that you are not before the start
	' or beyond end of the recordset.  If you are beyond the end, set 
	' the current page equal to the last page of the recordset.  If you are
	' before the start, set the current page equal to the start of the recordset.
	If CInt(intPage) > CInt(intPageCount) Then intPage = intPageCount
	If CInt(intPage) <= 0 Then intPage = 1
	' Make sure that the recordset is not empty.  If it is not, then set the 
	' AbsolutePage property and populate the intStart and the intFinish variables.
	If intRecordCount > 0 Then
		objRst.AbsolutePage = intPage
		intStart = objRst.AbsolutePosition
		If CInt(intPage) = CInt(intPageCount) Then
			intFinish = intRecordCount
		Else
			intFinish = intStart + (objRst.PageSize - 1)
		End if
	End If
%>
<BODY>
<h1>Book Search Results</h1>
<h4>Your search for books published in <%=strYear%> 
returned <%=intRecordCount%> records.</h4>
<%If intRecordCount > 0 Then
	' Display the record that you are starting on and the record
	' that you are finishing on for this page by writing out the
	' values in the intStart and the intFinish variables.
%>
	<h4>You are now viewing records 
	<%=intStart%> through <%=intFinish%>.</h4>
	<table border=1>
	<tr><td><font size=+1><b>Book Title</b></font></td></tr>
	<%
		' Iterate through the recordset until we reach the end of the page
		' or the last record in the recordset.
		For intRecord = 1 to objRst.PageSize
			Response.Write "<tr><td>" & objRst("Title") & "</td></tr>"
			objRst.MoveNext
			If objRst.EOF Then Exit for
		Next
	%>
	<tr><td align=right>
	<%
		' Check to see if the current page is greater than the first page
		' in the recordset.  If it is, then add a "Previous" link.
		If cInt(intPage) > 1 Then
	%>
	   <a href="bk_searchresult.asp?NAV=<%=intPage - 1%>&YEAR;=<%=strYear%>"><< Prev</a>
	<%End IF%>
	<%
		' Check to see if the current page is less than the last page
		' in the recordset.  If it is, then add a "Next" link.
		If cInt(intPage) < cInt(intPageCount) Then
	%>
	   <a href="bk_searchresult.asp?NAV=<%=intPage + 1%>&YEAR;=<%=strYear%>">Next >></a>
	<%End If%>	
	</td></tr>
	</table>
<%End If%>
<a href="bk_search.htm">NEW SEARCH</a>

Summary

There are many more things that you could do to expand upon this simple example. You could allow your users to set the number of records that would be returned with each page by allowing them to pass a value to the PageSize property. In order to exand your navigation features, you could offer links that load the first page or last page of the recordset. You could even allow the users the ability to jump to specific pages. Once you have a good understanding of the basics, the sky is the limit for what you can do with recordset paging.

Support Materials

A copy of this article and the scripts described within is available for download here. You might also find our Database Paging sample helpful.


Home |  News |  Samples |  Articles |  Lessons |  Resources |  Forum |  Links |  Search |  Feedback

Internet.com
The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers