作家
登录

查询翻页优化第1/2页

作者: 来源:www.28hudong.com 2013-03-30 08:58:14 阅读 我要评论

复制代码 代码如下:<SCRIPT language=VBSCRIPT RUNAT=SERVER> '//---- COCOON_自定义类_翻页优化代码 ----//' Class cc_db_Pager '-------------------------------- ' COCOON Db_Pager 类 (Ver: 1.02) ' 作者: Sunrise_Chen (sunrise_chen@msn.com) ' 请保留此信息,谢谢。 ' ' 2003/06 修正一个错误,描述:如果ID不唯一,将产生多条重复记录的错误。 ' 2003.5 '-------------------------------- '//-------------------- 定义变量 --------------------//' 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 Sub Class_Initialize() ReDim aCondition(-1) iPage = 1 iRecCount = Null iTotalRecCount = Null iPageCount = Null bShowError = True iPageSize = 10 sFields = "*" sPkey = "ID" sCondition = "" sOrderBy = "" sSqlString = "" sProjectName = "COCOON 类系列 数据库翻页优化" sVersion = "1.02" End Sub '//类结束事件 Private Sub Class_Terminate() Set oConn = Nothing End Sub '//错误处理 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"":"""")'>" ) Response.write( "<SPAN STYLE='BACKGROUND-COLOR:#CCCC00;COLOR:WHITE;'>〖 CC_db_Pager 提示信息 〗</SPAN><BR></LABEL>" ) Response.write( "<DIV ID='ERRORDIV"&sTmp&"' STYLE='DISPLAY:NONE;WIDTH:100%;" ) Response.write( "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 ) Response.write( " <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() iStart = ( iPage - 1 ) * iPageSize iEnd = iStart + iPageSize getSql = " SELECT DISTINCT " & sFields & " FROM ["&sTableName&"] " _ & " WHERE ["&sPKey&"] NOT IN ( " _ & " SELECT TOP "&iStart&" ["&sPKey&"] FROM ["&sTableName&"] " & sCondition & " " & sOrderBy & " " _ & " )" _ & " AND ["&sPKey&"] IN ( " _ & " SELECT TOP "&iEnd&" ["&sPKey&"] FROM ["&sTableName&"] " & sCondition & " " & sOrderBy & " " _ & " )" _ & " " & sOrderBy & " " End Function '//产生条件字符串 Private Sub makeCondition() 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() Set oRs = oConn.Execute( "SELECT COUNT(["&sPKey&"]) FROM [" & sTableName & "]" & sCondition ) If Err Then doError Err.Description Response.End() End If iRecCount = oRs.Fields.Item(0).Value Set oRs = Nothing End Sub '//计算总记录数(不带条件) Private Sub CaculateTotalRecCount() On Error Resume Next Dim oRs Set oRs = oConn.Execute( "SELECT COUNT(["&sPKey&"]) FROM [" & sTableName & "]" ) If Err Then doError Err.Description Response.End() End If 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 '//增加条件 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 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) iPage = s If Not isNumeric(iPage) Then iPage = 1 If CLng(iPage)<1 Then iPage = 1 Call CaculatePageCount() If CLng(iPage)>CLng(iPageCount) And iPageCount>0 Then iPage = iPageCount End Property '//自定义查询语句 Public Property Let Sql(s) sSqlString = s End Property '//-------------------- 输出属性 --------------------//' '//取得当前条件下的记录数 Public Property Get RecordCount If isNull(iRecCount) Then CaculateRecCount() RecordCount = iRecCount End Property '//取得当前页码 Public Property Get Page Page = iPage End Property '//取得当前页码 Public Property Get AbsolutePage AbsolutePage = iPage End Property '//取得当前查询的条件 Public Property Get Condition If Len(sCondition)<1 Then makeCondition() Condition = sCondition 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 End Class </SCRIPT>

  推荐阅读

  Asp Access 创建静态文件/HTML

作用说明: 本代码实现从 Access 提取数据以创建 HTML 文件, 不建文件夹. 如果文件名与要创建的文件名相同, 则覆盖, 跟 SQL 的更新一样的原理. 待加功能: 删除与查找. 复制代码 代码如下:<% '''''''''''''''''''>>>详细阅读


本文标题:查询翻页优化第1/2页

地址:http://www.17bianji.com/kaifa2/ASP/33059.html

关键词: 探索发现

乐购科技部分新闻及文章转载自互联网,供读者交流和学习,若有涉及作者版权等问题请及时与我们联系,以便更正、删除或按规定办理。感谢所有提供资讯的网站,欢迎各类媒体与乐购科技进行文章共享合作。

网友点评
自媒体专栏

评论

热度

精彩导读
栏目ID=71的表不存在(操作类型=0)