Recommended: Use ASP to call views and stored procedures in the database 1. Preface ASP (Active Server Pages) is a server-side scripting environment, which is supported by Microsoft's IIS3.0 or above. It can be used to create dynamic web pages or to generate powerful web applications. ASP pages include HTML tags, text and
What is pagination display when ADO accesses databases? If you have used the electronic bulletin board program on many websites at present, 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 in one page, but divides them into multiple pages to display a certain number of posts per page, such as 20 posts. 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 manage paging processing through several attributes provided by the ADO Recordset object that specifically supports paging processing: PageSize (page size), PageCount (page number) and AbsolutePage (absolute pages).
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 reads the specified number of records that meet the query conditions into memory, thereby 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 each customer querys the page, all the records that meet the criteria must be stored in the server memory, and then paging and other processing is carried out. 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 on the server and the number of people online at the same time are not very large, the execution efficiency of the two is almost the same. At this time, the first method is generally adopted, because the ASP program writing of the first method is much simpler and clearer than the second method.
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 the number of people accessing simultaneously in the BBS program we generally use are not too many, so the following program examples are the first pagination display method introduced previously.
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 has exceeded the scope of the Recordset object.
like:
< % 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 the label for the start of the record.
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, 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 the current record 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 that may have 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 attribute
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 the Recordset object, 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 total number of pages displayed; finally, accessing the specified pages 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, the automatic number of each post; subject, the subject of each post; name, the name of the user who added the post; email, the user's email address; postdate, the time of adding the post. 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:
| The following is the quoted content: /'----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.open sql,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 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 < TABLE WIDTH=600 BORDER=1 CELLPADDING=4 CELLSPACING=0 BGCOLOR=#FFFFFF> Response.Write < TR BGCOLOR=#5FB5E2>< FONT SIZE=2>< TD>< B>Topic< /B>< /TD>< TD>< B>User< /B>< /TD>< TD>< B>Email< /B>< /TD>< TD>< B>Published Date< /B>< /TD>< /FONT>< TR BGCOLOR=#FFFFF> 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=GET ACTION=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. Since we need to pass parameters representing the current page number every time the program is called, 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 add some fuel to the grammar of VbScript; if you have some questions, I can do my best to answer them; if you have any better suggestions, please send me a letter.
Share: Generate static web pages by replacing tags Everyone knows that HTML static web pages are more likely to be indexed by search engines. Dynamically generating HTML web pages can also increase the number of web pages on the website, and search engines may also include more. What is the point of improving the quality of the web pages? I think everyone knows it too. for