Paging through ASP records

Paging through ASP records

J. D. Meier

Directory

1. Introduction 2. Problems 3. Solutions

4. Sample Code 5. Analysis 6. Conclusion

Introduction

Displaying a large record set in an Active Server Pages (ASP) application may be a problem you are familiar. This article discusses the problem, its solution, and sample code in depth. This sample code can be applied to your specific situation after a simple modification. The sample code is designed as a server-side solution, which has nothing to do with the browser. In addition, I will point out the issues you need to consider when designing your own solutions.

Problem

Your query returns a large record set. You need to provide a simple way to view these results, that is, only a subset of the results is displayed on each page. To effectively complete this task, you need to have a deep understanding of how ActiveX (r) Data Objects (ADO) work together with databases.

Solution

How do I divide your record set into "pages" without large results? The so-called page is basically a number of records you specify that should be displayed together. For example, if your record set contains 100 records, 10 records may be displayed on each page.

ADO provides two methods,PagesizeAndAbsolutepage. These methods enable you to specify the number of records to be displayed on each page and locate the cursor at the beginning of a page.

After opening the record set, the basic steps are as follows:

1. SpecifyPagesize. It indicates the number of records to be displayed on each page.
2. SpecifyAbsolutepage. This moves the record pointer to the sequence of pages, starting from the given page.
3. The record page is displayed. To complete this step, you must setPagesizeCycles the entire record set, 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 a prototype of your own solution. In your own code, make sure you complete the following steps:

• Add error handling.
• Add a limit on the number of records returned by the query.
• Filter records using conditions. (For example, create a where clause ).
• Use stored procedures or views.

You must modify the sample code by changing the connection string and SQL statement to point to your database. Because the Code uses the ADO constant, suchAduserserverMake sure to reference ADO typelibrary in your global. Asa file, or include the adovbs. inc file on the ASP page. Note that when you set the project reference to Microsoft ado, Visual InterDev (r) automatically generates typelibrary reference for you.

Note that there are two methods for this example to provide the navigation bar:

• Shownavbar.It allows you to jump to the specified page with the record count (see figure 1 ). To achieve this step, it usesRecordcountAndPagecountAttribute.
• Shownavbarfast.This method does not provide the ability to jump to the specified page, nor does it provide the record count, but can passCachesizeProperty controls the number of retrieved records (see figure 2 ).

Pagethroughrs. asp

<% @ Language = VBScript %> <% option explicit %> <script language = VBScript runat = Server> 'Make sure to reference 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; US Er id = webuser; "& _" Initial catalog = northwind; Data Source = mysqlserver; "& _" Network = dbmssocn; "'The following SQL statements retrieve all columns from the SQL view. 'The performance to be optimized: '-use stored procedures, views, or specify columns in the select'-use conditions that limit returned records (for example, where clauses) ssql = "select categoryname, productname, response, "ssql = ssql &" unitsinstock, discontinued "ssql = ssql &" from [products by category] "set rst = getrecords (sconnstring, ssql) writetableheader rst writetablebody rst, irowsperpage, the ipagenum shownavbar rst 'showfastnavbar method does not use recordcount' or pagecount. Therefore, the number of retries of the ipagenum method is equal to the cac of the 'record set The number specified by hesize. '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 'the cursorlocation' of aduseclient will retrieve all records when the record set is opened. 'Aduseserver allows cachesize RST. cursorlocation = aduseserver' cachesize' to limit the number of retrieved rows when using a server-side cursor. We will only capture the number of records being displayed-irowsperpage RST. cachesize = irowsperpage RST. Open ssql, adopenstatic, adlockreadonly records? Set getrecords = rst end Function Sub writetableheader (RST) dim implements response. write "<Table width = 80% border = 1>" response. write "<tr>" 'create the table's column title for each created in rst. fields response. write "<TD> <B>" & gt. name & "</B> </TD>" next response. write "</tr>" End sub writetablebody (RST, irowsperpage, ipagenum) dim iloop dim incluiloop = 1 rst. pagesize = irowsperpage rst. absolutepage = ipagenum 'write the current page of the record do While (not rst. EOF) and (iloop <= irowsperpage) response. write "<tr>" for each failed in rst. fields response. write "<TD>" & Other. value & "</TD>" next iloop = iloop + 1 rst. movenext response. write "</tr>" Loop Response. write "</table>" End sub shownavbar (RST) dim ipagecount dim iloop dim sscriptname 'provides richer user navigation, but' depends on recordcount and pagecount, 'It offsets the benefit of specifying the cachesize for the server-side cursor. Response. Write "<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" success? End sub showfastnavbar (RST) dim ipagecount dim iloop dim sscriptname 'is particularly effective when cachesize is specified and server-side cursor is used, because it does not use recordcount and pagecount. User experience is required. Response. Write "<br>" sscriptname = request. servervariables ("script_name") if ipagenum> 1 then response. Write "<a href =" & sscriptname &"? Ipagenum = "response. write (ipagenum-1) & "> <previous </a>" end 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 cleanup (RST) if not RST is nothing then if rst. state = adstateopen then rst. close set rst = nothing end if end sub </SCRIPT>

