Home>Network programming tutorial>ASP tutorial

How to display the ADO database paging

Author：Eve Cole Update Time：2025-03-20 12:32:02

This article mainly introduces how to display ADO database paging. Friends who need it can refer to it

How can we paginate the query results of the database? There are actually many ways, but there are two main ways:

1. Read all records in the database that meet the query conditions into the recordset at one time and store them in memory. Then, through the ADO Recordset object, several properties that specifically support paging processing are provided: PageSize (page size), PageCount( Page number) and AbsolutePage (absolute page) to manage paging processing.

2. According to the customer's instructions, the specified number of records shall be read out and displayed from the records that meet the query conditions each time.

The main difference between the two is that the former reads all records into memory at one time and then makes judgments and analysis in sequence according to the instructions to achieve the effect of pagination display, while the latter makes judgments based on the instructions and sets the specified number to meet the query conditions. The records are read into memory, thus directly achieving the function of pagination display.

We can clearly feel that when the number of records in the database reaches tens of thousands or more, the execution efficiency of the first method will be significantly lower than that of the second method, because when every customer querys the page, all the results must be transferred. Records that meet the criteria are stored in the server memory and are then processed in paging, etc. If more than 100 customers query online at the same time, the execution efficiency of the ASP application will be greatly affected. However, when the number of records in the database on the server and the number of people online at the same time are not large, the execution efficiency of the two is almost the same. At this time, the first method is generally used because the ASP program of the first method is relatively written The second method is much simpler and clear.

Here, the author will take our common ASP BBS program as an example to analyze how to implement the pagination display function in the BBS program. Since the number of database records and number of people accessing simultaneously in the BBS program we generally use will not be too many. , so the following program example is the first pagination display method introduced previously using.

The pagination display when ADO accesses the database is actually to operate the records of the Recordset. So we must first understand the properties and methods of the Reordset object:

BOF attribute: The current indicator refers to the first transaction to the RecordSet.

EOF Attribute: The current indicator refers to the last transaction to the RecordSet.

Move method: Move metrics to a record in the RecordSet.

AbsolutePage property: Sets the current location of the record on which page is located. AbsolutePosition property: The current location of the metric in the RecordSet.

PageCount property: Displays how many pages of data a Recordset object contains.

PageSize property: Displays the number of records displayed on each page of the Recordset object.

RecordCount property: Displays the total number of records of the Recordset object.

Let's take a closer look at these important attributes and methods

1. BOF and EOF attributes

Usually we write code in an ASP program to check the BOF and EOF attributes to know the location of the RecordSet pointed to by the current indicator. Using the BOF and EOF attributes, we can know whether a Recordset object contains records or whether the moving record line is The scope of the Recordset object has been exceeded.

For example: < %if not rs.eof then ... %>

< % if not (rs.bof and rs.eof) %>

If the current record position is before the first row of a Recordset object, the BOF property returns true, otherwise it returns false.

If the current record position is after the last row of a Recordset object, the EOF property returns true, otherwise it returns false.

Both BOF and EOF are False: means that the indicator is located in the RecordSet.

BOF is True: The current indicator refers to the first record to the RecordSet. EOF is True: The current indicator refers to the last record to the RecordSet.

Both BOF and EOF are True: There is no record in the RecordSet.

2. Move method

You can use the Move method to move metrics to a record in the RecordSet, with the syntax as follows:

rs.Move NumRecords, Start

Here rs is an object variable, indicating a Recordset object that wants to move when the current record position; NumRecords is a positive and negative number calculation formula that sets the number of movements of the current record position; start is an optional item to specify Record the starting tag.

All Recordset objects support the Move method. If the NumRecords parameter is greater than zero, the current record position moves to the end; if it is less than zero, the current record position moves to the beginning; if an empty Recordset object calls the Move method, the An error will be generated.

MoveFirst method: Move the current record position to the first record.

MoveLast method: Move the current record position to the last record.

MoveNext method: Move the current record position to the next record. MovePrevious method: Move the current record position to the previous record.

Move [n] Method: Move the index to the nth pen record, n starts from 0.

3. AbsolutePage attributes

The AbsolutePage property sets the page number of pages on which page is located; use the PageSize property to divide the Recordset object into logical pages, and the number of records for each page is PageSize (except for the last page, there may be less than PageSize records). It must be noted here that not all data providers support this property, so be careful when using it.

The same as the AbsolutePosition property, the AbsolutePage property starts with 1. If the current record is the first row of the Recordset, the AbsolutePage is 1. The AbsolutePage property can be set to move to the first row record position of a specified page.

4. AbsolutePosition property

If you need to determine the current location of the metric in the RecordSet, you can use the AbsolutePosition property.

The value of the AbsolutePosition property is the position of the current indicator relative to the first transaction, which starts from 1, that is, the AbsolutePosition of the first transaction is 1.

Note that when accessing RecordSets, there is no guarantee that RecordSets will appear in the same order every time.

To enable AbsolutePosition, you must first set it to use the user cursor (pointer), and the asp code is as follows:

rs2.CursorLocation = 3

5. PageCount property

Use the PageCount property to determine how many pages of data a Recordset object contains. The page here is a collection of data records, and its size is equal to the setting of the PageSize property. Even if the number of records on the last page is less than the value of PageSize, the last page is considered a page of PageCount. It must be noted that not all data providers support this property.

6. PageSize property

