ASP Basics Tutorial: How to page out when ADO accesses a database

"Dynamic website design terrorize--asp article" a article from the first period has been spent with friends for most of the year, I believe that through this period of learning, practice to re-study, re-practice, we have been able to skillfully use the ASP built-in objects, ActiveX components to write some basic ASP Application. From the letters I received from my friends, I can clearly feel that we are constantly upgrading the ASP skill. A lot of friends recently wrote that I want to write some examples of ASP in practical use. Therefore, from the beginning of this period, I decided to change the position of "dynamic website design terrorize--asp" from introducing and learning the basic knowledge of ASP to the discussion and deepening of the actual operation of ASP. At the request of friends, in this issue I will focus on the "How ADO access the database paging display" problem.

What is the paging display when ADO accesses a database? If you have used the electronic bulletin board program on many websites, then you should know that the electronic bulletin board program in order to improve the page read speed, generally will not all the posts in a page to list out, but it is divided into multiple pages display, Each page shows a certain number of posts, such as 20. This is the database query paging display, if you do not understand, to look at Yahoo search engines such as the query results will understand.

So how exactly can the query results of the database be paged out? In fact, there are a lot of methods, but there are two main types:

First, all the records in the database that meet the query criteria are read into the recordset one time, stored in memory, and then through the ADO Recordset object provides several properties that specifically support paging processing: 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 conditions of the query will be read out and display the number of records specified.

The main difference between the two is that the former is a one-time all records are read into memory and then according to the instructions to do the analysis in order to achieve the effect of pagination display, and the latter is based on the instructions to make a judgment and the number of records matching the query criteria read into memory, thus directly to the paging display function.

We can obviously feel that when the number of records in the database reaches tens or more, the first method will be significantly less efficient than the second method, because when each customer queries the page to keep all eligible records in the server memory, and then in the paging and other processing, if there are more than 100 Customers online, the efficiency of ASP application execution will be greatly affected. However, when the number of database records on the server and the number of simultaneous online is not many, the two are very similar in execution efficiency, the first method is generally adopted, because the first method of ASP program writing relative to the second method is much simpler and more straightforward.

Here the author of our common ASP BBS program for example, to give you an analysis of how the BBS program to achieve the paging display function, because we generally use the BBS program database records and the number of simultaneous visits will not be too much, So the following program instance is the first page display method that was previously described.

The paging display of ADO Access database is actually the record of the recordset operation. So we first have to understand the properties and methods of the Reordset object:

BOF Properties: The current indicator refers to the first pen of the RecordSet.

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

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

AbsolutePage property: Sets the position of the current record on which page AbsolutePosition property: The current position of the indicator in the RecordSet.

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

PageSize Properties: 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 look at these important properties and methods in detail

First, BOF and EOF properties

Usually we write code in the ASP program to verify the BOF and EOF properties, so that the current indicator points to the location of the recordset, using the BOF and EOF properties, you can know whether a Recordset object contains a record or whether the mobile record line has exceeded the Re The scope of the Cordset object.

Such as:

<% if not rs.eof then ...%>

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

The BOF property returns True if the position of the current record is before the first row of a Recordset object, or False if it is not.

The EOF property returns True if the position of the current record is after the last row of a Recordset object, or False if it is the reverse.

Both 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.

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

Second, Move method

You can move the indicator to a record in the Recordset with the Move method, with the following syntax:

Rs. Move Numrecords,start

The "RS" here is an object variable that represents a Recordset object that you want to move at the current record position; NumRecords "is a positive negative number, set the current record position of the movement;" Start is an optional item that specifies the label at which to start the record.

All Recordset objects support the Move method, and if the NumRecords parameter is greater than 0, the current record position moves toward the end, and if it is less than 0, the current record position moves in the direction of the beginning, or 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: Move indicator to nth pen record, n from 0.

Third, AbsolutePage properties

The AbsolutePage property sets the number of pages on which page the current record is located, and uses the PageSize property to split the Recordset object into logically numbered pages, with the number of records per page being PageSize (except for the number of records that may be less than PageSize on the last page) )。 It's important to note that not all data providers support this property, so be careful when you use it.

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

Iv.. AbsolutePosition Properties

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

The value of the AbsolutePosition property is the current indicator's position relative to the first stroke, which is calculated from 1, that is, the first stroke of absoluteposition is 1.

Note that there is no guarantee that the recordset will appear in the same order each time it accesses the recordset.

To enable AbsolutePosition, you must first set the user-side cursor (pointer) to use the ASP code as follows:

Rs2. CursorLocation = 3

V. PageCount Properties

Use the PageCount property to determine how much "page" data the Recordset object includes. The "page" Here is a collection of data records, equal to the setting of the PageSize property, even if the last page has fewer records than PageSize, and the last page is a page of PageCount. It is important to note that this property is not supported by all data providers.

Vi.. PageSize Properties

The PageSize property is the key to how the paging is displayed when ADO accesses the database, and it is used to determine how many records compose a logically "one page". Sets and establishes the size of a page, allowing the use of the AbsolutePage property to move to the first record of another logical page. The PageSize property 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 above properties and methods of the Recordset object, let's consider how we can use them to achieve the purpose of our pagination display. First, we can set a value for the PageSize property to specify the number of rows to make up a page from the group of records, and then to determine the total number of records by the RecordCount property, and then divide the total number of records by PageSize to get the total number 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 is implemented. Shanghai Treatment Impotence Hospital}

We built such a simple BBS application, its database has the following five fields: "ID", the automatic numbering of each post; " Subject ", the subject of each post; Name ", add the name of the user; "Email", the user's email address; " Postdate ", add the time. The DSN for the database is "BBS". We will show all the steps of the post paging in a process called "showlist ()" To make it easy to call. The procedure is as follows:

\ '----BBS show posts paged----

<% Sub Showlist ()%>

<%

PGSZ=20 \ ' Set switch, specify the number of posts displayed on each page, default to 20 pages

Set Conn = Server.CreateObject ("ADODB. Connection ")

Set RS = Server.CreateObject ("ADODB". RecordSet ")

sql = "SELECT * from Message ORDER by ID DESC"

\ ' Query all posts and sort by post ID 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 = "Prev" 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 & "Condition-eligible information, 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> users </b></td>< td>< b>email</b></td>< td>< B> Release date </B> </td></font>< TR bgcolor= #FFFFFF > "

Do and not (RS are nothing)

RowCount = RS. PageSize

Do and 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=\ ' prev \ >"

End If

If RowCount = 0 and PageNo < >total Then

Response.Write "< INPUT type=submit name=\ ' scrollaction\ ' value=\ ' next \ ' > '

End If

Response.Write "</form>"

End if

%>

<% End Sub%>

I believe that everyone should be able to fully understand the above procedures, so the author will not explain in detail here. It is important to note that a small trick is used in this procedure.

< INPUT type= "HIDDEN" name= "PageNo" value= "<% =pageno%>" >

, this is the "Andao" that is used to pass data every time the ASP file is called, because we need to pass a parameter that represents the current page number each time the program is called, but you might think of using the session, but in terms of saving system resources and generality, using such a hidden form To pass the data will achieve better results.

Well, it's time to say goodbye, if you don't fully understand the procedures listed in this article, you have to add oil and look at VbScript's grammar;

ASP Basics Tutorial: How to page out when ADO accesses a database