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!
|
Join this webcast, to learn how the Rational Process Library can help with compliance issues, drive process improvement, and assist in service-oriented architecture (SOA) or Agile development. We will take a peek into the Rational Process Library with content around software and systems engineering (including RUP), operations and systems management, program and portfolio management, and asset and SOA governance.
FREE! Go There Now!
| | | |
Hear how IBM Rational Project and Portfolio Management integrated solutions help teams put the right tools and processes in place to maximize the effectiveness and efficiency of project teams and ensure that the business vision is being executed correctly. Learn how to automate and integrate requirements prioritization, top-down project planning, communications and controls, and methodology deployment to keep your scope, costs, and schedules under control. Tackle with an end-to-end approach the management of scope and scope changes, usage of methodology to control and empower project teams, and optimization of resources to align activity costs with the overall project plan.
FREE! Go There Now!
| | | |
As businesses grow increasingly dependent upon Web applications to provide services to customers, employees and partners, these complex applications become more difficult to secure. Although traditional security solutions protect Internet infrastructure layers, they do not guard against HTTP and HTML attacks. Many organizations that conduct security testing still deploy applications that allow attackers to manipulate their logic and wreak havoc on their business. To mitigate this risk, development and delivery teams must address Web application security throughout the lifecycle, addressing the many layers detailed in this paper.
FREE! Go There Now!
| | | |
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!
| | | |
You probably have thousands of lines of COBOL code loaded with business intelligence and being used to run your business, along with an army of developers maintaining these applications. Learn how to prepare your applications and developers so you can keep that competitive edge and move to a service-oriented architecture with the IBM Rational Enterprise Modernization solutions. Replay is available for 9 months.
FREE! Go There Now!
| | | |
Download a free trial version of IBM DB2 9.5 for Linux, UNIX, and Windows. DB2 9 is the result of a five-year development project that transformed traditional (static) database technology into an interactive data server that merges the high performance and ease of use of DB2 with the self-describing benefits of XML.
FREE! Go There Now!
| | | |
Visit IBM developerWorks to download IBM DB2 Express-C 9.5, a no-charge version of DB2 Express 9 database server. DB2 Express-C offers the same core data server base features as other DB2 Express editions and provides a solid base to build and deploy applications developed using C/C++, Java, .NET, PHP, and other programming languages.
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!
| | | |
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!
| | | |
Explore how Rational and WebSphere software enable enterprise documentation in SOA environments. Specifically, a new integration between IBM WebSphere® Business Modeler and IBM Rational® Method Composer software can help technical writers more easily keep enterprise operations manuals in sync with changes that are made to business processes, resulting in more accurate and timely documentation that benefits the entire enterprise.
FREE! Go There Now!
| | | |
All FREE IBM® developerWorks Tools!
| |
