Paging certain # of records using
Stored Procedure, Command Object and MS-SQL Server
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={SQL Server};Description=sqldemo;SERVER=127.0.0.1;UID=sa;PWD=;DATABASE=pubs"
' 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
'This is the Stored proc syntax
'CREATE PROCEDURE spAuthors AS
'select * from authors
'Return
'Go
strSql = "spAuthors"
'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 = adCmdStoredProc
.CommandText = "spAuthors"
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=""./paging3.asp?page=1"
Response.Write """>First Page</A>"
Response.Write " "
If strPageCurrent <> 1 Then
Response.Write "<A HREF=""./paging3.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 " " & vbCrLf
End If
If strPageCurrent < strPageCount Then
Response.Write "<A HREF=""./paging3.asp?page="
Response.Write strPageCurrent + 1
Response.Write """>Next Page</A>" & vbCrLf
End If
Response.Write " "
Response.Write "<A HREF=""./paging3.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  developerWorks - FREE Tools!
|
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!
| | | |
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!
| | | |
Download a free trial version of IBM Rational Developer for System z, software that can help you deliver core development capabilities; the power of Java Platform, Enterprise Edition (Java EE); and rapid application development support to diverse enterprise application development teams. With comprehensive development tools to help create, deploy and maintain traditional enterprise and composite applications, Rational Developer for System z enables developers with different technical backgrounds to easily participate in important technology projects.
FREE! Go There Now!
| | | |
XML has become a common way of storing business data as flat files and many data server vendors including IBM have provided ways to store this data within relational database systems. Increasingly collections of XML files are accessed like databases using an xQuery and other XML standard mechanisms. Businesses find the need to combine the traditional tabular structured data with XML formatted data. In this webcast, you’ll learn about IBM’s WebSphere Federation Server technology, which provides users with the ability to integrate these two data formats.
FREE! Go There Now!
| | | |
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!
| | | |
Rational Build Forge Express Edition is an automation framework that packages the latest enterprise-grade technologies into a reliable, flexible and robust configuration designed and priced specifically for small to midsize businesses. The new Rational Build Forge Express eKit provides you with valuable resources – including a case study, podcast, demo, and articles – to help you increase staff productivity, compress development cycles and deliver better software, fast.
FREE! Go There Now!
| | | |
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!
| | | |
Get a free trial download of the latest version of IBM Rational Functional Tester V7.0.1. Rational Functional Tester is an automated functional and regression testing solution for QA teams concerned with the quality of their Java, Microsoft Visual Studio .NET, and Web-based applications.
FREE! Go There Now!
| | | |
IBM Lotus Notes 8 provides a wide range of developers the ability to provide customized, integrated user interfaces via composite applications and via custom sidebar and toolbar plug-ins. This webcast provides you with tips and techniques to use with out-of-the-box capabilities of Lotus Notes 8, and survey how you can share useful components within your own company and within a larger community.
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!
| | | |
All FREE IBM® developerWorks Tools!
| |
| | | | | | | |  | | |
|
AT&T devCentral & BlackBerry(r) Webcast Series: BlackBerry and GPS -Build Location Awareness into your BlackBerry Applications, July 10th -1:00PM EST. 
