HOWTO: Page Through a Recordset from ASP

ID: Q202125

The information in this article applies to:

ActiveX Data Objects (ADO), versions 2.0, 2.1 SP2
Active Server Pages
Microsoft Internet Information Server versions 4.0, 5.0

SUMMARY

This article describes how to use the PageSize, PageCount, and AbsolutePage properties of an ADO 2 recordset and what cursor types must be used to get Recordset Paging to work.

MORE INFORMATION

This sample code demonstrates using Recordset Paging against the Adventure Works Access database.

The code Assumes the system DSN named AdvWorks is pointing to the Adventure Works Access 97 database.


<%@ EnableSessionState=False Language=VBScript %>
<%
set conn = Server.CreateObject("ADODB.Connection")
conn.Open "DSN=AdvWorks"

set rs = Server.CreateObject("ADODB.Recordset")
rs.CursorLocation = 3  ' adUseClient
rs.Open "Select * from Employees", conn
rs.PageSize = 2
intPageCount = rs.PageCount

Select Case Request("Action")
	case "<<"
		intpage = 1
	case "<"
		intpage = Request("intpage")-1
		if intpage < 1 then intpage = 1
	case ">"
		intpage = Request("intpage")+1
		if intpage > intPageCount then intpage = IntPageCount
	Case ">>"
		intpage = intPageCount
	case else
		intpage = 1
end select
%>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<TITLE>ASP & ADO Paging</TITLE>
</HEAD>
<BODY bgColor=White text=Black>

<%
rs.AbsolutePage = intPage 
For intRecord = 1 To rs.PageSize 
	Response.Write "Record	number: " & intRecord & " " 
	Response.Write rs.Fields("FirstName") & " " 
	Response.Write rs.Fields("LastName") & "<br>" 
	rs.MoveNext
If rs.EOF Then Exit For 

Next

rs.Close
set rs = Nothing
conn.Close
set conn = nothing
%>
<form name="MovePage" action="default.asp" method="post">
<input type="hidden" name="intpage" value="<%=intpage%>">
<input type="submit" name="action" value="&lt;&lt;">
<input type="submit" name="action" value="&lt;">
<input type="submit" name="action" value="&gt;">
<input type="submit" name="action" value="&gt;&gt;">
Page: <%=Intpage & " of " & intpagecount%>
</form>
</BODY>
</HTML>

When using Recordset Paging against SQL Server 6.5, you can use a server side cursor. However, you must use either a Static or a Keyset cursor type. Otherwise, no data will be returned. If you use a client side cursor, all cursor types will work, but only a static cursor is returned.

When using Recordset Paging against Microsoft Access 97, you must use a client side cursor. Any cursor type will work, but again only a static cursor is returned. A server side cursor will not return any data.

When using Recordset Paging against Oracle 7.3.3, 7.3.4, and 8.0.3, you can use a server side cursor, but if you do, you must use a Static or Keyset cursor type. Otherwise, no data will be returned. If you use a client side cursor, all cursor types will work, but only a keyset cursor is returned.

Additional query words:

Keywords : kbADO kbASP kbDatabase kbGrpASP kbGrpMDAC kbDSupport kbRDS210SP2 kbCodeSnippet kbiis400 kbiis500
Version : WINDOWS:2.0,2.1 SP2; winnt:
Platform : WINDOWS winnt
Issue type : kbhowto