| <SCRIPT language=VBSCRIPT RUNAT=SERVER> '//---- COCOON_自定义类_翻页优化代码 ----//' Class cc_db_Pager '-------------------------------- ' COCOON Db_Pager 类 (Ver: 1) ' 作者: Sunrise_Chen (sunrise_chen@msn.com) ' 请保留此信息，谢谢。 ' Thanks for eway365 from Club.PCHOME.net ' 2003.6 ' ' 2003-07-05 对代码做了较大的改动，增加了一些特性 ' 增加了DISTINCT选择属性 '-------------------------------- '//-------------------- 定义变量 --------------------//' Private sTableName '//表名 Private sSqlString '//自定义Sql语句 Private aCondition() '//查询条件(数组) Private sCondition '//查询条件(字符串) Private iPage '//当前页码 Private iPageSize '//每页记录数 Private iPageCount '//总页数 Private iRecCount '//当前查询条件下的记录数 Private iTotalRecCount '//总记录数 Private sFields '//输出的字段名 Private sOrderBy '//排序字符串 Private sSql '//当前的查询语句 Private sPkey '//主键 Private oConn '//连接对象 Private iDefPageSize '//默认每页显示的记录数 Private sProjectName '/项目名 Private sVersion '/版本号 Private bShowError '//是否显示错误信息 Private bDistionct '//是否显示唯一记录 Private sPageInfo '//记录数、页码等信息 Private sPagerFuncName '//前台翻页函数名 Private bDistinct '//是否Distinct Private sPageParam '//page参数名称 '//-------------------- 事件、方法 --------------------//' '//类初始化事件 Private Sub Class_Initialize() ReDim aCondition(-1) sProjectName = "COCOON 类系列 数据库翻页优化类" sVersion = "1.05" sPkey = "ID" sFields = "*" sCondition = "" sOrderBy = "" sSqlString = "" iPageSize = 10 iPage = 1 iRecCount = Null iTotalRecCount = Null iPageCount = Null bShowError = True bDistionct = False sPageInfo = "当前在第 %1 页 共计 %2 个页面 共有 %3 条记录" sPagerFuncName = "__cc_doPage" sPageParam = "page" End Sub '//类结束事件 Private Sub Class_Terminate() Set oConn = Nothing End Sub '//从来也没搞懂过VBSCRIPT里竟然没有IIF()函数！！！ Private Function IIf(Expr,Val1,Val2) If (Expr) Then IIf = Val1 Else IIf = Val2 End If End Function '//处理错误信息 Public Sub doError(s) Dim sTmp sTmp = CLng(Rnd() * 100) Response.write( "<DIV STYLE='WIDTH:760;font-size:9pt;cursor:hand'>" ) Response.write( "<LABEL ONCLICK='ERRORDIV"&sTmp&".style.display=(ERRORDIV"&sTmp&".style.display==""""?""none"":"""")'><SPAN STYLE='BACKGROUND-COLOR:#CCCC00;COLOR:WHITE;'>〖 CC_db_Pager 提示信息 〗</SPAN><BR></LABEL>" ) Response.write( "<DIV ID='ERRORDIV"&sTmp&"' STYLE='DISPLAY:NONE;WIDTH:100%;BORDER: 1PX SOLID #CCCC00;PADDING:5;overflow:hidden;text-overflow:ellipsis;'><NOBR>" ) Response.write( "<SPAN STYLE='COLOR:RED'>Description</SPAN>: " & s & "<BR>" ) Response.write( "<SPAN STYLE='COLOR:RED'>Provider</SPAN>: " & sProjectName & " <SPAN STYLE='COLOR:RED'>Version</SPAN>: " & sVersion & "<BR>" ) Response.write( "</NOBR></DIV></DIV><BR>" ) End Sub '//产生分页的SQL语句 Public Function getSql() Dim iStart, iEnd Call makeCondition() If Not isNumeric(iPage) Then iPage = 1 If CLng(iPage)<1 Then iPage = 1 iStart = ( iPage - 1 ) * iPageSize iEnd = iStart + iPageSize getSql = " SELECT " & IIf(bDistinct,"DISTINCT","") & " " & sFields & " FROM ["&sTableName&"] " _ & " WHERE ["&sPKey&"] IN ( " _ & " SELECT TOP "&iEnd&" ["&sPKey&"] FROM ["&sTableName&"] " & sCondition & " " & sOrderBy & " " _ & " )" If CLng(iPage)>1 Then getSql = getSql & " AND ["&sPKey&"] NOT IN ( " _ & " SELECT TOP "&iStart&" ["&sPKey&"] FROM ["&sTableName&"] " & sCondition & " " & sOrderBy & " " _ & " )" End If getSql = getSql & " " & sOrderBy & " " End Function '//产生条件字符串 Private Sub makeCondition() If Len(sCondition)>0 Then Exit Sub If UBound(aCondition)>=0 Then sCondition = " WHERE " & Join(aCondition, " AND ") End If End Sub '//计算记录数 Private Sub CaculateRecCount() On Error Resume Next Dim oRs Call makeCondition() sSqlString = "SELECT COUNT(["&sPKey&"]) FROM [" & sTableName & "]" & IIf(Len(sCondition)<1,"",sCondition) Set oRs = oConn.Execute( sSqlString ) If Err Then doError Err.Description : Response.End() iRecCount = oRs.Fields.Item(0).Value Set oRs = Nothing End Sub '//计算总记录数 Private Sub CaculateTotalRecCount() Dim oRs Set oRs = oConn.Execute( "SELECT COUNT(*) FROM [" & sTableName & "]" ) iTotalRecCount = oRs.Fields.Item(0).Value Set oRs = Nothing End Sub '//计算页数 Private Sub CaculatePageCount() If isNull(iRecCount) Then CaculateRecCount() If iRecCount = 0 Then iPageCount = 0 : Exit Sub iPageCount = Abs( Int( 0 - (iRecCount / iPageSize) ) ) End Sub '//设置页码 Private Function setPage(n) iPage = n If Not isNumeric(iPage) Then iPage = 1 If CLng(iPage)<1 Then iPage = 1 End Function '//增加条件 Public Sub AddCondition(s) If Len(s)<0 Then Exit Sub ReDim Preserve aCondition(UBound(aCondition)+1) aCondition(UBound(aCondition)) = s End Sub '//版本信息 Public Function Information() doError "Coding by <A HREF='MAILTO:sunrise_chen@msn.com'>Sunrise_Chen</A> @ <A HREF='http://www.ccopus.com'>;http://www.ccopus.com<;/A> ." End Function '//-------------------- 输入属性 --------------------//' '//定义连接对象 Public Property Set ActiveConnection(o) Set oConn = o End Property '//连接字符串 Public Property Let ConnectionString(s) Set oConn = Server.CreateObject("ADODB.Connection") oConn.ConnectionString = s oConn.Open() End Property '//定义查询表名 Public Property Let TableName(s) sTableName = s End Property '//定义需要输出的字段名 Public Property Let Fields(s) sFields = s End Property '//定义主键 Public Property Let Pkey(s) sPkey = s End Property '//定义排序规则 Public Property Let OrderBy(s) sOrderBy = " ORDER BY " & s & " " End Property '//定义每页的记录条数 Public Property Let PageSize(s) iPageSize = s If Not isNumeric(iPageSize) Then iPageSize = iDefaultPageSize If CLng(iPageSize)<1 Then iPageSize = iDefaultPageSize End Property '//定义当前页码 Public Property Let Page(s) setPage s End Property '//定义当前页码(同Property Page) Public Property Let AbsolutePage(s) setPage s End Property '//自定义查询语句 Public Property Let Sql(s) sSqlString = s End Property '//是否DISTINCT Public Property Let Distinct(b) bDistinct = b End Property Public Property Let PageParam(s) sPageParam = LCase(s) End Property '//-------------------- 输出属性 --------------------//' '//输出查询表名 Public Property Get TableName() TableName = sTableName End Property '//输出需要输出的字段名 Public Property Get Fields() Fields = sFields End Property '//输出主键 Public Property Get Pkey() Pkey = sPkey End Property '//输出排序规则 Public Property Get OrderBy() OrderBy = sOrderBy End Property '//取得当前条件下的记录数 Public Property Get RecordCount If isNull(iRecCount) Then CaculateRecCount() RecordCount = iRecCount End Property '//取得每页记录数 Public Property Get PageSize PageSize = iPageSize End Property '//取得当前查询的条件 Public Property Get Condition If Len(sCondition)<1 Then makeCondition() Condition = sCondition End Property '//取得当前页码 Public Property Get Page Page = iPage End Property '//取得当前页码 Public Property Get AbsolutePage AbsolutePage = iPage End Property '//取得总的记录数 Public Property Get TotalRecordCount If isNull(iTotalRecCount) Then CaculateTotalRecCount() TotalRecordCount = iTotalRecCount End Property '//取得总页数 Public Property Get PageCount If isNull(iPageCount) Then CaculatePageCount() PageCount = iPageCount End Property '//得到分页后的记录集 Public Property Get Recordset On Error Resume Next sSql = getSql() Set Recordset = oConn.Execute( sSql ) If Err Then If bShowError Then doError Err.Description If Len(sSqlString)>0 Then Set Recordset = oConn.Execute( sSqlString ) If Err Then doError Err.Description Response.End() End If Else doError Err.Description End If End If Err.Clear() End Property '//版本信息 Public Property Get Version Version = sVersion End Property '//输出翻页的前台脚本 Public Property Get PagerScript Dim sUrl, sQueryString, x sUrl = Request.ServerVariables("URL") sQueryString = "" For Each x In Request.QueryString If LCase(x)<>sPageParam Then sQueryString = sQueryString & x & "=" & Request.QueryString(x) & "&" Next PagerScript = vbCrLf & _ "<S"&"CRIPT LANGUAGE=""JavaScript"" ID=""cc_script_dbPager"">" & vbCrLf & _ " //"&sProjectName&" (ver: "&sVersion&")" & vbCrLf & _ " //Coding by Sunrise_Chen (sunrise_chen@msn.com)" & vbCrLf & _ " function " & sPagerFuncName & "(n){" & vbCrLf & _ " location.href='" & sUrl & "?" & sQueryString & sPageParam &"='+n+'" & "';" & vbCrLf & _ " }" & vbCrLf & _ "</S"&"CRIPT>" & vbCrLf & _ vbCrLf End Property '//输出页码及记录数等信息 Public Property Get PageInfo CaculatePageCount() PageInfo = Replace(Replace(Replace(sPageInfo,"%3",iRecCount),"%2",iPageCount),"%1",iPage) End Property '//输出翻页按钮 Public Property Get Pager Pager = "" & vbCrLf _ & "[<A "&IIf(CLng(iPage)<=1,"disabled href='javascript:void(0);'","href='javascript:"&sPagerFuncName&"(1);'")&">首页</A>]" & vbCrLf _ & "[<A "&IIf(CLng(iPage)<=1,"disabled href='javascript:void(0);'","href='javascript:"&sPagerFuncName&"("&(iPage-1)&");'")&">前页</A>]" & vbCrLf _ & "[<A "&IIf(CLng(iPage)>=CLng(iPageCount),"disabled href='javascript:void(0);'","href='javascript:"&sPagerFuncName&"("&(iPage+1)&");'")&">后页</A>]" & vbCrLf _ & "[<A "&IIf(CLng(iPage)>=CLng(iPageCount),"disabled href='javascript:void(0);'","href='javascript:"&sPagerFuncName&"("&iPageCount&");'")&">末页</A>]" End Property '//输出连接语句 Public Property Get ConnectionString() ConnectionString = oConn.ConnectionString End Property '//输出连接对象 Public Property Get Conn() Set Conn = oConn End Property End Class </SCRIPT> |
使用范例：
Rem 把上面类的代码或者拷贝包含到本页面. DbPath = "db_CC_Counter6.mdb" '定义数据库名 DbPassword = "" sConnString = "DBQ=" & Server.mappath(DbPath)&";DefaultDir=;DRIVER={Microsoft Access Driver (*.mdb)};" '定义连接 set db_Pager = new cc_db_Pager '引用类创建一对象 db_Pager.ConnectionString = sConnString '连接数据库 db_Pager.TableName = "t_Visit" '设置要查询的表名 db_Pager.Sql = "select * from t_Visit" '自定义SQL语句 db_Pager.page = Request("page") temStr = temStr&db_Pager.PagerScript temStr = temStr&db_Pager.PageInfo temStr = temStr&db_Pager.Pager set myRs = db_Pager.Recordset if not(myRs.eof and myRs.bof) then do while not myRs.eof response.write myRs("id")&"|"&myRs("CreateTime")&"|"&myRs("LastActTime")&"|"&myRs("IsOnline")&"<br>" myRs.movenext loop end if response.Write(temStr) set db_Pager = nothing |