ASP + sqlserver paging method (no stored procedure required)

My ASP + sqlserver paging program: This program is characterized by dropping the current page record to the page. The performance is good, and there is no need to store stored procedures. Because the code is not encapsulated, it is scattered. To use it in your program, you need to simply read it first. Then pay attention to the meaning of the places you need to set. Some of them can be omitted. Some parameter passing can also be omitted.

The Code is as follows:

'''' Selei's snow paging tool (SQL Server) does not need stored procedures -------------------------

'In the annotations, ### must be set by the user.
'Parameter passing ** is required in the annotation.

'Define variables
Dim toption 'query Condition
Dim torder 'sort string
The dim torderfield 'sorting field can be obtained through the parameter: order_field
The dim torderdirection 'sorting direction can be obtained through the parameter: order_direction

Dim tpagesize 'page size
Dim ttotalcount 'Total number of records can be obtained through the parameter: t_count
Dim tpagecount 'page
The current page number of dim tcurpage can be obtained through the parameter: Page

Dim ttablename' table or view name
Dim tfieldalias
List of fields queried by dim tfieldlist
Dim tpagefield field used for paging Fields

Number of records retrieved by dim r_count

Set rs = server. Createobject ("ADODB. recordset") 'record set object

'Sorting
Torderfield = request ("order_field") 'to obtain the sorting field (parameter transfer **)
Torderdirection = request ("order_dir") 'to obtain the sorting direction (passing parameters **)

If (torderfield = "") Then torderfield = "item_code" '### set the default sorting Field
If (torderdirection = "") Then torderdirection = "ASC" '### set the default sorting direction

Torder = "order by" & torderfield & "& torderdirection &" "'generate the sorting string

'Define parameters
Tpagesize = find_rs_count '### set the page size
Ttablename = "view_select1" '### set and query tables or views
Tfieldlist = "*" '### list of fields to be queried
Tpagefield = "item_code" '### set a primary key or unique index field for paging Computation

'Page number processing
Tcurpage = request ("page") 'to obtain the current page (parameter transfer **)
Ttotalcount = request ("t_count") 'to obtain the total number of pages (passing parameters **)

If (tcurpage = "") Then tcurpage = 1
If (CINT (tcurpage) = 0) Then tcurpage = 1
If (tpagecount = "") Then tpagecount = 1
If (CINT (tpagecount) = 0) Then tpagecount = 1

'Construct the query condition. It must be different according to the specific program. But the final condition must be "where ??? "
Toption = "issue_flag = 'y'" '### set conditions
If f_c <> "" Then toption = toption & f_c '### set conditions

If trim (toption) = "" then
Toption = "where 1 = 1" 'if there are no conditions, add one.
Else
Toption = "where" & toption
End if

'Construct the query string, the core of this paging program. This query string is the record we only need to download the current page.
If (tcurpage> 1) then
Constr = "select top" & tpagesize & "" & tfieldlist & "from" & ttablename & toption
Constr = constr & "and" & tpagefield & "not in (select top" & tpagesize * (tCurPage-1) & "" & tpagefield & "from" & ttablename & toption & "& torder &") "& torder
Else
Constr = "select top" & tpagesize & "" & tfieldlist & "from" & ttablename & toption & "" & torder
End if
 
 
'Execute the primary query to obtain the corresponding record set.
Call conndatabase () '### create a database connection
Rs. cursorlocation = 3
Rs. Open constr, Conn, 3, 1 ': Query
R_count = Rs. recordcount

'When the total number of records has not been queried and the total number of records exceeds the page size, query the total number of records under the current condition
If (r_count> = tpagesize or tcurpage> 1) and ttotalcount = 0 then
Set rr1_conn.exe cute ("select count (*) from" & ttablename & "& toption)
Ttotalcount = RR (0)
Rr. Close ()
Set RR = nothing
End if
If (CINT (ttotalcount) = 0) Then ttotalcount = r_count 'if the total number of records is 0, set the number of records in the current bad record set to the total number of records, the total number of records is smaller than the page size.

'Calculate the page size and total number of records
If (CINT (ttotalcount)> CINT (tpagesize) then
Tpagecount = CINT (ttotalcount)/CINT (tpagesize )))
If (CINT (ttotalcount) mod CINT (tpagesize)> 0 then
Tpagecount = tpagecount + 1
End if
End if
 
Tcurpage = CINT (tcurpage)
Tpagecount = CINT (tpagecount)

'---------------------------------------------------------------------

This is all the code. If you are interested, you can study it or encapsulate it and add paging navigation and other methods. In short, I hope this code can be useful to everyone.