Analysis

Note the following when designing a paging solution:

• Cursor locating. If a client cursor is used, all records are read each time the record set is opened. Therefore, since all records are readRecordcountOrPagecountAttribute will soon be used. If you use a server-side cursor, only the required records are retrieved. You can useCachesizeAttribute specifies the number of records to be read at a time to improve performance. However, if you use a server-side cursorRecordcountOrPagecountAll records will be read, and the performance will not be improved. The user interface with more information and richer navigation must compromise the performance impact of retrieving all records.
• When using a server-side cursor,CursortypeAttribute must beAdopenstaticOrAdopenkeysetTo use paging.
• Paging is not always the best user page. It may only apply when a user is scanning results from a search engine or browsing a product directory.
• Try to classify the records so that more relevant records appear on the previous pages (for example, using the SQL order by clause ). Users can do so much.
• Retrieve only the columns to be displayed (that is, avoid select *).
• Only the records to be displayed are retrieved. Make sure that the filter conditions (that is, use the WHERE clause) are met ).

The following are some tips to keep in mind:

• Encapsulate your logic in methods. You can use this method to separate the representation logic from the data access logic, which simplifies code loading into Windows Script components, Visual BASIC script editing (VBScript) classes, or components. It is easier to change functions and code maintenance can also be improved. Testing and debugging can also be improved by commenting on and canceling annotation method calls.
• Compared with adovbs. Inc, referencing typelibrary of ADO is a better solution. This is because ASP reads the entire file into the memory when processing the file that contains it, rather than reading the content it requires.

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, you need to consider some issues, such as how to retrieve records and what type of user navigation should be provided. Although the best solution depends on your specific application, using the technology in this article will help you make better design decisions.

 

J. D. MeierBorn and grown up on the east coast of the United States. Since following hsf-e Greeley's advice, he has become a development support engineer specializing in server-side components and Windows DNA applications related to MTS and ASP technologies.

Archived servin' it up Column

		2000
April 24		Debug ASP applications)
March 28		Improve the Performance of ASP applications)
February 28		ASP is used in IIS 5.0)
January 24		ASP Component Guide (English)
		1999
December 27		ASP Guide (English)
November 22		Server reliability in process isolation)
October 25		Troubleshooting of components using ASP Technology)
September 27		Stress again-load and test your ASP Application)
July 26		Servin' it up generous prizes (English)
June 28		Upload File: Posting Acceptor, part 2)
May 24		Upload File: Posting Acceptor, part 1)
April 26		Blur the line between the client and the server with remote scripts)
March 30		Use ASP and wsh to print data on the Intranet)
February 22		Use ADSI, ASP, and some tricks to automatically create virtual directories. Part 2)
January 25		Use ADSI, ASP, and some tricks to automatically create virtual directories. Part 1)
		1998
December 28		Face ASP)
November 23		Tom's 10 major reasons for upgrading to Microsoft SQL Server 7.0)
October 28		My favorite object, Part 3: record count and error check)
October 1		My favorite object. Part 2: add data access using WFC/ADO)
September 1		My favorite objects: Create and debug Java Server objects)
July 27		Use firewall for fire prevention)
June 29		There is no doubt that ASP has no problem (English)
June 1		Smart investment on your web site)
April 27		Know your limits: Unknown capacity planning tools)
March 31		Security Basics)