Solutions and suggestions for negative numbers in ASP Recordset pagination

For the recordset page when the phenomenon of negative numbers, I believe that many people have encountered this problem, sometimes Baidu, Google also may not be its solution, now let me summarize.

There is a negative number, which is primarily related to the cursor type. (For example, suppose you already have a conn.asp linked database file and include) now for example, the code is as follows:

'=======================================================
Sql= "SELECT * from table name where condition ORDER BY ID Desc" ' Here the order by condition can be rewritten according to your needs
Set rs=conn.execute (sql) ' = = Note this sentence = =
rs.pagesize=10 ' = = Set the number of records per page to 10===
Page=request.querystring ("page")
If page= "" Then page=1
If not IsNumeric (page) Then page=1
PAGE=CLNG (page)
If page<1 Then page=1
If Page>rs.pagecount Then Page=vrs.pagecount
Rs.absolutepage=page
Dim c
C=1
Do, not rs.eof and C<=rs.pagecount
' Output content
C=c+1
Rs.movenext
Loop
' Make a link to the page
'=======================================================

Based on the above code, the recordset object is directly code: Set rs=conn.execute (SQL), after which the Recordset object's default cursor is 0, that is, the cursor can only scroll forward, the lock type is 0, and the read-only lock is used. The Recordset object cannot be updated.

Therefore, if a negative number occurs for paging, check that the Recordset object is written in the above form, written as:
Set rs=server.createobject ("Adodb.recordset")
Rs.Open sql,conn,1,3
The above indicates that the cursor is 1, can be moved forward backward, the lock type is 3, and multiple records can be updated in batches.

Doing so basically will no longer be a problem, but for insurance, according to the Recordset paging principle is based on reading all records after the number of records, so let the cursor scroll a circle, after the level rs.pagesize=10 add the following two sentences:
Rs.movelast ' cursor moved to last
Rs.movefirst ' cursor moved to the front

The paging principle of a known recordset is to get the value of the Rs.recordcount (total number of records) before it is read from the entire database. This paging method is relatively simple, but there is a fatal disadvantage, when the number of records in the database, according to its paging principle, this will occupy a high system resources, very wasteful, it is recommended that the actual programming does not use this method. Now give you a thought, you can do in the SQL Query page processing, each read a fixed number of records, specific as follows:

Take the n record from the first m record in the database table, using the TOP keyword: Note If the SELECT statement has both top and order by, select from a sorted result set:

SELECT *
From (SELECT top N *
From (SELECT top (M + N-1) * FROM table name ORDER BY ID desc) t1) T2
ORDER BY ID Desc

Using the above SQL statements for paging, the error will be less, the most important is the higher efficiency.