程序代码
以下为引用的内容
<%
Class dbClass
变量说明
connconnection对象
strsql执行查询的语句
vTbName查询分页的表名
vPKey查询分页的表的主键
vPgFields查询分页要显示的字段
vPgSize查询分页每页显示的记录数
vCurrPg查询分页显示的当前页
vConditions查询分页的条件
vOrderBy查询分页的排序
private connstrsqlvTbNamevPKeyvPgFieldsvPgSizevCurrPgvConditionsvOrderBy
类的初始化
private Sub Class_Initialize()
当是MS Sql数据库时设置以下两个变量
dim dbServer 数据库服务器的名称或ip地址
dim dbname 数据库的名字
dim dbPath 若是Access数据库此处设置其路径
dim dbUser 数据库的登录用户名
dim dbPass 数据库的登录密码
dim connstr
dbPath = "/testasp/data/datamdb" 设置数据库路径
dbUser = "admin"
dbPass = ""
若是access并且有密码
connstr = "Provider=MicrosoftJetOLEDB;Data Source=" & ServerMapPath(dbPath) &_
";User ID=" & dbUser & ";Password=;Jet OLEDB:Database Password=" & dbPass
若是access并且没有密码
connstr = "Provider = MicrosoftJetOLEDB;Data Source = " & ServerMapPath(dbPath)
若是mssql数据库
connstr = "Provider = Sqloledb; User ID = " & dbUser & "; Password = " & dbPass &_
"; Initial Catalog = " & dbname & "; Data Source = " & dbServer
on error resume next
set conn=serverCreateObject("adodbconnection")
connopen connstr
errMsg "连接数据库"
End Sub
类结束
Private Sub Class_terminate()
connclose
set conn=nothing
End Sub
给类的变量设置值
设置sql语句
Public Property Let sqlStr(Byval Values)
strsql=Values
End Property
设置查询分页的表名
public property let tbName(Byval Values)
vTbName=Values
end property
设置查询分页的表的主键
public property let pKey(ByVal Values)
vPKey=Values
end property
设置显示的字段
public property let pgFields(ByVal Values)
vPgFields=Values
end property
设置每页显示的记录数
public property let pgSize(ByVal Values)
vPgSize=Values
end property
设置当前显示的页数
public property let currPg(ByVal Values)
vCurrPg=Values
end property
设置查询的条件
public property let conditions(ByVal Values)
if Len(Values)> then
vConditions=" where "&Values
else
vConditions=" where = "
end if
end property
设置查询的排序
public property let orderBy(ByVal Values)
if Len(Values)> then
vOrderBy=" order by "&Values
else
vOrderBy=Values
end if
end property
得到记录总数
public property get vRsCount()
if vCurrPg= then
sqlc="select count("&vPKey&") as Idcount from "&vTbName&" "&vConditions
set rsc=serverCreateObject("adodbrecordset")
rscopen sqlcconn
RsNum=rsc("IdCount")
rscclose
set rsc=nothing
if RsNum> then
responseCookies("iRecord")=RsNum
vRsCount=RsNum
else
vRsCount=
end if
else
vRsCount=requestCookies("iRecord")
end if
end property
得到总页数
public property get vPgCount()
iRsCount=vRsCount()
if iRsCount mod vPgSize = then
vPgCount=int(iRsCount/vPgSize)
else
vPgCount=int(iRsCount/vPgSize)+
end if
end property
查询数据库
Public Function rsDB()
on error resume next
简单的查询出结果
set rsDB = ServerCreateObject("ADODBRecordSet")
rsDBOpen strsqlconn
Set rsDB=connExecute(strsql)
errMsg "查询数据库"
End Function
添加更新删除数据库记录
public Function upDB()
on error resume next
connexecute(strsql)
errMsg "编辑数据库记录"
end Function
用来实现分页的记录集函数
public function pageRs()
on error resume next
dim startRs
startRs=(vCurrPg)*vPgSize
使用此语句的话要根据参数修改代码具体的是若排序为asc则<改为>min改为max
if startRs= then
strsql="select top "&vPgSize&" "&vPgFields&" from "&vTbName&" "&vConditions&" "&vOrderBy
else
strsql="select top "&vPgSize&" "&vPgFields&" from "&vTbName&" "&vConditions&" and "&vPKey&" < "
strsql=strsql&"(select min("&vPKey&") from (select top "&startRs&" "&vPKey&" from "
strsql=strsql&vTbName&" "&vConditions&" "&vOrderBy&") as idTable) "&vOrderBy
end if
if startRs= then
strsql="select top "&vPgSize&" "&vPgFields&" from "&vTbName&" "&vConditions&" "&vOrderBy
else
strsql="select top "&vPgSize&" "&vPgFields&" from "&vTbName&" "&vConditions&" and "&vPKey&" not "
strsql=strsql&"in (select top "&startRs&" "&vPKey&" from "&vTbName&" "&vConditions&" "&vOrderBy
strsql=strsql&") "&vOrderBy
end if
set pageRs=serverCreateObject("adodbrecordset")
pageRsopen strsqlconn
errMsg "记录分页"
end function
sql用存储过程分页
public function sqlPage()
on error resume next
Set sqlPage=serverCreateObject("AdodbRecordSet")
Set Cm=ServerCreateObject("AdodbCommand")
CmCommandType =
CmActiveConnection = conn
CmCommandText="sp_Util_Page"
Cmparameters() = vPgFields
Cmparameters() = vTbName
Cmparameters() = vConditions
Cmparameters() = vOrderBy
Cmparameters() = vPKey
Cmparameters() = vCurrPg
Cmparameters() = vPgSize
Cmparameters() = vRsCount()
Cmparameters() = ""
sqlPageCursorLocation =
sqlPageLockType =
sqlPageOpen Cm
errMsg "记录分页"
end function
关闭记录集objRs
Public Function cRs(ByVal ObjRs)
ObjRsclose()
Set ObjRs = Nothing
End Function
分页的页码导航
public function pageNav()
iRsCount=vRsCount()总记录数
mypage=vCurrPg当前页数
PgCount=vPgCount()总页数
prePage=mypage
if prePage< then
prePage=
end if
nextPage=mypage+
if nextPage>PgCount then
nextPage=PgCount
end if
pagestr="<div id=""fy""><span id=""rpc"">总共有"&iRsCount&"条记录 "&mypage&"/"&PgCount&"</span>"
pagestr=pagestr&"<a class=aW>首页</a><a ?currpage="&prePage&" class=aW>前一页</a>"
if (mypage) mod = then
firstPage=mypage
elseif int((mypage)/)= then
firstPage=
else
firstPage=int((mypage)/)*+
end if
endPage=firstPage+
astr=""
for i=firstPage to endPage
astr=astr&"<a ?currpage="&i&""
if Cstr(mypage)=Cstr(i) then
astr=astr&" id=currP"
end if
astr=astr&">"&i&"</a>"
if i>PgCount then exit for
next
astr=astr&"<a ?currpage="&nextPage&" class=aW>后一页</a><a ?currpage="&PgCount&" class=aW>尾页</a></div>"
pagestr=pagestr&astr
pageNav=pagestr
end function
输出带分页功能的table
Function showTb(ByVal TbTil)
set rsTb=pageRs()若是存储过程就调用sqlPage()
tbRs= rsTbgetrows()
cRs(rsTb)
iTblRow=Ubound(tbRs)
iTblCol=Ubound(TbTil)
tbStr="<table border= cellspacing= cellpadding=><tbody>"
for r= to iTblCol
tr=tr&"<td width="&split(TbTil(r)"|")()&">"&split(TbTil(r)"|")()&"</td>"
next
tr="<tr>"&tr&"</tr>"
for ri= to iTblRow
for ci= to iTblCol
td=td&"<td width="&split(TbTil(ci)"|")()&">"&tbRs(ciri)&"</td>"
next
tr=tr&"<tr>"&td&"</tr>"
td=null
next
TbTil=null
tbRs=null
responseWrite(tbStr&tr&tr&"<tr><td colspan="&iTblCol+&">"&pageNav()&"</td></tr></tbody></table>")
tbStr=null
tr=null
tr=null
End Function
打印sql语句以便语句有错误时检查
Public Sub prnSql()
responseWrite(strsql)
End Sub
容错函数
Private Function errMsg(errMsg)
If Errnumber<> Then
出现问题可利用此处代码打印出描述信息方便调试可注释掉
responseWrite(Cstr(Errdescription)&"<br>")
ErrClear
ResponseWrite "<font color=#FF>"&errMsg&"出错</font>" 注释
ResponseEnd()
End If
End Function
容错函数结束
End Class
%>
