查询翻页优化第12页

　　复制代码 代码如下:
<SCRIPTlanguage=VBSCRIPTRUNAT=SERVER>

//----COCOON_自定义类_翻页优化代码----//
Classcc_db_Pager
--------------------------------
COCOONDb_Pager类(Ver:1.02)
作者
请保留此信息，谢谢。

2003/06修正一个错误，描述：如果ID不唯一，将产生多条重复记录的错误。
2003.5
--------------------------------

//--------------------定义变量--------------------//
PrivatesTableName//表名
PrivatesSqlString//自定义Sql语句
PrivateaCondition()//查询条件(数组)
PrivatesCondition//查询条件(字符串)
PrivateiPage//当前页码
PrivateiPageSize//每页记录数
PrivateiPageCount//总页数
PrivateiRecCount//当前查询条件下的记录数
PrivateiTotalRecCount//总记录数
PrivatesFields//输出的字段名
PrivatesOrderBy//排序字符串
PrivatesSql//当前的查询语句
PrivatesPkey//主键
PrivateoConn//连接对象
PrivateiDefPageSize//默认每页显示的记录数
PrivatesProjectName/项目名
PrivatesVersion/版本号
PrivatebShowError//是否显示错误信息

//--------------------事件、方法--------------------//
//类初始化事件
PrivateSubClass_Initialize()
ReDimaCondition(-1)
iPage=1
iRecCount=Null
iTotalRecCount=Null
iPageCount=Null
bShowError=True
iPageSize=10
sFields=*
sPkey=ID
sCondition=
sOrderBy=
sSqlString=
sProjectName=COCOON类系列数据库翻页优化
sVersion=1.02
EndSub

//类结束事件
PrivateSubClass_Terminate()
SetoConn=Nothing
EndSub