The PageSize property is the key to determining how ADO paginates when accessing databases. Using it, you can decide how many records form a logical page. Sets and creates a page size, allowing the AbsolutePage property to be moved to the first record of other logical pages. The PageSize property can be set at any time.

7. RecordCount property

This is also a very common and important property. We often use the RecordCount property to find out how many records a Recordset object contains. For example: < %totle=RS.RecordCount %>

After understanding the above properties and methods of Recordset objects, let’s consider how to use them to achieve the purpose of our pagination display. First, we can set a value for the PageSize property, thereby specifying the number of rows that constitute a page taken from the record group; then use the RecordCount property to determine the total number of records; then divide the total number of records by PageSize to obtain the displayed page Total number; finally access to the specified page can be completed through the AbsolutePage property. It seems very not complicated. Let’s take a look at how the program should be implemented?

We have built such a simple BBS application, which has the following five fields in its database: ID, automatic number of each post; subject, subject of each post; name, name of the user added; email, user's Email address; postdate, time of posting. The DSN of the database is bbs. We put all the steps in displaying post paging in a process called ShowList() for easy call. The procedure is as follows:

  1. '----BBS displays post paging---
  2. <%SubShowList()%>
  3. <%
  4. PgSz=20' setting switch, specify the number of posts displayed on each page, default is 20 posts per page
  5. SetConn=Server.CreateObject(ADODB.Connection)
  6. SetRS=Server.CreateObject(ADODB.RecordSet)
  7. sql=SELECT*FROMmessageorderbyIDDESC
  8. 'Query all posts and arrange them in reverse order by post ID
  9. Conn.Openbbs
  10. RS.opensql,Conn,1,1
  11. IfRS.RecordCount=0then
  12. response.write<P><center>Sorry, there is no relevant information in the database! </center></P>
  13. else
  14. RS.PageSize=Cint(PgSz)'Set the value of the PageSize property
  15. Total=INT(RS.recordcount/PgSz*-1)*-1'Calculate the total number of pages that can be displayed
  16. PageNo=Request(pageno)
  17. ifPageNo=Then
  18. PageNo=1
  19. else
  20. PageNo=PageNo+1
  21. PageNo=PageNo-1
  22. endif
  23. ScrollAction=Request(ScrollAction)
  24. ifScrollAction=Previous page Then
  25. PageNo=PageNo-1
  26. endif
  27. ifScrollAction=Next Page Then
  28. PageNo=PageNo+1
  29. endif
  30. ifPageNo<1Then
  31. PageNo=1
  32. endif
  33. n=1
  34. RS.AbsolutePage=PageNo
  35. Response.Write<CENTER>
  36. position=RS.PageSize*PageNo
  37. pagebegin=position-RS.PageSize+1
  38. ifposition<RS.RecordCountthen
  39. pagend=position
  40. else
  41. pagend=RS.RecordCout
  42. endif
  43. Response.Write<P><fontcolor='Navy'><B>Database query results:</B>
  44. Response.Write (there are a total of &RS.RecordCount& pieces of information that meet the conditions, display &pagebegin&-&pagend&)</font></p>
  45. Response.Write<TABLEWIDTH=600BORDER=1CELLPADDING=4CELLSPACING=0BGCOLOR=#FFFFFF>
  46. Response.Write<TRBGCOLOR=#5FB5E2><FONTSIZE=2><TD><B>Theme</B></TD><TD><B>Users</B></TD><TD><B> Email</B></TD><TD><B>Published Date</B></TD></FONT><TRBGCOLOR=#FFFFFF>
  47. Dowhilenot(RSisnothing)
  48. RowCount=RS.PageSize
  49. DoWhileNotRS.EOFandrowcount>0
  50. Ifn=1then
  51. Response.Write<TRBGCOLOR=#FFFFFF>
  52. ELSE
  53. Response.Write<TRBGCOLOR=#EEEEE>
  54. EndIf
  55. n=1-n%>
  56. <TD><spanstyle=font-size:9pt><Ahref='view.asp?key=<%=RS(ID)%>'><%=RS(subject)%></A></span> </td>
  57. <TD><spanstyle=font-size:9pt><%=RS(name)%></A></span></td>
  58. <TD><spanstyle=font-size:9pt><ahref=mailto:<%=RS(email)%>><%=RS(email)%></a></span></TD>
  59. <TD><spanstyle=font-size:9pt><%=RS(postdate)%></span></td>
  60. </TR>
  61. <%
  62. RowCount=RowCount-1
  63. RS.MoveNext
  64. Loop
  65. setRS=RS.NextRecordSet
  66. Loop
  67. Conn.Close
  68. setrs=nothing
  69. setConn=nothing
  70. %>
  71. </TABLE>
  72. <FORMMETHOD=GETACTION=list.asp>
  73. <INPUTTYPE=HIDDENNAME=pagenoVALUE=<%=PageNo%>>
  74. <%
  75. ifPageNo>1Then
  76. response.write<INPUTTYPE=SUBMITNAME='ScrollAction'VALUE='Previous Page'>
  77. endif
  78. ifRowCount=0andPageNo<>Totalthen
  79. response.write<INPUTTYPE=SUBMITNAME='ScrollAction'VALUE='Next Page'>
  80. endif
  81. response.write</FORM>
  82. Endif
  83. %>
  84. <%EndSub%>
?
Related Articles