asp的通用数据分页类
2023-06-13 09:13:54 时间
(原创)<!--#include file="Conn.asp" -->
通用数据分页类
通用分页类,以后写分页显示数据时就轻松多啦.直接调用此类,然后再Execute即可以取得当前页的所有数据.
此类所做的工作是只取得当前页的数据,和总页数和总记录数等等数据.
ASP代码:
<%
"/*****************************分页显示类**************************
"/* 作者:哇哇鱼
"/* 日期:2004年11月18日
"/* 作用:取得某一页的数据并返回给外部
"/* 说明示例:
"/* Dim MyPage=New PageClass
"/* MyPage.Conn=Conn "设置连接对象
"/* MyPage.PageSize=20 "设置一页显示多少条数据 (默认为10条)
"/* MyPage.CurPage=2 "设置当前要显示的页码
"/*""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
"/* MyPage.TableName="Member" "设置表名
"/* MyPage.Fields="ID,MemberName,MemberPass" "设置显示字段列表
"/* MyPage.Condition="ID>100" "设置查询条件
"/* MyPage.OrderBy="ID DESC" "设置排序条件(一定要设置该属性)
"/* Set PageRs=MyPage.Execute "返回当前第2页的数据(RecordSet对象),如果出错则返回Nothing值
"/*""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
"/*"以上的定义也可以用以下的方法:ExecuteBy("表名","字段列表","查询条件","排序条件")
"/* Set PageRs=MyPage.ExecuteBy("Member","ID,MemberName,MemberPass","ID>100","ID DESC")
"/*""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
"/* PageCount=MyPage.PageCount "返回页码总数
"/* RecordCount=MyPage.RecordCount "返回记录总数
"/* NextPage=MyPage.NextPage "返回下页的页码
"/* PrePage=MyPage.PrePage "返回上一页的页码
"/*****************************************************************
Class PageClass
Private Connection "连接数据库的外部Connection对象
Private Rs
Private List_Fields
Private Table_Name
Private Query_Where
Private OrderBy_SQL "字段排序语句部分
Private Page_Count "返回当前查询的记录页总数
Private Page_Size "设置一页显示多少条的记录
Private Cur_Page "设置当前的页码
Private Record_Count "返回当前查询的记录总数
"/****************设置Connection对象****************************
Public Property Let Conn(ByRef ObjConn)
Set Connection=ObjConn
End Property
Public Property Get Conn()
Set Conn=Connection
End Property
"/****************End******************************************
"/****************设置查询SQL语句*******************************
""查询显示字段
Public Property Let Fields(ByVal Value)
List_Fields=Value
End Property
Public Property Get Fields()
Fields=List_Fields
End Property
""查询表名
Public Property Let TableName(ByVal Value)
Table_Name=Value
End Property
Public Property Get TableName()
TableName=Table_Name
End Property
""查询条件
Public Property Let Condition(ByVal Value)
Query_Where=Value
End Property
Public Property Get Condition()
Condition=Query_Where
End Property
""*****************排序部分********************************************
""Value 语不用写上Order By 。如: [object].OrderBy="ID Desc,PostTime Asc"
Public Property Let OrderBy(ByVal Value)
OrderBy_SQL=Value
End Property
Public Property Get OrderBy()
OrderBy=OrderBy_SQL
End Property
"/****************End******************************************
"/****************返回当前查询结果的总页数***********************
Public Property Get PageCount()
PageCount=Page_Count
End Property
Public Property Get RecordCount()
RecordCount=Record_Count
End Property
Public Property Get NextPage()
If Cur_Page<Page_Count Then
NextPage=Cur_Page+1
Else
NextPage=Page_Count
End If
End Property
Public Property Get PrePage()
If Cur_Page>1 Then
PrePage=Cur_Page-1
Else
PrePage=Cur_Page
End If
End Property
"/****************End******************************************
"/****************设置一页显示的记录数***************************
Public Property Let PageSize(ByVal Value)
If Not IsNumeric(Value) Or Value="" Then
Value=10
Else
Value=Cint(Value)
End If
If Value<1 Then Value=10
Page_Size=Value
End Property
Public Property Get PageSize()
PageSize=Page_Size
End Property
""设置当前的页码数**************************
Public Property Let Page(ByVal Value)
If Not IsNumeric(Value) Or Value="" Then
Value=1
Else
Value=CLng(Value)
End If
If Value<1 Then Value=1
Cur_Page=Value
End Property
Public Property Get Page()
Page=Cur_Page
End Property
"/****************End******************************************
Private Sub Class_Initialize
"初始化RecordSet对象
Page_Size=10 "默认一页为10条数据
CurPage=1 "默认当前为第一页
Record_Count=0
Page_Count=0
End Sub
Private Sub Class_Terminate
Call CloseRecordSet
End Sub
"/***关闭数据库的连接*******
Private Sub CloseRecordSet
On Error Resume Next
If IsObject(Rs) Then
Rs.Close
Set Rs=Nothing
End If
On Error Goto 0
End Sub
"/**********执行查询返回对应页码的数据***********************************************
Public Function ExecuteBy(ByVal oTableName,ByVal oFields,ByVal oCondition,ByVal oOrderBy)
Table_Name=oTableName
List_Fields=oFields
Query_Where=oCondtion
OrderBy_SQL=oOrderBy
Set ExecuteBy=Execute()
End Function
"查询并返回当前CurPage的页码记录
Public Function Execute()
Call CloseRecordSet
On Error Resume Next
Dim TSQL,TopMod,sWhere
If Not IsObject(Connection) Or Table_Name="" Or OrderBy_SQL="" Then
Set Execute=Nothing
Record_Count=0
Page_Count=0
Exit Function
End If
If Trim(Query_Where)<>"" Then
sWhere="Where "&Query_Where
Else
sWhere=""
End If
TSQL="Select Count(*) From ["&Table_Name&"] "&sWhere
Record_Count=Connection.Execute(TSQL)(0) "获取记录总数
If Err Then
Err.Clear
Set Execute=Nothing
Record_Count=0
Page_Count=0
Exit Function
End If
If Record_Count<1 Then
Set Execute=Nothing
Record_Count=0
Page_Count=0
Exit Function
End If
"取得页的总数
If Record_Count Mod Page_Size <>0 Then
TopMod=Record_Count Mod Page_Size
Page_Count=Fix(Record_Count/Page_Size)+1
If Cur_Page<Page_Count Then
TopMod=Page_Size
End If
Else
TopMod=Page_Size
Page_Count=Fix(Record_Count/Page_Size)
End If
If Cur_Page>Page_Count Then Cur_Page=Page_Count
If Cur_Page<1 Then Cur_Page=1
If Trim(List_Fields)="" Then List_Fields="*"
TSQL="Select * From (Select Top "&TopMod&" * From (Select Top "&(Cur_Page*Page_Size)&" "&List_Fields&" From ["&Table_Name&"] "&sWhere&" Order By "&OrderBy_SQL&") Order By "&TransformOrder(OrderBy_SQL)&")Order By "&OrderBy_SQL
Set Rs=Connection.Execute(TSQL)
If Err Then
Err.Clear
Set Execute=Nothing
Record_Count=0
Page_Count=0
Exit Function
End If
Set Execute=Rs
End Function
"转换OrderBy的顺序 ASC->DESC DESC->ASC
Private Function TransformOrder(ByVal Value)
If Value="" Then
TransformOrder=""
Exit Function
End If
Dim OrderArray,i,Result,ByString,Fields,InPos
OrderArray=Split(Value,",") "分解每个字段值
For i=0 To Ubound(OrderArray)
If OrderArray(i)<>"" Then
InPos=InStrRev(Trim(OrderArray(i))," ") "找出排序的顺序
If InPos<1 Then "如果找不到则是ASC排序
ByString="ASC"
Fields=OrderArray(i)+" "
Else
ByString=Trim(Mid(OrderArray(i),InPos+1))
Fields=Left(OrderArray(i),InPos)
If ByString<>"" Then
ByString=UCase(ByString)
Else
ByString="ASC"
End If
End If
""转换排序
If ByString="ASC" Then
ByString="DESC"
Else
ByString="ASC"
End If
Result=Result+Fields+ByString+","
End If
Next
If Result<>"" Then Result=Left(Result,Len(Result)-1)
TransformOrder=Result
End Function
End Class
"示例代码:
Sub Show_List
Dim Page,PageRs
Page=Request("Page")
Dim MyPage
Set MyPage=New PageClass
MyPage.Conn=Conn
MyPage.PageSize=20
MyPage.Page=Page
MyPage.TableName="table1"
MyPage.Fields="*"
MyPage.OrderBy="ID Asc"
Set PageRs=MyPage.Execute
"Set PageRs=MyPage.ExecuteBy("table1","*","","ID Asc")
If PageRs Is Nothing Then Exit Sub
Do Until PageRs.Eof
Response.Write " <tr bgcolor=""#FDFDFD"" style=""cursor:hand"" onmouseover=""this.style.background="#F3F3F3""" onmouseout=""this.style.background="#FDFDFD""">"
Response.Write " <td height=""20""><div align=""center"">"&PageRs("ID")&"</div></td>"
Response.Write " <td>"&PageRs("aaaa")&"</td>"
Response.Write " <td><a href="""&PageRs("bbbb")&"""><font color="#000000">"&PageRs("bbbb")&"</font></a></td>"
Response.Write " <td>"&PageRs("cccc")&"</td>"
Response.Write " </tr>"
PageRs.MoveNext
Loop
PageRs.Close
PageCount=MyPage.PageCount
Page=MyPage.Page "取得当前正确的页码数
NextPage=MyPage.NextPage
PrePage=MyPage.PrePage
Set PageRs=Nothing
Set MyPage=Nothing
End Sub
Show_List
%>
通用数据分页类
通用分页类,以后写分页显示数据时就轻松多啦.直接调用此类,然后再Execute即可以取得当前页的所有数据.
此类所做的工作是只取得当前页的数据,和总页数和总记录数等等数据.
ASP代码:
<%
"/*****************************分页显示类**************************
"/* 作者:哇哇鱼
"/* 日期:2004年11月18日
"/* 作用:取得某一页的数据并返回给外部
"/* 说明示例:
"/* Dim MyPage=New PageClass
"/* MyPage.Conn=Conn "设置连接对象
"/* MyPage.PageSize=20 "设置一页显示多少条数据 (默认为10条)
"/* MyPage.CurPage=2 "设置当前要显示的页码
"/*""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
"/* MyPage.TableName="Member" "设置表名
"/* MyPage.Fields="ID,MemberName,MemberPass" "设置显示字段列表
"/* MyPage.Condition="ID>100" "设置查询条件
"/* MyPage.OrderBy="ID DESC" "设置排序条件(一定要设置该属性)
"/* Set PageRs=MyPage.Execute "返回当前第2页的数据(RecordSet对象),如果出错则返回Nothing值
"/*""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
"/*"以上的定义也可以用以下的方法:ExecuteBy("表名","字段列表","查询条件","排序条件")
"/* Set PageRs=MyPage.ExecuteBy("Member","ID,MemberName,MemberPass","ID>100","ID DESC")
"/*""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""""
"/* PageCount=MyPage.PageCount "返回页码总数
"/* RecordCount=MyPage.RecordCount "返回记录总数
"/* NextPage=MyPage.NextPage "返回下页的页码
"/* PrePage=MyPage.PrePage "返回上一页的页码
"/*****************************************************************
Class PageClass
Private Connection "连接数据库的外部Connection对象
Private Rs
Private List_Fields
Private Table_Name
Private Query_Where
Private OrderBy_SQL "字段排序语句部分
Private Page_Count "返回当前查询的记录页总数
Private Page_Size "设置一页显示多少条的记录
Private Cur_Page "设置当前的页码
Private Record_Count "返回当前查询的记录总数
"/****************设置Connection对象****************************
Public Property Let Conn(ByRef ObjConn)
Set Connection=ObjConn
End Property
Public Property Get Conn()
Set Conn=Connection
End Property
"/****************End******************************************
"/****************设置查询SQL语句*******************************
""查询显示字段
Public Property Let Fields(ByVal Value)
List_Fields=Value
End Property
Public Property Get Fields()
Fields=List_Fields
End Property
""查询表名
Public Property Let TableName(ByVal Value)
Table_Name=Value
End Property
Public Property Get TableName()
TableName=Table_Name
End Property
""查询条件
Public Property Let Condition(ByVal Value)
Query_Where=Value
End Property
Public Property Get Condition()
Condition=Query_Where
End Property
""*****************排序部分********************************************
""Value 语不用写上Order By 。如: [object].OrderBy="ID Desc,PostTime Asc"
Public Property Let OrderBy(ByVal Value)
OrderBy_SQL=Value
End Property
Public Property Get OrderBy()
OrderBy=OrderBy_SQL
End Property
"/****************End******************************************
"/****************返回当前查询结果的总页数***********************
Public Property Get PageCount()
PageCount=Page_Count
End Property
Public Property Get RecordCount()
RecordCount=Record_Count
End Property
Public Property Get NextPage()
If Cur_Page<Page_Count Then
NextPage=Cur_Page+1
Else
NextPage=Page_Count
End If
End Property
Public Property Get PrePage()
If Cur_Page>1 Then
PrePage=Cur_Page-1
Else
PrePage=Cur_Page
End If
End Property
"/****************End******************************************
"/****************设置一页显示的记录数***************************
Public Property Let PageSize(ByVal Value)
If Not IsNumeric(Value) Or Value="" Then
Value=10
Else
Value=Cint(Value)
End If
If Value<1 Then Value=10
Page_Size=Value
End Property
Public Property Get PageSize()
PageSize=Page_Size
End Property
""设置当前的页码数**************************
Public Property Let Page(ByVal Value)
If Not IsNumeric(Value) Or Value="" Then
Value=1
Else
Value=CLng(Value)
End If
If Value<1 Then Value=1
Cur_Page=Value
End Property
Public Property Get Page()
Page=Cur_Page
End Property
"/****************End******************************************
Private Sub Class_Initialize
"初始化RecordSet对象
Page_Size=10 "默认一页为10条数据
CurPage=1 "默认当前为第一页
Record_Count=0
Page_Count=0
End Sub
Private Sub Class_Terminate
Call CloseRecordSet
End Sub
"/***关闭数据库的连接*******
Private Sub CloseRecordSet
On Error Resume Next
If IsObject(Rs) Then
Rs.Close
Set Rs=Nothing
End If
On Error Goto 0
End Sub
"/**********执行查询返回对应页码的数据***********************************************
Public Function ExecuteBy(ByVal oTableName,ByVal oFields,ByVal oCondition,ByVal oOrderBy)
Table_Name=oTableName
List_Fields=oFields
Query_Where=oCondtion
OrderBy_SQL=oOrderBy
Set ExecuteBy=Execute()
End Function
"查询并返回当前CurPage的页码记录
Public Function Execute()
Call CloseRecordSet
On Error Resume Next
Dim TSQL,TopMod,sWhere
If Not IsObject(Connection) Or Table_Name="" Or OrderBy_SQL="" Then
Set Execute=Nothing
Record_Count=0
Page_Count=0
Exit Function
End If
If Trim(Query_Where)<>"" Then
sWhere="Where "&Query_Where
Else
sWhere=""
End If
TSQL="Select Count(*) From ["&Table_Name&"] "&sWhere
Record_Count=Connection.Execute(TSQL)(0) "获取记录总数
If Err Then
Err.Clear
Set Execute=Nothing
Record_Count=0
Page_Count=0
Exit Function
End If
If Record_Count<1 Then
Set Execute=Nothing
Record_Count=0
Page_Count=0
Exit Function
End If
"取得页的总数
If Record_Count Mod Page_Size <>0 Then
TopMod=Record_Count Mod Page_Size
Page_Count=Fix(Record_Count/Page_Size)+1
If Cur_Page<Page_Count Then
TopMod=Page_Size
End If
Else
TopMod=Page_Size
Page_Count=Fix(Record_Count/Page_Size)
End If
If Cur_Page>Page_Count Then Cur_Page=Page_Count
If Cur_Page<1 Then Cur_Page=1
If Trim(List_Fields)="" Then List_Fields="*"
TSQL="Select * From (Select Top "&TopMod&" * From (Select Top "&(Cur_Page*Page_Size)&" "&List_Fields&" From ["&Table_Name&"] "&sWhere&" Order By "&OrderBy_SQL&") Order By "&TransformOrder(OrderBy_SQL)&")Order By "&OrderBy_SQL
Set Rs=Connection.Execute(TSQL)
If Err Then
Err.Clear
Set Execute=Nothing
Record_Count=0
Page_Count=0
Exit Function
End If
Set Execute=Rs
End Function
"转换OrderBy的顺序 ASC->DESC DESC->ASC
Private Function TransformOrder(ByVal Value)
If Value="" Then
TransformOrder=""
Exit Function
End If
Dim OrderArray,i,Result,ByString,Fields,InPos
OrderArray=Split(Value,",") "分解每个字段值
For i=0 To Ubound(OrderArray)
If OrderArray(i)<>"" Then
InPos=InStrRev(Trim(OrderArray(i))," ") "找出排序的顺序
If InPos<1 Then "如果找不到则是ASC排序
ByString="ASC"
Fields=OrderArray(i)+" "
Else
ByString=Trim(Mid(OrderArray(i),InPos+1))
Fields=Left(OrderArray(i),InPos)
If ByString<>"" Then
ByString=UCase(ByString)
Else
ByString="ASC"
End If
End If
""转换排序
If ByString="ASC" Then
ByString="DESC"
Else
ByString="ASC"
End If
Result=Result+Fields+ByString+","
End If
Next
If Result<>"" Then Result=Left(Result,Len(Result)-1)
TransformOrder=Result
End Function
End Class
"示例代码:
Sub Show_List
Dim Page,PageRs
Page=Request("Page")
Dim MyPage
Set MyPage=New PageClass
MyPage.Conn=Conn
MyPage.PageSize=20
MyPage.Page=Page
MyPage.TableName="table1"
MyPage.Fields="*"
MyPage.OrderBy="ID Asc"
Set PageRs=MyPage.Execute
"Set PageRs=MyPage.ExecuteBy("table1","*","","ID Asc")
If PageRs Is Nothing Then Exit Sub
Do Until PageRs.Eof
Response.Write " <tr bgcolor=""#FDFDFD"" style=""cursor:hand"" onmouseover=""this.style.background="#F3F3F3""" onmouseout=""this.style.background="#FDFDFD""">"
Response.Write " <td height=""20""><div align=""center"">"&PageRs("ID")&"</div></td>"
Response.Write " <td>"&PageRs("aaaa")&"</td>"
Response.Write " <td><a href="""&PageRs("bbbb")&"""><font color="#000000">"&PageRs("bbbb")&"</font></a></td>"
Response.Write " <td>"&PageRs("cccc")&"</td>"
Response.Write " </tr>"
PageRs.MoveNext
Loop
PageRs.Close
PageCount=MyPage.PageCount
Page=MyPage.Page "取得当前正确的页码数
NextPage=MyPage.NextPage
PrePage=MyPage.PrePage
Set PageRs=Nothing
Set MyPage=Nothing
End Sub
Show_List
%>
相关文章
- 在MySQL5中使用ASP搭建数据库连接(asp连接mysql5)
- ASP读取MySQL时间的方式(asp读取mysql时间)
- 企业网站用ASP读取MySQL数据持续发展(asp网站读取MySQL)
- MySQL如何使用ASP插入数据(asp插入数据mysql)
- MySQL语句实现 ASP 程序开发精彩功能(asp mysql语句)
- ASP驱动的Oracle数据库连接源码解析(asp连oracle源码)
- 使用ASP连接Oracle数据源的简便方式(asp数据源oracle)
- ASP与Oracle联手,智慧集群打造Web应用(asp和oracle联用)
- ASP程序从Oracle数据库精挖宝贵资源(asp取oracle数据)
- 利用ASP 与 Oracle 技术实现项目进度管理(asp oracle项目)
- Oracle ASP程序助你轻松下载(oracle asp下载)
- ASP中Web页面间的数据传递方式
- ASP编程入门进阶(二十):ADO组件之删除数据记录
- 在ASP.NET中重写URL的代码
- 在Asp中用“正则表达式对象”来校验数据的合法性
- asp.net下用DataSet生成XML的问题
- asp.net下模态对话框关闭之后继续执行服务器端代码的问题
- asp代码实现检测组件是否安装的函数
- 将ASP记录集输出成n列的表格形式显示的方法
- asp.net面试笔试题目[附答案]
- asp.net(C#)Access数据操作类
- asp.net数据访问层存储过程分页语句
- asp.netURL重写简化版速学URL重写
- asp中的rs.open与conn.execute的区别说明
- CentOS5.1下跑Mono和Asp.net的实现方法分享
- asp.net中对象失去焦点时自动提交数据V2
- ASP.NETEval进行数据绑定的方法
- asp.netEXECUTENONQUERY()返回值介绍
- asp.net使用npoi读取excel模板并导出下载详解
- asp.net发邮件示例