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:
?
- '----BBS displays post paging---
- <%SubShowList()%>
- <%
- PgSz=20' setting switch, specify the number of posts displayed on each page, default is 20 posts per page
- SetConn=Server.CreateObject(ADODB.Connection)
- SetRS=Server.CreateObject(ADODB.RecordSet)
- sql=SELECT*FROMmessageorderbyIDDESC
- 'Query all posts and arrange them in reverse order by post ID
- Conn.Openbbs
- RS.opensql,Conn,1,1
- IfRS.RecordCount=0then
- response.write<P><center>Sorry, there is no relevant information in the database! </center></P>
- else
- RS.PageSize=Cint(PgSz)'Set the value of the PageSize property
- Total=INT(RS.recordcount/PgSz*-1)*-1'Calculate the total number of pages that can be displayed
- PageNo=Request(pageno)
- ifPageNo=Then
- PageNo=1
- else
- PageNo=PageNo+1
- PageNo=PageNo-1
- endif
- ScrollAction=Request(ScrollAction)
- ifScrollAction=Previous page Then
- PageNo=PageNo-1
- endif
- ifScrollAction=Next Page Then
- PageNo=PageNo+1
- endif
- ifPageNo<1Then
- PageNo=1
- endif
- n=1
- RS.AbsolutePage=PageNo
- Response.Write<CENTER>
- position=RS.PageSize*PageNo
- pagebegin=position-RS.PageSize+1
- ifposition<RS.RecordCountthen
- pagend=position
- else
- pagend=RS.RecordCout
- endif
- Response.Write<P><fontcolor='Navy'><B>Database query results:</B>
- Response.Write (there are a total of &RS.RecordCount& pieces of information that meet the conditions, display &pagebegin&-&pagend&)</font></p>
- Response.Write<TABLEWIDTH=600BORDER=1CELLPADDING=4CELLSPACING=0BGCOLOR=#FFFFFF>
- 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>
- Dowhilenot(RSisnothing)
- RowCount=RS.PageSize
- DoWhileNotRS.EOFandrowcount>0
- Ifn=1then
- Response.Write<TRBGCOLOR=#FFFFFF>
- ELSE
- Response.Write<TRBGCOLOR=#EEEEE>
- EndIf
- n=1-n%>
- <TD><spanstyle=font-size:9pt><Ahref='view.asp?key=<%=RS(ID)%>'><%=RS(subject)%></A></span> </td>
- <TD><spanstyle=font-size:9pt><%=RS(name)%></A></span></td>
- <TD><spanstyle=font-size:9pt><ahref=mailto:<%=RS(email)%>><%=RS(email)%></a></span></TD>
- <TD><spanstyle=font-size:9pt><%=RS(postdate)%></span></td>
- </TR>
- <%
- RowCount=RowCount-1
- RS.MoveNext
- Loop
- setRS=RS.NextRecordSet
- Loop
- Conn.Close
- setrs=nothing
- setConn=nothing
- %>
- </TABLE>
- <FORMMETHOD=GETACTION=list.asp>
- <INPUTTYPE=HIDDENNAME=pagenoVALUE=<%=PageNo%>>
- <%
- ifPageNo>1Then
- response.write<INPUTTYPE=SUBMITNAME='ScrollAction'VALUE='Previous Page'>
- endif
- ifRowCount=0andPageNo<>Totalthen
- response.write<INPUTTYPE=SUBMITNAME='ScrollAction'VALUE='Next Page'>
- endif
- response.write</FORM>
- Endif
- %>
- <%EndSub%>