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 : - Declare ADO recordset variable
- Set cursor type, location and pagesize properties
- Open recordset
- 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
- Set current page using AbsolutePage property
- 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 - 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
- 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.
- rs.Open "select * from authors",strConn
The recordset is then opened.
- 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
- 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  developerWorks - FREE Tools!
|
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!
| | | |
Download the Rational Application Developer (RAD) v7.5 open beta code and start developing applications for the JEE5 standard which features EJB3.0, JPA, JSF 1.2, JSP 2.1 and Servlet 2.5 standards. When you use this beta you will see how you can increase developer productivity for already existing applications with improved support for refactoring, as well as adding new features to existing applications. In addition, the beta provides tooling for JD Edwards, Oracle, SAP, Siebel and PeopleSoft to improve the developer productivity with these enterprise systems.
FREE! Go There Now!
| | | |
Discover how IBM Rational AppScan Standard Edition can help you detext vulnerabilities in your web applications in the Web Application Security eKit. IBM Rational AppScan is a leading suite of automated web application security solutions that scan and test for common Web application vulnerabilities. The new Web Application Security eKit provides you with valuable resources, including white papers, demos, and additional information on the benefits of testing your Web applications.
FREE! Go There Now!
| | | |
Download a free trial version of IBM Rational Software Analyzer Developer Edition V7.0 to identify bug defects earlier in the software development cycle. Rational Software Analyzer is an extensible software development solution that reduces the expense of bug-fixes by enabling static analysis code reviews and bug identification very early in the development cycle.
FREE! Go There Now!
| | | |
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!
| | | |
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!
| | | |
Discover how Rational tools and best practices for testing can make your job easier. The new Rational Testing eKits provide you with valuable resources – including demos, webcasts, tutorials, and articles – that help you address your specific testing needs across the software lifecycle. Five new eKits are available covering the topics of Requirements and Test Management, Functional Testing, Performance Testing, Code Quality and Embedded Systems, and SOA and Web Services Testing.
FREE! Go There Now!
| | | |
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!
| | | |
Get a free trial download of the latest version of IBM Rational Tester for SOA Quality V7.0.1, a functional and regression testing tool that enables the creation, comprehension, modification and execution of testing GUI-less Web services.
FREE! Go There Now!
| | | |
Viper 2 brings a great value to developer communities including SQL, XML, PHP, Ruby, .NET and Java. You probably already know that DB2 Express-C is free for developers to develop, deploy and distribute. Viper 2 provides a variety of means that help move your application from the development stage to deployment more rapidly. This webcast shows how to best utilize the latest tools available for developing DB2 applications.
FREE! Go There Now!
| | | |
All FREE IBM® developerWorks Tools!
| |
