Solution to negative numbers for ASP recordset paging

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 表名 where 条件 order by ID desc" '这里的 order by 条件可根据自己需要改写
Set rs=conn.execute(sql) '===注意一下这一句===
rs.pagesize=10 '===设置每页的记录数为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 while Not rs.eof And c<=rs.pagecount
'输出内容
c=c+1
rs.movenext
Loop
'做页面的链接
'=======================================================

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 the known recordset is to first read the records in the entire database before obtaining Rs. The value of the Recordcount (total number of records). 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 表名 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.