Database Code
  Home arrow Database Code arrow Paging certain # of records using Access, ...
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  
Visual Basic.NET  
Windows Scripting  
Windows Security  
XML  
ASP Web Hosting  
ASP.NET Web Hosting 
Dedicated Servers 
Actuate Whitepapers 
Moblin 
Windows Web Hosting
 
IBM® developerWorks 
Sun Developer Network 
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

Paging certain # of records using Access, Command Object
By: aspfree
  • Search For More Articles!
  • Disclaimer
  • Author Terms
  • Rating: 5 stars5 stars5 stars5 stars5 stars / 5
    2001-08-04

    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

    Ajax Application Generator Generate database and reporting .NET Web apps in minutes. Quickly create visually stunning, feature-rich apps that are easy to customize and ready to deploy. Download Now!

    Paging certain # of records using
    Access Database, Command Object

    I recently was putting together some web-based screens and I wanted an easy way to display a certain number of records on each page.  I wanted it to be flexible enough I could change it on-the-fly.  Well after some hunting through the recordset object there is this nice method called paging.  I started with the maxrecords method, tried a few other things and then BOOM, I found the paging method. 

    Its real easy, in this demo I let the user display different number of records depending on their mood.  If you don't do anything with this the default is 10 records.    There are 2 pages, one has the dropdown box to select the # of records, the 2nd one displays the information.  Below is the code, hope it helps. This example shows using the Command Object, the main reason is the dis-connected recordset model of using Database resources most efficently.

     

    Page 1-The page with the dropdown box.

    <html><head>
    <title>paging</title></head>
    <body>
    <form method="post" action="paging.asp" name="form1">
    <select name="d1">
    <option value="10">10</option>
    <option value="7">7</option>
    <option value="5">5</option>
    </select>
    <input type="submit" value="submit" name="b1">
    </form>
    </body>

    Page 2-The page that shows the records

    <%@ Language="VBScript"%>
    <!-- #INCLUDE FILE="./adovbs.Inc" -->
    <%
    ' I'm using a DSN-less connection.

    ConnString = "DRIVER=Microsoft Access Driver (*.mdb);DBQ=" & Server.MapPath("paging.mdb")

    ' Declare variables
    Dim iPageSize 'How big our pages are
    Dim strPageCount 'The number of pages we get back
    Dim strPageCurrent 'The page we want to show
    Dim strSql 'SQL select to limit fields
    Dim conn 'The connection object
    Dim rs 'The recordset object
    Dim x 'Standard looping var

    If request("d1") <> "" Then Session("d1") = request("d1")

    'Get parameters from the dropdown box on the previous page.
    ' You could easily just use the default of 10
    iPageSize = Session("d1")
    If Request("page") = "" Then
        strPageCurrent = 1
    Else
        strPageCurrent = CInt(Request("page"))
    End If

    'Open the connection string
    Set conn = Server.CreateObject("ADODB.Connection")
    conn.Open connstring

    set cmd = server.CreateObject("adodb.command")

    set cmd.ActiveConnection = conn
    'set sql statement to a local variable
    strSql = "SELECT * FROM table1"

    'adCmdUnspecified -1 Does not specify the command type argument.
    'adCmdText 1 Evaluates CommandText as a textual definition of a command or stored procedure call.
    'adCmdTable 2 Evaluates CommandText as a table name whose columns are all returned by an internally generated SQL query.
    'adCmdStoredProc 4 Evaluates CommandText as a stored procedure name.
    'adCmdUnknown 8 Default. Indicates that the type of command in the CommandText property is not known.
    'adCmdFile 256 Evaluates CommandText as the file name of a persistently stored Recordset. Used with Recordset.Open or Requery only.
    'adCmdTableDirect 512 Evaluates CommandText as a table name whose columns are all returned. Used with Recordset.Open or Requery only. To use the Seek method, the Recordset must be opened with adCmdTableDirect.
    'This value cannot be combined with the ExecuteOptionEnum value adAsyncExecute.

    With cmd
        .CommandType = adCmdText
        .CommandText = strSQL
    End With

    Set rs = Server.CreateObject("ADODB.Recordset")

    ' Set cursor location and pagesize
    rs.CursorLocation = adUseClient
    rs.PageSize = iPageSize

    ' Open Recordset object and
    rs.Open cmd

    'Disconnect from database
    cmd.ActiveConnection = nothing

    ' Get the count of the pages using the given page size
    strPageCount = rs.PageCount

    ' If the request page falls outside the range,
    ' give them the closest match (1 or max)
    If 1 > strPageCurrent Then strPageCurrent = 1
    If strPageCurrent > strPageCount Then strPageCurrent = strPageCount

    ' Move to the selected page
    rs.AbsolutePage = strPageCurrent

    ' Start output with a page x of n line
    Response.Write "<FONT SIZE=""+1"">Page <B>"
    Response.Write strPageCurrent
    Response.Write "</B> of <B>"
    Response.Write strPageCount
    Response.Write "</B></FONT><BR><BR>" & vbCrLf

    ' Continue with a title row in our table
    Response.Write "<TABLE BORDER=""1"">" & vbCrLf

    ' Show field names
    Response.Write vbTab & "<TR>" & vbCrLf
    For x = 1 To rs.Fields.Count
        Response.Write vbTab & vbTab & "<TD><B>"
        Response.Write rs.Fields(x - 1).Name
        Response.Write "<B></TD>" & vbCrLf
    Next
        Response.Write vbTab & "</TR>" & vbCrLf

    ' Loop through our records
    Do While rs.AbsolutePage = strPageCurrent And Not rs.EOF
        Response.Write vbTab & "<TR>" & vbCrLf
        For y = 1 To rs.Fields.Count
            Response.Write vbTab & vbTab & "<TD>"
            Response.Write rs.Fields(y - 1)
            Response.Write "</TD>" & vbCrLf
        Next
            Response.Write vbTab & "</TR>" & vbCrLf

    'Move to the next record!
    rs.MoveNext
    Loop

    'Closing html table tag
    Response.Write "</TABLE>" & vbCrLf

    ' Close all objects and clear from Memory
    rs.Close
    Set rs = Nothing

    'Show "previous" and "next" links which navigate between pages
       
        Response.Write "<A HREF=""./paging5.asp?page=1"
        Response.Write """>First Page</A>"
       
        Response.Write "&nbsp;&nbsp;&nbsp;"
    If strPageCurrent <> 1 Then
        Response.Write "<A HREF=""./paging5.asp?page="
        Response.Write strPageCurrent - 1
        Response.Write """>Previous Page</A>" & vbCrLf
    'Spacer - inside the if so we don't get it unless needed
        Response.Write "&nbsp;&nbsp;" & vbCrLf
    End If
    If strPageCurrent < strPageCount Then
        Response.Write "<A HREF=""./paging5.asp?page="
        Response.Write strPageCurrent + 1
        Response.Write """>Next Page</A>" & vbCrLf
    End If
        Response.Write "&nbsp;&nbsp;&nbsp;"
        Response.Write "<A HREF=""./paging5.asp?page="
        Response.Write strPageCount
        Response.Write """>Last Page</A>" & vbCrLf


    %>
    <html>

    <head>
    <title>Paging Results page</title>
    </head>

    <body>
    </body>
    </html>


    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 aspfree

     

    IBM® developerWorks developerWorks - FREE Tools!


    NEW! Accelerating Software Innovation on i on Power Systems

    Attend this launch webcast with Scott Hebner, Vice President of IBM Rational Marketing and Strategy, for an overview of Rational’s new software offerings and resources to help modernize and accelerate software innovation on i on Power Systems – while ensuring past application investments are protected and continue to grow. Learn how these solutions are helping customers extend their core i5/OS solutions toward modern architectures such as SOA and web technologies to deliver business improvements that stand the test of time.
    FREE! Go There Now!


    NEW! Best Practices in Integrated Requirements Management

    Poor Requirements Management capabilities in an Enterprise have been linked to excessive project failures, escalating IT costs, and failure to deliver competitive advantage into the marketplace. Join Brianna M Smith from IBM Rational and learn about how successful organizations align IT and Business stakeholders through collaborative processes and tools for effective requirements management, and how an integrated approach across the IT lifecycle can provide unparalleled visibility and traceability to ensure that project teams are delivering on the business vision by "doing the right things" and "doing things right."
    FREE! Go There Now!


    NEW! Download a free trial of Lotus Quickr 8.0

    Visit IBM developerWorks to download a free trial version of Lotus Quickr 8.0, which enables collaboration by transforming the way everyday business content such as documents, rich media, photos, and video can be shared. Lotus Quickr makes it faster and easier to share content of all types (not just documents) within virtual teams. It is designed to make it easier to collaborate across organizational boundaries, while continuing to work within the context of familiar desktop applications.
    FREE! Go There Now!


    NEW! Harnessing the power of SQL and Java for high performance data access

    Join this webcast to see how IBM Data Studio Developer and pureQuery can take the pain out of Java data access. uApplications developed using both Java and SQL have become a common requirement. Database connectivity using Java Database Connectivity (JDBC) to create an application is a multi-step tedious process, and tooling that covers both SQL and Java has been unavailable, until now. IBM Data Studio introduces the pureQuery platform: a high-performance, Java data access platform focused on simplifying the tasks of developing, managing, and optimizing database applications and services.
    FREE! Go There Now!


    NEW! Hello World: Monitor a simple business process using WebSphere Business Monitor V6.0.2

    This tutorial shows new users of IBM WebSphere Business Monitor Version 6.0.2 how to perform the "Hello World" equivalent for monitoring business process applications. It is intended to help you get familiar with the capabilities of the product.
    FREE! Go There Now!


    NEW! Project and Portfolio Management Executive Resource Kit

    Portfolio Management is about effectively managing portfolio value by aligning portfolio investments with business goals. This complimentary e-kit provides a collection of materials that can help you understand how IBM Rational enables and automates best practices for improved governance and clear visibility into portfolio and project performance across the entire IT project lifecycle.
    FREE! Go There Now!


    NEW! Rational Modeling Extension for Microsoft.Net

    Rational Modeling Extension for Microsoft .NET enhances usability for code generation supporting a more intelligent refactoring. The latest enhancements enable organizations with Java and .NET systems and software development maintain architectural integrity across heterogeneous platforms.
    FREE! Go There Now!


    NEW! Rational Talks to You:Per Kroll on Rational Method Composer Plug-in customization

    Join this Rational Talks to You teleconference on December 11 at 1:00 pm ET to get tips on building your own plugins with Rational Method Composer. Get your questions answered!
    FREE! Go There Now!


    NEW! Section 508 of the U.S. Rehabilitation Act: Web accessibility compliance

    Because access to government information continues to be an area of concern for many U.S. citizens with disabilities, the U.S. government enacted Section 508 of the Rehabilitation Act in 2001 to ensure that government agencies create accessible Web content, enabling all citizens to access the information they need. A fully accessible Web site makes Web content accessible to all individuals, including those with disabilities, who may be accessing Web content via a variety of user agents. Common user agents include standard Web browsers, text-only browsers, assistive devices and mobile devices such as cell phones or personal digital assistants (PDAs).
    FREE! Go There Now!


    NEW! Test terminal-based applications with Rational Functional Tester

    Regression testing -- in which code is thoroughly tested to ensure that changes have not produced unexpected results -- is an important part of any development process. But many testing environments neglect the terminal-based applications that still form the backbone of many industries. In this tutorial, you'll learn how the Rational Functional Tester Extension for Terminal-Based Applications works with other Rational Functional Tester to help test terminal-based applications quickly and easily.
    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-2008 by Developer Shed. All rights reserved. DS Cluster 2 hosted by Hostway