What is pagination display when ADO accesses databases? If you have used the electronic bulletin board program on many websites, you should know that in order to improve the reading speed of the page, the electronic bulletin board program generally does not list all posts on one page, but instead It is divided into multiple pages to display, and each page displays a certain number of posts, such as 20. Want to know how to implement pagination display? Please read this article!
The article "Eighteen Martial Arts of Dynamic Website Design--ASP" has spent most of half a year with friends since the first issue. I believe that through learning, practicing, learning, and practicing during this period, everyone will be able to learn and practice. I am already able to use ASP's built-in objects and ActiveX components to write some basic ASP applications. From the letters I have received from my friends, we can clearly feel that everyone’s ASP skills are constantly improving. Recently, many friends have written to me and hope that I can write some examples of ASP in real-life use. Therefore, starting from this issue, I decided to shift the positioning of "Eighteen Martial Arts of Dynamic Website Design-ASP Edition" from introducing and learning ASP basic knowledge to discussing and deepening the actual operation of ASP. At the request of my friends, in this issue, I will focus on how to display the ADO database paging when accessing it.
What is pagination display when ADO accesses databases? If you have used the electronic bulletin board program on many websites, you should know that in order to improve the reading speed of the page, the electronic bulletin board program generally does not list all posts on one page, but instead It is divided into multiple pages to display, and each page displays a certain number of posts, such as 20. This is the pagination display of database queries. If you don’t understand it yet, you will understand it by looking at the query results of search engines such as yahoo.
So 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 clearer.
Here, the author will take our common ASPBBS 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. Therefore, 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 that the indicator points to. 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 location is in a Recordset object
When the first line is recorded, the BOF attribute returns true, otherwise 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 Show post paging---
< %Sub ShowList() %>
< %
PgSz=20 'Set switch, specify the number of posts displayed on each page, default is 20 posts per page
Set Conn = Server.CreateObject(ADODB.Connection)
Set RS = Server.CreateObject(ADODB.RecordSet)
sql= SELECT* FROM message order by ID DESC
'Query all posts and arrange them in reverse order by post ID
Conn.Open bbs
RS.opensql,Conn,1,1
If RS.RecordCount=0 then
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)
if PageNo= Then
PageNo = 1
else
PageNo=PageNo+1
PageNo=PageNo-1
end if
ScrollAction = Request(ScrollAction)
if ScrollAction = Previous page Then
PageNo=PageNo-1
end if
if ScrollAction = Next page Then
PageNo=PageNo+1
end if
if PageNo < 1 Then
PageNo = 1
end if
n=1
RS.AbsolutePage = PageNo
Response.Write < CENTER>
position=RS.PageSize*PageNo
pagebegin=position-RS.PageSize+1
if position < RS.RecordCount then
pagend=position
else
pagend= RS.RecordCount
end if
Response.Write < P>< font color='Navy'>< B>Database query result:< /B>
Response.Write (There are a total of &RS.RecordCount & pieces of information that meet the conditions, display &pagebegin&-&pagend&)< /font>< /p>
Response.Write < TABLE WIDTH=600 BORDER=1 CELLPADDING=4 CELLSPACING=0 BGCOLOR=#FFFFFF>
Response.Write < TR BGCOLOR=#5FB5E2>< FONT SIZE=2>< TD>< B>Theme< /B>< /TD>< TD>< B>User< /B>< /TD>< TD>< B>Email< /B>< /TD>< TD>< B>Published Date< /B>< /TD>< /FONT>< TR BGCOLOR=#FFFFFF>
Do while not (RS is nothing)
RowCount = RS.PageSize
Do While Not RS.EOF and rowcount > 0
If n=1 then
Response.Write < TR BGCOLOR=#FFFFFF>
ELSE
Response.Write < TR BGCOLOR=#EEEEE>
End If
n=1-n %>
< TD>< span style=font-size:9pt>< A href='view.asp?key=< % =RS(ID)%>'>< % =RS(subject)%>< /A>< / span></td>
< TD>< span style=font-size:9pt>< % =RS(name)%>< /A>< /span>< /td>
< TD>< span style=font-size:9pt>< a href=mailto:< % =RS(email)%>>< % =RS(email)%>< /a>< /span> < /TD>
< TD>< span style=font-size:9pt>< % =RS(postdate)%>< /span> < /td>
< /TR>
< %
RowCount = RowCount - 1
RS.MoveNext
Loop
set RS = RS.NextRecordSet
Loop
Conn.Close
set rs = nothing
set Conn = nothing
%>
< /TABLE>
< FORM METHOD=GETACTION=list.asp>
< INPUT TYPE=HIDDEN NAME=pageno VALUE=< % =PageNo %>>
< %
if PageNo > 1 Then
response.write < INPUT TYPE=SUBMIT NAME='ScrollAction' VALUE=' Previous Page'>
end if
if RowCount = 0 and PageNo < >Total then
response.write < INPUT TYPE=SUBMIT NAME='ScrollAction' VALUE='Next Page'>
end if
response.write < /FORM>
End if
%>
< % End Sub %>
I believe everyone should be able to fully understand the above program, so the author will not explain it in detail here. It is worth noting that a small trick was used in this program < INPUT TYPE=HIDDEN NAME=pageno VALUE=< % =PageNo %>>, which is a secret passage used to pass data every time the ASP file is called, because We need to pass parameters representing the current page number every time we call the program, and you may think of using session, but in terms of saving system resources and universality, using such a hidden form to pass data will achieve better results.
Okay, it's time to say goodbye again. If you don't fully understand the programs listed in this article, you must take a look at the syntax of VbScript; if you still have some questions, you can I will try my best to answer; if you have any better suggestions, please send me a letter :)