//错误处理
PublicSubdoError(s)
DimsTmp
sTmp=CLng(Rnd()*100)
Response.write(<DIVSTYLE=WIDTH:760;font-size:9pt;cursor:hand>)
Response.write(<LABELONCLICK=ERRORDIV&sTmp&.style.display=(ERRORDIV&sTmp&.style.display==?none:)>)
Response.write(<SPANSTYLE=BACKGROUND-COLOR:#CCCC00;COLOR:WHITE;>〖CC_db_Pager提示信息〗</SPAN><BR></LABEL>)
Response.write(<DIVID=ERRORDIV&sTmp&STYLE=DISPLAY:NONE;WIDTH:100%;)
Response.write(BORDER:1PXSOLID#CCCC00;PADDING:5;overflow:hidden;text-overflow:ellipsis;><NOBR>)
Response.write(<SPANSTYLE=COLOR:RED>Description</SPAN>:&s&<BR>)
Response.write(<SPANSTYLE=COLOR:RED>Provider</SPAN>:&sProjectName)
Response.write(<SPANSTYLE=COLOR:RED>Version</SPAN>:&sVersion&<BR>)
Response.write(</NOBR></DIV></DIV><BR>)
EndSub

//产生分页的SQL语句
PublicFunctiongetSql()
DimiStart,iEnd
CallmakeCondition()
iStart=(iPage-1)*iPageSize
iEnd=iStart+iPageSize
getSql=SELECTDISTINCT&sFields&FROM[&sTableName&]_
&WHERE[&sPKey&]NOTIN(_
&SELECTTOP&iStart&[&sPKey&]FROM[&sTableName&]&sCondition&&sOrderBy&_
&)_
&AND[&sPKey&]IN(_
&SELECTTOP&iEnd&[&sPKey&]FROM[&sTableName&]&sCondition&&sOrderBy&_
&)_
&&sOrderBy&
EndFunction

//产生条件字符串
PrivateSubmakeCondition()
IfUBound(aCondition)>=0Then
sCondition=WHERE&Join(aCondition,AND)
EndIf
EndSub

//计算总记录数（带条件）
PrivateSubCaculateRecCount()
OnErrorResumeNext
DimoRs
CallmakeCondition()
SetoRs=oConn.Execute(SELECTCOUNT([&sPKey&])FROM[&sTableName&]&sCondition)
IfErrThen
doErrorErr.Description
Response.End()
EndIf
iRecCount=oRs.Fields.Item(0).Value
SetoRs=Nothing
EndSub

//计算总记录数（不带条件）
PrivateSubCaculateTotalRecCount()
OnErrorResumeNext
DimoRs
SetoRs=oConn.Execute(SELECTCOUNT([&sPKey&])FROM[&sTableName&])
IfErrThen
doErrorErr.Description
Response.End()
EndIf
iTotalRecCount=oRs.Fields.Item(0).Value
SetoRs=Nothing
EndSub

//计算页数
PrivateSubCaculatePageCount()
IfisNull(iRecCount)ThenCaculateRecCount()
IfiRecCount=0TheniPageCount=0:ExitSub
iPageCount=Abs(Int(0-(iRecCount/iPageSize)))
EndSub

//增加条件
PublicSubAddCondition(s)
IfLen(s)<0ThenExitSub
ReDimPreserveaCondition(UBound(aCondition)+1)
aCondition(UBound(aCondition))=s
EndSub

//版本信息
PublicFunctionInformation()
EndFunction


//--------------------输入属性--------------------//
//定义连接对象
PublicPropertySetActiveConnection(o)
SetoConn=o
EndProperty

//定义查询表名
PublicPropertyLetTableName(s)
sTableName=s
EndProperty

//定义需要输出的字段名
PublicPropertyLetFields(s)
sFields=s
EndProperty

//定义主键
PublicPropertyLetPkey(s)
sPkey=s
EndProperty

//定义排序规则
PublicPropertyLetOrderBy(s)
sOrderBy=ORDERBY&s&
EndProperty

//定义每页的记录条数
PublicPropertyLetPageSize(s)
iPageSize=s
IfNotisNumeric(iPageSize)TheniPageSize=iDefaultPageSize
IfCLng(iPageSize)<1TheniPageSize=iDefaultPageSize
EndProperty

//定义当前页码
PublicPropertyLetPage(s)
iPage=s
IfNotisNumeric(iPage)TheniPage=1
IfCLng(iPage)<1TheniPage=1
CallCaculatePageCount()
IfCLng(iPage)>CLng(iPageCount)AndiPageCount>0TheniPage=iPageCount
EndProperty

//自定义查询语句
PublicPropertyLetSql(s)
sSqlString=s
EndProperty

//--------------------输出属性--------------------//
//取得当前条件下的记录数
PublicPropertyGetRecordCount
IfisNull(iRecCount)ThenCaculateRecCount()
RecordCount=iRecCount
EndProperty

//取得当前页码
PublicPropertyGetPage
Page=iPage
EndProperty

//取得当前页码
PublicPropertyGetAbsolutePage
AbsolutePage=iPage
EndProperty

//取得当前查询的条件
PublicPropertyGetCondition
IfLen(sCondition)<1ThenmakeCondition()
Condition=sCondition
EndProperty

//取得总的记录数
PublicPropertyGetTotalRecordCount
IfisNull(iTotalRecCount)ThenCaculateTotalRecCount()
TotalRecordCount=iTotalRecCount
EndProperty

//取得总页数
PublicPropertyGetPageCount
IfisNull(iPageCount)ThenCaculatePageCount()
PageCount=iPageCount
EndProperty

//得到分页后的记录集
PublicPropertyGetRecordset
OnErrorResumeNext
sSql=getSql()
SetRecordset=oConn.Execute(sSql)
IfErrThen
IfbShowErrorThendoErrorErr.Description
IfLen(sSqlString)>0Then
SetRecordset=oConn.Execute(sSqlString)
IfErrThen
doErrorErr.Description
Response.End()
EndIf
Else
doErrorErr.Description
EndIf
EndIf
Err.Clear()
EndProperty

//版本信息
PublicPropertyGetVersion
Version=sVersion
EndProperty

EndClass

</SCRIPT>

12下一页阅读全文