Paging through an ASP record

Paging through ASP Records
J.D Meier.
Directory

--------------------------------------------------------------------------------
1. Introduction
2, the problem
3. Solution
4. Sample Code
5, analysis
6. Conclusion

--------------------------------------------------------------------------------

Brief introduction

displaying large recordsets in an Active Server Pages (ASP) application is probably a familiar issue. This article delves into this problem and its solutions and sample code, and this sample code is simply modified to apply to your specific situation. The sample code is designed as a server-side solution, and it is not browser-independent. In addition, I will point out the issues you need to consider when designing your own solution.

Problem

Your query returned a large set of records. You need to provide an easy way to view these results, that is, only a subset of the results is displayed on each page. To perform this work effectively, you need to have an in-depth understanding of how ActiveX (R) Data Objects (ADO) and databases work together.

Solution

How do I divide your recordset into pages without large results? The so-called page is basically the number of records that you specify should be displayed together. For example, if you have 100 records in your Recordset, 10 records may appear on each page.

ADO provides two methods, PageSize and AbsolutePage. These methods enable you to specify the number of records to display per page, and to start the tour calibration at the beginning of a page.

After you open a recordset, the basic steps are:

Specifies PageSize for the recordset. That represents the number of records to display per page.
Specifies the absolutepage of the recordset. This moves the record pointer to the sequence of the page, at the beginning of the given page.
Displays the record page. To complete this step, you want to loop through the recordset with the set number of PageSize, or until the end of the file is reached.
Sample code

The following sample code illustrates the page creation process. With it, you can build your own solution prototype. In your own code, be sure to complete the following steps:

Add error handling.
Adds a limit to the number of records returned by a query.
Filter records with conditions. (for example, to create a WHERE clause).
Use a stored procedure or view.
Be sure to modify my sample code to point to your database by changing the connection string and the SQL statement. Because the code uses ADO constants, such as Aduserserver, be sure to reference the ADO typelibrary in your Global.asa file, or include Adovbs in the ASP page. INC files. Note that when you set the project reference to Microsoft ADO, Visual InterDev (R) automatically generates typelibrary references for you.

Note This example has two ways to provide a navigation bar:

Shownavbar. It provides a way for users to jump to a specified page with a record count (see Figure 1). To achieve this, it uses the RecordCount and PageCount properties.
Shownavbarfast. This method does not provide the ability to jump to a specified page, nor does it provide a record count, but the number of records retrieved can be controlled by the CacheSize property (see Figure 2).
Pagethroughrs.asp

<%@ Language=vbscript%>
<% Option Explicit%>
<script Language=vbscript runat=server>
' Make sure to refer to ADO Typelib or use Adovbs. Inc
Dim Ipagenum, Irowsperpage

Main
Sub Main ()
Dim rst
Dim sSQL, sConnString

If request.querystring ("ipagenum") = "" Then
Ipagenum = 1
Else
Ipagenum = Request.QueryString ("Ipagenum")
Ipagenum = CInt (ipagenum)
End If

Irowsperpage = 10

sconnstring = "Provider=sqloledb.1;password=xyz123;user id=webuser;" & _
"Initial catalog=northwind;data source=mysqlserver;" & _
"NETWORK=DBMSSOCN;"

' The following SQL retrieves all the columns from SQL view.
' To optimize performance:
'-Use stored procedures, views, or specify columns in SELECT
'-use restrictions on the criteria returned by the record (for example, WHERE clause)
sSQL = "Select CategoryName, ProductName, QuantityPerUnit,"
sSQL = sSQL & "UnitsInStock, discontinued"
sSQL = sSQL & "From [Products by Category]"

Set rst = Getrecords (sconnstring, sSQL)

Writetableheader rst
Writetablebody rst, Irowsperpage, Ipagenum
Shownavbar rst

' Showfastnavbar method does not use RecordCount
' or PageCount, so the number of records it retries is just equal to
' The number of CacheSize specified for the recordset.

' Showfastnavbar rst

CleanUp rst
End Sub

Function getrecords (sconnstring, sSQL)
Dim CNN
Dim rst

Set cnn = Server.CreateObject ("ADODB.") CONNECTION ")
Cnn. ConnectionString = sConnString
Nn. Open

Set rst = Server.CreateObject ("ADODB.") RECORDSET ")

Set rst. ActiveConnection = CNN

' When the recordset is open, the adUseClient cursorlocation
' Will retrieve all the records.
' adUseServer allowed to follow CacheSize
Rst. CursorLocation = adUseServer

' When using a server-side cursor, CacheSize
' Limits the number of rows retrieved. We will only crawl the displayed
' The number of records-irowsperpage
Rst. CacheSize = Irowsperpage

Rst. Open ssql,,adopenstatic, adLockReadOnly 牋?
Set getrecords = rst
End Function

Sub Writetableheader (RST)
Dim FLD

