How ADO can be paged when accessing a database

ado| Pagination | data | database | display

What is the paging display when ADO accesses a database? If you've ever used an electronic bulletin board program on a number of websites today, you should know that. Electronic bulletin board program in order to improve the reading speed of the page, generally will not all the posts listed in a page, but it will be divided into more than one page display, each page shows a certain number of posts, such as 20. Do you want to know how to implement a paging display? Please read this article!

"Dynamic Web Design Skills--asp" A article from the first phase has been with friends spent most of the year, I believe that through this period of study, practice to study again, practice, we have been able to skillfully use the ASP's built-in objects, ActiveX components to write some basic ASP Application. From the letters I received from my friends, I can clearly feel that our ASP skills are constantly improving. Recently, a lot of friends wrote that I want to write some examples of ASP in the practical application. Therefore, from the beginning of this period I decided to "Dynamic Web design Skills--asp" positioning from the introduction and learning of ASP basic knowledge to the actual operation of ASP to explore and deepen. At the request of my friends, in this period I will give you a talk about "ADO access to the database how to pagination display" problem.

What is the paging display when ADO accesses a database? If you've ever used an electronic bulletin board program on a number of websites today, you should know that. Electronic bulletin board program in order to improve the reading speed of the page, generally will not all the posts listed in a page, but it will be divided into more than one page display, each page shows a certain number of posts, such as 20. This is the database query page display, if you do not understand, to see Yahoo and other search engine query results will understand.

So how exactly can you do to the database query results page display it? In fact, there are many ways, but there are two main types:

First, all records that meet the query criteria in the database are read into the recordset at once, stored in memory, and then through the ADO Recordset object, several properties are specifically supported for paging: PageSize (page size), PageCount (number of pages), and AbsolutePage (Absolute page) to manage paging processing.

Second, according to the customer's instructions, each time from the records that meet the query conditions will be the number of records to read out and display.

The main difference between the two is that the former is a one-time read all the records into memory and then according to the instructions to do the judgment analysis to achieve the effect of pagination display, the latter is the first according to the instructions to make judgments and the specified number of records to meet the requirements of the query to read into memory, so as to directly achieve the function of paging display.

We can clearly feel that when the number of records in the database reaches tens of thousands or more, the first approach is significantly less efficient than the second, because every customer queries the page to keep all qualifying records in server memory, then paging, and so on, if there are more than 100 Customers online, the efficiency of ASP application execution will be greatly affected. However, when the number of databases on the server is not many and the number of people online is not much, the efficiency of the two is the same, the first method is generally used, because the first method of ASP program writing is much simpler than the second method.

Here the author of our common ASP BBS program as an example, to give us an analysis of how the BBS in the implementation of the paging display function, because we generally use the BBS program database records and the number of visitors are not too much, So the following program instance is the first page-display method that was previously described using.

When you make ADO access to the database, the paging display, in fact, is the record of the recordset to operate. So we first have to understand the properties and methods of the Reordset object:

BOF attribute: Current indicator refers to the first pen in the RecordSet.

EOF attribute: The current indicator refers to the last stroke of the RecordSet.

Move method: Moves the metric to a record in the recordset.

AbsolutePage Property: Sets the location of the current record in which page AbsolutePosition property: The current indicator's position in the RecordSet.

PageCount property: Displays data about how many "pages" the Recordset object includes.

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

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

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

First, BOF and EOF attributes

Typically, we write code in an ASP program to verify the BOF and EOF attributes, so that we know the location of the recordset pointed to by the current indicator, and use the BOF and EOF attributes to know whether a Recordset object contains records or whether the move record row has exceeded the Re The scope of the Cordset object.

such as: <% if not rs.eof then ...%>

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

If the current record is positioned before the first row of a Recordset object, the BOF property returns True, and vice versa returns false.

If the current record is positioned after the last row of a Recordset object, the EOF property returns True and vice versa.

BOF and EOF are False: Indicates that the indicator is in the RecordSet.

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

BOF and EOF are True: There are no records in the recordset.

Two, Move method

You can move the metric to a record in the recordset using the motion method, as follows:

Rs. Move Numrecords,start

The "RS" here is an object variable that represents a Recordset object that wants to move when the current record is positioned; "NumRecords" is a positive negative expression that sets the number of moves for the current record position; "Start" is an optional item that specifies the label at which the record starts.

