ADO Recordset Paging

Many times after executing a query we get large number of records back and need to display certain number of records per page. Typically this occurs while providing search or find functionality.

Contributed by
Rating: 4 stars4 stars4 stars4 stars4 stars / 37
January 01, 2003
Rate this Article:
MEH MEH++


SEARCH ASP FREE
TOOLS YOU CAN USE

advertisement

Above task can be accomplished by using ADO recordset's paging properties. Following properties are of our interest :

  • PageSize which sets the number of records per ADO recordset page
  • PageCount which gives total number of pages
  • AbsolutePage which gets/sets the current page

Now,we will develop a sample script to demonstrate the usage. For the purpose of
this example you should have biblio.mdb that comes with VB. For the sake of
simplicity error handling and fine tuning is omitted.

Following steps are involved in the process :

  1. Declare ADO recordset variable
  2. Set cursor type, location and pagesize properties
  3. Open recordset
  4. Display a list of page nummbers as hyperlinks so that when user clicks any link he will be presented with the records from that page
  5. Set current page using AbsolutePage property
  6. Display record contents from current page


Example Code :

<%@ Language=VBScript %>
<% Response.Buffer=true %>

<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
</HEAD>
<BODY>

<%
dim rs,i,strConn,strTemp
set rs=server.CreateObject("adodb.recordset")

rs.CursorLocation=3 'clientside
rs.CursorType=3 'staticrecordset
rs.PageSize=100

strConn="Provider=Microsoft.Jet.OLEDB.3.51;Persist Security Info=False;Data Source=D:\Microsoft Visual Studio\VB98\Biblio.mdb"

rs.Open "select * from authors",strConn

Response.Write "<table><tr>"
for i=1 to rs.PageCount
Response.Write "<td><ahref=paging.asp?pg=" & i & ">" & i & "</a></td>"
next
Response.Write "</tr></table><br>"

if Request.QueryString("pg")="" then
rs.AbsolutePage=1
For i=1 to 100
Response.Write rs.Fields("author") & "<br>"
rs.MoveNext
Next
else
rs.AbsolutePage=cint(Request.QueryString("pg"))
For i=1 to 100
Response.Write rs.Fields("author") & "<br>"
rs.MoveNext
Next
end if

%>

</BODY>
</HTML>

Let's examine the code

  1. dim rs,i,strConn,strTemp
    set rs=server.CreateObject("adodb.recordset")

    First we declared variables for recordset,connection string and counter
    Next we reated recordset object using CreateObject method
  2. rs.CursorLocation=3
    rs.CursorType=3
    rs.PageSize=100

    The cursorlocation is set to client-side and cursor type is set to static cursor.
    Then we set the PageSize property to 100 which sets records per page to 100.
  3. rs.Open "select * from authors",strConn
    The recordset is then opened.
  4. Response.Write "<table><tr>"
    Response.Write rs.PageCount
    for i=1 to rs.PageCount
    Response.Write "<td><ahref=paging.asp?pg=" & i & ">" & i & "</a></td>"
    next
    Response.Write "</tr></table>"

    This code simply puts the links for available pages in a table. The total pages can be found out by PageCount property
  5. if Request.QueryString("pg")="" then
        rs.AbsolutePage=1
        For i=1 to 100
            Response.Write rs.Fields("author") & "<br>"
            rs.MoveNext
        Next
    else
        rs.AbsolutePage=cint(Request.QueryString("pg"))
        For i=1 to 100
            Response.Write rs.Fields("author") & "<br>"
            rs.MoveNext
        Next
    end if

    This code checks for page number selected by the user.First time when we visit
    that page it will be blank and hence we set the AbsolutePage property to 1. If
    user selects any page that page number will be assigned to AbsolutePage property.
    This causes the record pointer to move to the start of the page. We then display
    author names by scrolling through the recordset.

In live usage, code for checking ADO errors and checking recordset EOF can be added easily.

blog comments powered by Disqus
DATABASE CODE ARTICLES

- Deployment of the MobiLink Synchronization M...
- MobiLink Synchronization Wizard in SQL Anywh...
- Finding Matching Records in Data Access Pages
- Using the AccessDataSource Control in VS 2005
- A Closer Look at ADO.NET: The Command Object
- A Closer Look at ADO.NET: The Connection Obj...
- Using ADO to Communicate with the Database, ...
- Code Snippets: Counting Records
- Constraints In Microsoft SQL Server 2000
- Multilingual entries into a DB and to be dis...
- Two combos, one textbox example
- ADO Recordset Paging
- SQL Server Database Creator - .NET Version
- Getting A List of Tables From SQL Server
- Discussion & Listserv Module by Mike Eck...

ASP Web Hosting ASP.Net Web Hosting Windows Web Hosting
 
 
 

ASP Free Forums 
 RSS  Tutorials RSS
 RSS  Forums RSS
 RSS  All Feeds
Site Map 
Request Media Kit
Write For Us Get Paid 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Privacy Policy 
Support 


© 2003-2012 by Developer Shed. All rights reserved. DS Cluster 8 - Follow our Sitemap
Most Popular Topics
All ASP.Net Tutorials