Response.Write "<table width=80% border=1>"
Response.Write "<TR>"

' Create a table column heading
For each fld in RST. Fields
Response.Write "<TD><B>" & fld. Name & "</B></TD>"
Next
Response.Write "</TR>"
End Sub

Sub writetablebody (RST, irowsperpage, Ipagenum)
Dim Iloop
Dim FLD

Iloop = 1

Rst. PageSize = Irowsperpage
Rst. AbsolutePage = Ipagenum

' Write down the current page of the record
Do while (not rst. EOF) and (Iloop <= irowsperpage)
Response.Write "<TR>"
For each fld in RST. Fields
Response.Write "<TD>" & Fld.value & "</TD>"
Next
Iloop = Iloop + 1
Rst. MoveNext
Response.Write "</TR>"
Loop
Response.Write "</TABLE>"
End Sub

Sub Shownavbar (RST)
Dim Ipagecount
Dim Iloop
Dim Sscriptname

' This release provides richer user navigation, but
' Dependent on RecordCount and PageCount,
' It offsets the server-side cursor
' Specify the benefits of CacheSize.

Response.Write "<BR><BR>"
Sscriptname = Request.ServerVariables ("Script_name")

If ipagenum > 1 Then
Response.Write "<a href=" & Sscriptname & "? ipagenum="
Response.Write (iPageNum-1) & "><< previous</a>"
End If

Ipagecount = rst. PageCount
Do Until iloop > Ipagecount
F iloop = Ipagenum Then
Response.Write "<B>" & CStr (Iloop) & "</B>"
Else
Response.Write "<a href=" & Sscriptname & "Ipagenum=" & _
Cstr (Iloop) & ">" & Iloop & "</a>"
End If
Iloop = Iloop + 1
Loop

If not rst. EOF Then
Response.Write "<a href=" & Sscriptname & "? ipagenum="
Response.Write (Ipagenum + 1) & "> Next >></a><BR>"
Else
Response.Write "<BR>"
End If

Response.Write "Page" & Ipagenum & "of" & Ipagecount & "<BR>"
Response.Write rst. RecordCount & "Records" 牋?
End Sub

Sub Showfastnavbar (RST)
Dim Ipagecount
Dim Iloop
Dim Sscriptname

' When specifying CacheSize and using server-side cursors,
' This method is particularly effective because it does not use RecordCount
' and PageCount. User experience is required.

Response.Write "<BR><BR>"
Sscriptname = Request.ServerVariables ("Script_name")

If ipagenum > 1 Then
Response.Write "<a href=" & Sscriptname & "? ipagenum="
Response.Write (iPageNum-1) & "><< previous</a>"
End If

If not rst. EOF Then
Response.Write "<a href=" & Sscriptname & "? ipagenum="
Response.Write (Ipagenum + 1) & "> Next >></a><BR>"
Else
Response.Write "<BR>"
End If

Response.Write "Page" & Ipagenum

End Sub

Sub CleanUp (RST)
If not rst are nothing Then
If rst.state = adStateOpen then Rst.close
Set rst = Nothing
End If
End Sub

</SCRIPT>
Analysis

Several issues to be aware of when designing a paging solution:

Cursor positioning problem. If you use a client cursor, all records are read every time the recordset is opened. Therefore, because all records are read, it will be quick to access the RecordCount or PageCount properties later. If you use a server-side cursor, only the required records will be retrieved. You can improve performance by specifying the number of records to read at a time through the CacheSize property. However, if you use server-side cursors, and RecordCount or PageCount properties, all records are read and performance is not improved. You must compromise between the user interface with more information and richer navigation, and the performance impact of retrieving all records.
When using a server-side cursor, the CursorType property must be adOpenStatic or adOpenKeyset to use paging.
Pagination is not always the best user page. It may only apply if the user is scanning results from a search engine or browsing the product catalog.
Try classifying records so that more relevant records appear on the previous pages (for example, using SQL's ORDER BY clause). There's so much that users can do.
Retrieve only the columns that need to be displayed (that is, avoid SELECT *).
Retrieves only the records that need to be displayed. Make sure to filter the condition (that is, use the WHERE clause).
Here are a few tips to keep in mind:

Encapsulate your logic in a method. The use of methods separates presentation logic from data access logic, simplifying the work of loading code into Windows script components, Visual Basic script Editing (VBScript) classes, or components. Changing functionality is easier and code maintenance is improved. Testing and debugging can also be improved by commenting and uncomment method calls.
With the inclusion of Adovbs. TypeLibrary, which refers to ADO, is a better solution than INC. This is because an ASP reads an entire file into memory when it processes the containing file, rather than reading only the parts it needs.
Conclusion

Paging is a common technology that many WEB applications use to provide a good way to browse a large number of records. When designing a paging solution, there are some questions to consider, such as how to retrieve records and what type of user navigation you need to provide. Although the best solution depends on your specific application, using the techniques in this article will help you make better design decisions