'*******************************************************
'Page Pagination Class
'Features: Use not in paging output method, and need to be used in conjunction with the sp_Page stored procedure, which is relatively fast
'Input parameters connstr: Database link string StrTable: Table to query StrText: Field to query StrIndex: Index field
'Optional parameters: (iPageSize: Number of records per page StrWhere: Conditional statement StrOrder: Sort fields StrSc: Sort method ActionStr: Query parameters submitted by From)
'Output parameters rsArray:Returned record set array GetTotalNum:Total number of records returned GetiPage:Current page number GetTotalPage:Total number of pages GetiPageSize:Number of displayed pieces per page
'Output method GetFenYe():Pagination GetFenYeJmp(inForm):Pagination with jump
'Author: June Yu QQ: 44569457 Compilation time: September 27, 2008
'*******************************************************
Class Page
Private connstr 'Database link string
Private iPage 'Current page number
Private iPageSize 'Number of records per page
Private StrTable 'The table to be queried
Private StrText 'The field to be queryed
Private StrWhere 'conditional statement
Private StrIndex 'Index
Private StrOrder 'Sort fields
Private StrSc 'Sorting Method
Private ActionStr 'Page Turn Link Characters
Private Rs_dbs 'Recordset name
Private cmd 'cmd object name
Private rsArray 'Returned record set array
Private TotalNum 'Total records returned
Private Sub Class_Initialize() 'Initialize class
iPageSize=10
iPage=trim(Request("iPage"))
ActionStr=""
StrWhere=""
StrOrder=""
StrSc=""
If (not IsNumeric(iPage)) Then
iPage=1
Else
If iPage<1 then iPage=1
If iPage>5000000 then iPage=1
End If
End Sub
Private Sub Class_Terminate() 'Release class
set Rs_dbs=nothing
End Sub
Public Property Let SetConnstr(svalue) 'Get: Database link string
connstr=Lcase(svalue)
End Property
Public Property Let SetiPageSize(svalue) 'Get: Number of records per page
iPageSize=clng(svalue)
End Property
Public Property Let SetStrTable(svalue) 'Get: SQL table name
StrTable=Lcase(svalue)
End Property
Public Property Let SetStrText(svalue) 'Get: field of SQL query
StrText=Lcase(svalue)
End Property
Public Property Let SetStrWhere(svalue) 'Get: SQL condition field
StrWhere=Lcase(svalue)
End Property
Public Property Let SetStrIndex(svalue) 'Get: Index field
StrIndex=Lcase(svalue)
End Property
Public Property Let SetStrOrder(svalue) 'Get: sort field
StrOrder=Lcase(svalue)
End Property
Public Property Let SetStrSc(svalue) 'Get: sorting method
StrSc=Lcase(svalue)
End Property
Public Property Let SetActionStr(svalue) 'Get: page turn link character
ActionStr=Lcase(svalue)
End Property
Private Function MadeOrderBy() 'Method: Combining Order By statements
dim TempStrOrder,TempStrSc,t
if StrOrder<>"" and StrSc<>"" then
TempStrOrder=split(StrOrder,",")
TempStrSc=split(StrSc,",")
if ubound(TempStrOrder)=ubound(TempStrSc) then
for t=0 to ubound(TempStrOrder)
if t=0 then
MadeOrderBy=TempStrOrder(t)&" "&TempStrSc(t)
else
MadeOrderBy=MadeOrderBy&","&TempStrOrder(t)&" "&TempStrSc(t)
end if
next
end if
end if
End Function
Private Sub OpenRs() 'Method: Obtain record set
Set Rs_dbs=("")
Set cmd = ("")
with cmd
.ActiveConnection = connstr 'Database connection string
.CommandText = "sp_Page" 'Specify the stored procedure name
.CommandType = 4 ' indicates that this is a stored procedure
.Prepared = true 'Requires SQL command to be compiled first
. .CreateParameter("@iPage",3,1,4,iPage) 'Specify the number of pages
. .CreateParameter("@iPageSize",3,1,4,iPageSize) 'Number of records per page
. .CreateParameter("@StrTable",200,1,200,StrTable) 'The name of the table to be queryed during pagination
. .CreateParameter("@StrText",200,1,1000,StrText) ' field
. .CreateParameter("@StrWhere",200,1,1000,StrWhere) 'Query conditional statement in conditional where
. .CreateParameter("@StrIndex",200,1,30,StrIndex) 'Index value
. .CreateParameter("@StrOrder",200,1,100,MadeOrderBy()) 'Sorted fields
. .CreateParameter("@StrTotals",3,2,10) 'Total page count output
Set Rs_dbs = .Execute
end with
End Sub
Public Property Get GetRs() 'Output: Recordset (array method)
call OpenRs()
If not (Rs_dbs.eof and Rs_dbs.bof ) then
GetRs = Rs_dbs.GetRows()
End If
Rs_dbs.close
TotalNum = cmd(7)
set cmd = nothing
End Property
Public Property Get GetTotalNum 'Output: Total record count
GetTotalNum=TotalNum
End Property
Public Property Get GetTotalPage 'Output: Total Pages
If (TotalNum mod iPageSize)<>0 Then
GetTotalPage=(TotalNum\iPageSize)+1
Else
GetTotalPage=(TotalNum\iPageSize)
End If
End Property
Public Property Get GetiPageSize 'Output: Number of pieces displayed per page
GetiPageSize=iPageSize
End Property
Public Property Get GetiPage 'Output: Current page number
GetiPage=iPage
End Property
Private Function GetQueryUrl() 'Method: Get the current URL
dim UrlFile,Query,Querys,i
UrlFile= ("URL")
Query= ("QUERY_STRING")
Querys=split(Query,"&")
For i=0 to ubound(Querys)
if trim(Querys(i))<>"" then
If trim(Querys(i))=replace(trim(Querys(i)),"iPage=","") then
GetQueryUrl=GetQueryUrl&Querys(i)&"&"
End If
End If
Next
if ActionStr="" then
GetQueryUrl=UrlFile&"?"&GetQueryUrl
else
GetQueryUrl=UrlFile&"?"&ActionStr&"&"&GetQueryUrl
end if
End Function
Public Property Get GetFenYe() 'Output: Pagination
If (iPage>=1) and (clng(iPage)<=clng(GetTotalPage)) then
dim x,y,m,n,i,ActionUrL
ActionUrL=GetQueryUrl()
If iPage="" then iPage=1
iPage=Clng(iPage)
If TotalNum<>0 then
x=TotalNum\iPageSize
y=TotalNum mod iPageSize
If y<>0 then x=x+1 'Total number of pages
if iPage>5 then
If (iPage+5)<=x then
n=iPage-4
m=iPage+5
Else
n=iPage-4
m=x
End If
Else
n=1
m=10
End If
If x<=10 then
If x<>1 then
if iPage<>1 then
" <a href="&ActionUrL&"iPage="&(iPage-1)&">Previous Page</a> "
end if
for i=1 to x
if iPage=i then
(i)
Else
" <a href="&ActionUrL&"iPage="&i&">["&i&"]</a> "
End If
next
if iPage<>x then
" <a href="&ActionUrL&"iPage="&(iPage+1)&">Next Page</a> "
end if
End If
Else
If iPage>5 then
" <a href="&ActionUrL&"iPage=1>[Home]</a> "
End If
If iPage<>1 then
" <a href="&ActionUrL&"iPage="&(iPage-1)&">Previous Page</a> "
end if
for i=n to m
if iPage>x then Exit For
if iPage=i then
(i)
Else
" <a href="&ActionUrL&"iPage="&i&">["&i&"]</a> "
End If
next
if iPage<>x then
" <a href="&ActionUrL&"iPage="&(iPage+1)&">Next Page</a> "
End If
if (iPage+5)<x then
" <a href="&ActionUrL&"iPage="&x&">[Last Page]</a> "
End If
End If
End If
End If
End Property
Public Property Get GetFenYeJmp(inForm) 'Output: Pagination with jump
if inForm then
(GetFenYe()&" <input name='iPage' type='text' value="&iPage&" id='iPage' size='3'> ")
("<input type='submit' name='Submit' value='turn'>")
else
("<form name='Jmp' method='post' action="&GetQueryUrl()&">")
(GetFenYe()&" <input name='iPage' type='text' value="&iPage&" id='iPage' size='3'>")
("<input type='submit' name='Submit' value='turn'>")
("</form>")
end if
End Property
End Class