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!
| | | |
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!
| | | |
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!
| | | |
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!
| | | |
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!
| | | |
This paper is about the critical role that a discipline called integrated requirements management can play in helping to ensure that your business goals and IT investments are continuously aligned—whether you are sourcing, integrating, 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!
| | | |
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!
| | | |
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!
| | | |
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!
| | | |
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!
| |
