Database Code
  Home arrow Database Code arrow ADO Recordset Paging
ASP Free Forums 
.NET  
ASP  
ASP Code  
ASP.NET  
ASP.NET Code  
BrainDump  
C#  
Code Examples  
Database  
Database Code  
IIS  
Microsoft Access  
MS SQL Server  
Silverlight  
Visual Basic.NET  
Windows Scripting  
Windows Security  
XML  
Mobile Linux 
App Generation ROI 
IBM® developerWorks 
ASP Web Hosting  
ASP.NET Web Hosting 
Windows Web Hosting
 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us Get Paid 
Request Media Kit
Contact Us 
Site Map 
Privacy Policy 
Support 
 USERNAME
 
 PASSWORD
 
 
  >>> SIGN UP!  
  Lost Password? 
DATABASE CODE

ADO Recordset Paging
By: Bipin Joshi
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 5 stars5 stars5 stars5 stars5 stars / 34
    2003-01-01

    Table of Contents:

    Rate this Article: Poor Best 
      ADD THIS ARTICLE TO:
      Del.ici.ous Digg
      Blink Simpy
      Google Spurl
      Y! MyWeb Furl
    Email Me Similar Content When Posted
    Add Developer Shed Article Feed To Your Site
    Email Article To Friend
    Print Version Of Article
    PDF Version Of Article
     
     
    ADVERTISEMENT


    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.

    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.


    DISCLAIMER: The content provided in this article is not warranted or guaranteed by Developer Shed, Inc. The content provided is intended for entertainment and/or educational purposes in order to introduce to the reader key ideas, concepts, and/or product reviews. As such it is incumbent upon the reader to employ real-world tactics for security and implementation of best practices. We are not liable for any negative consequences that may result from implementing any information covered in our articles or tutorials. If this is a hardware review, it is not recommended to open and/or modify your hardware.

    More Database Code Articles
    More By Bipin Joshi

     

    IBM® developerWorks developerWorks - FREE Tools!


    NEW! Addressing software-as-a-service challenges using Tivoli security and WebSphere solutions

    Building a software-as-a-service solution requires addressing a few key technical challenges. In this webcast, we'll focus on the role of IBM Tivoli Directory Server and WebSphere Portlet Factory in creating a Software as a Service solution. We will demonstrate how to use Tivoli Directory Server to prevent the user population of one tenant from accessing the virtual portal and portlet components of another tenant. We will also use the dynamic profile capability of WebSphere Portlet Factory to create multiple highly customized applications from one code base.
    FREE! Go There Now!


    NEW! Develop Systems Software Assets with IBM Rational Asset Manager

    Join us for this on demand webcast to learn about developing complex systems more quickly and efficiently. We'll cover market drivers for developing, governing and reusing systems software assets and how you can develop system software assets with Rational Asset Manager.
    FREE! Go There Now!


    NEW! Discovering the value of WebSphere Process Server

    WebSphere Process Server delivers a unique integration framework that simplifies existing IT resources. Often, as IT assets grow to support business demand, so too does their complexity and manageability. In this webcast, we’ll discuss how WebSphere Process Server helps deliver an SOA infrastructure that provides a common model to orchestrate, mediate, connect, map, and execute the underlying IT functions. Discover how WebSphere Process Server simplifies integration of business processes by leveraging existing IT assets as reusable services without the complexities of traditional integration methodologies.
    FREE! Go There Now!


    NEW! IBM Enterprise Modernization Sandbox for System z: Architecture

    Analysts, architects, and developers who have existing COBOL or PL/I skills and want to extend those skills to deploy new workloads on the mainframe can use the IBM Enterprise Modernization Sandbox for System z to find hands-on walkthroughs of common real world scenarios. The scenarios provide examples of how to rapidly design, create, assemble, test, and deploy high-quality Web, Web services, portal, and SOA applications for IBM CICS, IBM IMS, and IBM WebSphere Application Server.
    FREE! Go There Now!


    NEW! Rational Talks to You: Grady Booch on Architecture

    Join this Rational Talks to You teleconference on November 29 at 1:00 pm ET to participate in an interactive discusssion with Grady Booch around architecture and reuse. Get your questions answered!
    FREE! Go There Now!


    NEW! The role of integrated requirements management in software delivery

    This paper is about the critical role that a discipline called integrated require­ments management can play in helping to ensure that your business goals and IT investments are continuously aligned—whether you are sourcing, integrat­ing, building or maintaining software. It also looks at ways that automated IBM Rational® products can work together to help you use requirements in the very best way.
    FREE! Go There Now!


    NEW! Trial download: IBM Rational Manual Tester V7.0.1

    Try the latest version of IBM Rational Manual Tester V7.0.1 by downloading a free trial from IBM developerWorks. This manual test authoring and execution tool promotes test step reuse to reduce the impact of software change on testers and business analysts and addresses the needs of teams performing at least a portion of their testing manually.
    FREE! Go There Now!


    NEW! Try the IBM SOA Sandbox for People

    Visit IBM developerWorks to try the IBM SOA Sandbox for people. The SOA Sandbox for people provides a trial environment with the necessary tooling and components required to enable consistent human and process interaction and collaboration, showing how you can improve user experience and business productivity.
    FREE! Go There Now!


    NEW! Understanding Web application security challenges

    As businesses grow increasingly dependent upon Web applications, these complex entities grow more difficult to secure. Most companies equip their Web sites with firewalls, Secure Sockets Layer (SSL), and network and host security, but the majority of attacks are on applications themselves – and these technologies cannot prevent them. This paper explains what you can do to help protect your organization, and it discusses an approach for improving your organization’s Web application security.
    FREE! Go There Now!


    NEW! Webcast: Introducing the new Information Server and Solutions community: LeverageInformation

    User communities play an important role in communication and collaboration around products, solutions and other areas of special interest to members. Successful communities are able to provide the right mix of content and services to deliver a value proposition that resonates with each audience. Join Tom Inman, VP of Marketing for Information and Platform Solutions as he introduces the new LeverageINFORMATION community. During this webcast, learn about the value provided by the community and how customers and partners derive value from the community in addressing their own technical and business challenges.
    FREE! Go There Now!



    All FREE IBM® developerWorks Tools!

    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...
    - Getting A List of Tables From SQL Server
    - SQL Server Database Creator - .NET Version
    - ADO Recordset Paging
    - Two combos, one textbox example
    - Discussion & Listserv Module by Mike Eck...





    © 2003-2009 by Developer Shed. All rights reserved. DS Cluster 2 Hosted by Hostway
    For more Enterprise Application Development news, visit eWeek