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 " "
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 " " & 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 " "
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  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!
| | | |
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!
| | | |
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!
| | | |
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!
| | | |
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!
| | | |
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 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 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!
| | | |
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!
| | | |
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!
| |
| | | | | | | |  | | |
|