All Recordset objects support the Move method, and if the numrecords argument is greater than 0, the current record position moves toward the end, and if it is less than 0, the current record position moves toward the beginning; If an empty Recordset object calls the Move method, An error will be generated.

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

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

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

Move [n] Method: Moves the metric to the nth record, and N is counted from 0.

Third, AbsolutePage properties

The AbsolutePage property sets the number of page numbers where the current record is located, and the PageSize property to divide the Recordset object into logical pages, with a record number of PageSize per page (except that the last page may have fewer than PageSize records )。 It must be noted that not all data providers support this attribute, so be careful when using it.

As with the AbsolutePosition property, the AbsolutePage property starts with 1, and if the current record is the first row of the recordset, the AbsolutePage is 1. You can set the AbsolutePage property to move to the first row record position on a specified page.

Four, AbsolutePosition property

If you need to determine where the current metrics are in the RecordSet, you can use the AbsolutePosition property.

The value of the AbsolutePosition attribute is the current index relative to the position of the first pen, from 1, that is, the first absoluteposition is 1.

Note that when you access the recordset, you cannot guarantee that the recordset will appear in the same order each time.

To enable AbsolutePosition, you must first set up to use the client cursor (pointer), the ASP code is as follows:

Rs2. CursorLocation = 3

Five, PageCount properties

Use the PageCount property to determine how many "pages" of data the Recordset object includes. Here the "page" is a collection of data records, the size is equal to the setting of the PageSize property, even if the last page records less than the PageSize value, the last page is a PageCount page. It is important to note that not all data providers support this property.

Six, PageSize property
The PageSize property is the key to determining how ADO is paged when it accesses a database, using it to determine how many records make up a logically "one page". Sets and builds the size of a page to allow the use of the AbsolutePage property to move to the first record of another logical page. PageSize properties can be set at any time.

Vii. RecordCount Properties

This is also a very common and important attribute, and we often use the RecordCount property to find out how many records a Recordset object includes. such as: <% totle=rs. RecordCount%>
After understanding the attributes and methods of the Recordset objects, let's consider how they can be used to achieve the purpose of our paging. First, we can set a value for the PageSize property to specify the number of rows that make up a page from the group of records, and then determine the total number of records by the RecordCount property, and then divide the total number of records by PageSize to get the total amount of pages displayed; The AbsolutePage property is able to complete access to the specified page. As if it's not complicated, let's take a look at how the program should be implemented.

We build such a simple BBS application, its database has the following five fields: "ID", the automatic number of each post, "subject", the subject of each post, "name", the user's name, "email", the user's e-mail address; "Postdate "and add a post to the time." The DSN for the database is "BBS". We'll put all the steps for displaying the post pagination in a process called "showlist ()" for easy invocation. The procedure is as follows:

\ '----BBS display posts paging----
<% Sub Showlist ()%>
<%
PGSZ=20 \ Set switch, specify the number of posts displayed on each page, default to 20 posts a 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 follow the ID of the post in reverse order
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) \ ' Sets the value of the PageSize property
Total=int (RS.RECORDCOUNT/PGSZ *-1) *-1 \ ' Calculates 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 "(total) &rs. RecordCount & "The information that conforms to the condition, displays" &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> Themes </b></td>< td> < b> user </b></td>< td>< b>email</b></td>< td>< B> Release date </B> </td></font>< TR bgcolor= #FFFFFF > "
Do While not (RS are nothing)
RowCount = RS. PageSize
Do but not RS. EOF and RowCount > 0
If N=1 Then
Response.Write "< TR bgcolor= #FFFFFF >"
ELSE
Response.Write "< TR bgcolor= #EEEEEE >"
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 procedure, so the author does not explain in detail here. It is noteworthy that a small tip < INPUT type= "HIDDEN" name= "PageNo" value= "<% =pageno%>" is used in this procedure, which is a "tunnel" for passing data each time the ASP file is invoked. , because we need to pass the arguments that represent the current page number each time the program is invoked, you might think of using the session, but in terms of saving system resources and versatility, using a hidden form to pass data will achieve better results.

Well, it's time to say goodbye, if you don't fully understand the procedures listed in this article, then you have to add the oil, look at the grammar of VbScript, if you have some questions, you can "sister son" I, I will try to answer; If you have any better advice, please write to me.:)