Fully functional paging programs + efficient SQL query statements

Subject: [Absolutely original] fully functional paging programs + efficient SQL query statements
Author: sunshift (http://www.56390.com /)
Level 1:
Reputation: 99
Forum: Web development ASP
Problem count: 10
Replies: 28
Posting time: 10:30:53

<%
''''''''''''''''''''''''''''''''''''
''Function complete paging program + efficient SQL query statement
''Power by love_computer
'Love_computer@163.com
''Www.56390.com
''Last modification time:
''If you have any questions during use, please post them and reply in time.
''''''''''''''''''''''''''''''''''''
Tablename = "newsinfo"
Fieldname = "*"
Searchwhere = ""
Orderfieldname = "ID"
Onlyfieldname = "ID"
Ordertype = 1
Pagesizenum = 10
Page = trim (request. querystring ("page "))
Call showtotalpage ()
Rstotal = SESSION ("rstotal ")
Totalpages = SESSION ("totalpages ")
Currentpage = SESSION ("currentpage ")
Session ("rstotal") = ""
Session ("totalpages") = ""
Session ("currentpage") = ""
'''''''''''''''''''''''''''''''''''''''' '''''''''''''''''
Pagesql = getpagesql (tablename, fieldname, searchwhere, orderfieldname, onlyfieldname, ordertype, pagesizenum, currentpage)

''' Display page
Call showpages (1, 1)

''' Created at: [love_computer@163.com]
''' Www.56390.com
''' Last modification time :,
''' Get the total number of pages and total number of records
Sub showtotalpage ()
The following content of REM is directly copied and called without any modification.
If page = "" Or isempty (PAGE) = true or page = "0" or page = empty then page = 1
Strwhere = ""
If searchwhere = "" then
Strwhere = ""
Else
Strwhere = "where" & searchwhere
End if
Strtotal = "select count ([" & onlyfieldname & "]) as [rstotal] from [" & tablename & "]" & strwhere
Set rstotalrecord = conn. Execute (strtotal)
Rstotal = rstotalrecord ("rstotal ")
Rstotalrecord. Close: Set rstotalrecord = nothing
Currentpage = page
Currentpage = int (currentpage)
If rstotal mod pagesizenum = 0 then
Totalpages = int (rstotal/pagesizenum)
Else
Totalpages = int (rstotal/pagesizenum) + 1
End if
If currentpage> totalpages then currentpage = totalpages
Session ("rstotal") = rstotal 'total record
Session ("totalpages") = totalpages total number of pages
Session ("currentpage") = currentpage 'current page
End sub

''' Display paging information
''Www.56390.com
Sub showpages (showlistorder, showselect, showhidden)
If rstotal <= pagesizenum then exit sub
Strurl = pageurl
If currentpage = 1 then
Previouspage = "Homepage"
Else
Previouspage = "<a href = '" & strurl & "1'> homepage </a> <a href = '" & strurl & currentpage-1 & "'> previous page </ a>"
End if
If currentpage = totalpages then
Nextpage = "next last page"
Else
Nextpage = "<a href = '" & strurl & currentpage + 1 & "'> next page </a> <a href = '" & strurl & totalpages & "'> last page </A>"
End if
Showpagenum = ""
If showlistorder = 0 then
If currentpage> 5 then
Xx = currentpage-4
YY = currentpage + 5
Else
Xx = 1
YY = 10
End if

For II = XX to YY
If II> totalpages then exit
Showpagenum = showpagenum & "<a href = '" & strurl & II & "'> ["
If II = currentpage then showpagenum = showpagenum & "<font color = '# ff0000'> <B>"
Showpagenum = showpagenum & ii
If II = currentpage then showpagenum = showpagenum & "</B> </font>"
Showpagenum = showpagenum & "] </a>"
Next
End if

Showtotal = "Total <font color = '# ff0000'> <B>" & rstotal & "</B> </font> records <font color = '# ff000000'> <B>"
If showlistorder = 1 then
Showtotal = showtotal & currentpage &"/"
End if
Showtotal = showtotal & totalpages & "</B> </font> page"
Showtable = "<Table width = '000000' border = '0' cellspacing = '0' cellpadding = '0'>"
If showselect = 0 then
Showtable = showtable & "<form name = 'page' method = 'get' action =''>"
End if
Showtable = showtable & "<tr> <TD align = 'center' valign = 'middle'>"
Showtable = showtable & previouspage & "" & showpagenum & "& nextpage &" & showtotal &""
Response. Write showtable
If showhidden = 0 then
Call showhiddentext ()
End if
Showtable = ""
If showselect = 0 then
Showtable = showtable & "go to <select name = 'page' onchange = 'this. Form. Submit () '>"
For n = 1 to totalpages
Showtable = showtable & "<option value = '" & N &"'"
If n = currentpage then showtable = showtable & "selected"
Showtable = showtable & ">" & N & "</option>"
Next
Showtable = showtable & "</SELECT> page"
End if
Showtable = showtable & "</TD> </tr>"
If showselect = 0 then
Showtable = showtable & "</form>"
End if
Showtable = showtable & "</table>"
Response. Write showtable
End sub

''' Get the complete URL
''It seems like a Xin came from the Internet. It's not clear. hehe
Function fullurl ()
Dim strtemp
If lcase (request. servervariables ("HTTPS") = "off" then
Strtemp = "http ://"
Else
Strtemp = "https ://"
End if
Strtemp = strtemp & request. servervariables ("SERVER_NAME ")
If request. servervariables ("server_port") <> 80 then strtemp = strtemp & ":" & request. servervariables ("server_port ")
Strtemp = strtemp & request. servervariables ("url ")
If trim (request. querystring) <> "then strtemp = strtemp &"? "& Trim (request. querystring)
Fullurl = strtemp
End Function

''' Obtain URL-dedicated Paging
''' Power by love_computer...
''''' ''' Www.56390.com
Function pageurl ()
Url = fullurl
Tempurl = Split (URL ,"? ")
If ubound (tempurl) = 0 then
Url = URL &"? Page ="
Else
Tempurl = Split (URL ,"? Page = ")
If ubound (tempurl) = 0 then
Tempurl = Split (URL, "& page ")
Url = tempurl (0) & "& page ="
Else
Url = tempurl (0 )&"? Page ="
End if
End if
Pageurl = URL
End Function

''' Paging SQL statement Functions
Function getpagesql (tablename, fieldname, searchwhere, orderfieldname, onlyfieldname, ordertype, pagesizenum, page)
'Power by love_computer
'Www.56390.com
Strwhere = ""
Strorder = ""
Strsql = ""
Maxminid = ""
If searchwhere = "" then
Strwhere = ""
Maxminid = "where ([" & onlyfieldname & "]"
Else
Strwhere = "where" & searchwhere &""
Maxminid = "and ([" & onlyfieldname & "]"
End if
If ordertype = 0 then
Maxminid = maxminid & "> (select Max"
Strorder = "order by [" & orderfieldname & "] ASC"
Else
Maxminid = maxminid & "<(select Min"
Strorder = "order by [" & orderfieldname & "] DESC"
End if
If page = 1 or page = 0 then
Strsql = "select top" & pagesizenum & "" & fieldname & "from [" & tablename & "]" & strwhere & strorder
Else
Strsql = "select top" & pagesizenum & "" & fieldname & "from [" & tablename & "]" & strwhere & maxminid
Strsql = strsql & "([" & onlyfieldname & "]) from (select top"
Strsql = strsql & pagesizenum * (page-1) & "[" & onlyfieldname & "] from [" & tablename & "]" & strwhere & strorder
Strsql = strsql & ") as temptable)" & strorder
End if
Getpagesql = strsql
End Function
%>