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!
|
The IBM DB2 Deep Compression ROI tool is designed for DBA’s and IT management personnel to perform a clinical analysis of the cost savings gained from the Storage Optimization feature of DB2 9 for Linux, UNIX and Windows. The feature, also known as Deep Compression, compresses data that lies within a database by up to 80% at times.
FREE! Go There Now!
| | | |
You'll get answers to many questions and more from David Barnes, Lead Evangelist for IBM Emerging Internet Technologies. David will discuss aspects of Web 2.0 that bring value to corporations, academia, and government. He'll also discuss IBM's vision around Web 2.0, including the importance of remixability and consumability. The discussion will culminate with examples of various IBM Software Group solutions you can use to get ahead of the Web 2.0 adoption curve.
FREE! Go There Now!
| | | |
Visit IBM developerWorks to download a free trial of the latest release of IBM Lotus Sametime Standard V8.0. Lotus Sametime Standard V8.0 is a platform for unified communications and collaboration that combines security features with an extensible, open solution including integrated Voice over IP, geographic location awareness, mobile clients, and a robust Business Partner community offering telephony and video integration.
FREE! Go There Now!
| | | |
Listen to this webcast to get an overview of Info 2.0 and a technical demo of how to quickly build an enterprise mashup. IBM's Info 2.0 technology leverages emerging Web 2.0 technologies such as mashups, feeds, AJAX, and JSON in order to simplify assembly of information using feeds and services. Come learn about the technical elements of Info 2.0 including the Feed Generation framework, Mashup Engine, and mashup assembly components. Learn how to pull information from databases, departmental information, and the Web to create mashups critical to your company’s success. We will also discuss best practices to help you get started.
FREE! Go There Now!
| | | |
This webcast outlines the best practices that must be instituted to gain the maximum benefit from SOA while maintaining high quality of service. Whether you are deploying new applications or managing and monitoring your existing infrastructure, learn how you can ensure high quality of services with SOA based solutions from IBM. All registrants who attend this live Web Seminar will receive complimentary access to a white paper titled “Maintaining QoS in an SOA Environment”.
FREE! Go There Now!
| | | |
This whitepaper provides areas to consider when evaluating any software configuration management solution. It addresses how the IBM solutions (Rational ClearCase and Rational ClearQuest) meet the needs and requirements of both project leaders and developers to provide successful Software Change and Configuration Management.
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!
| | | |
In this webcast, IBM Rational will discuss the importance of Web application security and will share techniques and best practices to introduce application security testing into current QA processes including: understanding common security vulnerabilities and techniques to integrate security testing with defect tracking and remediation systems in an effort to safeguard sensitive online information.
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!
| | | |
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!
| |
