An asp + sqlserver paging program of mine: The characteristic of this program is to drop the records of the current page onto the page. The performance is good, and there is no need for stored procedures. Since the code is not encapsulated, it is very fragmented. To use it in your program, you need to read it briefly. Then pay attention to the meaning of the places you need to set yourself. Some can be omitted. Some parameter passing can also be omitted.
The code is as follows:
'''' Saibei's Snow paging tool (sql server) does not require stored procedures ------------------------------
'There is # in the comments ## requires user settings
'There is a description of parameter passing** in the comment to be passed through parameters.
'Define variables
dim tOption 'query conditions
dim tOrder 'Sort strings
dim tOrderField 'Order field can be obtained through parameters: order_field
dim tOrderDirection 'Sort direction can be obtained through parameters: order_direction
dim tPageSize 'Page size
dim tTotalCount 'The total number of records can be obtained through parameters: t_count
dim tPageCount 'Number of pages
dim tCurPage 'Current page number can be obtained through parameters: page
dim tTableName 'Table or view name
dim tFieldAlias 'alias of line number
dim tFieldList 'Query field list
dim tPageField 'Field used for paging
dim r_count 'Number of records found
set rs=server.createobject("adodb.recordset") 'Record set object
' sorting processing
tOrderField=Request("order_field") 'Get the sorting field (parameter passing**)
tOrderDirection=Request("order_dir") 'Get the sorting direction (parameter passing**)
if(tOrderField="") then tOrderField="item_code" ' ### Set the default sorting field
if(tOrderDirection="") then tOrderDirection="asc" ' ### Set the default sort direction
'www.downcodes.com
tOrder=" order by " & tOrderField & " " & tOrderDirection & " " 'Generate sorting string
'Define parameters
tPageSize=find_rs_count ' ### Set page size
tTableName="view_select1" ' ### Set and query the table or view
tFieldList=" * " ' ### Field list to be queried
tPageField="item_code" ' ### Set a primary key or unique index field for paging calculations
'Page processing
tCurPage=Request("page") 'Get the current page (parameter passing**)
tTotalCount=Request("t_count") 'Get the total number of pages (parameter passing**)
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 query conditions, which will definitely be different depending on the specific program. But the last 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 is no condition, add one yourself.
else
tOption= " where " & tOPtion
end if
'Construct the query string, the core of this paging program. This query string is the record we need to download only 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 main query and obtain the corresponding record set
Call ConnDataBase() ' ### Establish a database connection
rs.cursorlocation=3
rs.open conStr,conn,3,1 'Execute 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 conditions
if(r_count>=tPageSize or tCurPage>1) and tTotalCount=0 then
set rr=conn.execute("select count(*) from " & tTableName & " " & tOption)
tTotalCount=rr(0)
rr.close()
setrr=nothing
end if
if(cint(tTotalCount)=0) then tTotalCount=r_count 'If the total records are 0, set the number of records in the current difference records set to the total number of records, indicating that the current total number of records is less than the page size
'Calculate the number of pages using the page size and total number of records
if(cint(tTotalCount)>cint(tPageSize)) then
tPageCount=cint((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 the whole code. Friends who are interested can study it, or encapsulate it and add paging navigation and other methods. Anyway, I hope this code can be useful to everyone.