| 以下是代码： <% Class dbClass ’------------------------------------------------------------------------- ’变量说明 ’conn-----------connection对象 ’strsql---------执行查询的语句 ’vTbName--------查询分页的表名 ’vPKey----------查询分页的表的主键 ’vPgFields------查询分页要显示的字段 ’vPgSize--------查询分页每页显示的记录数 ’vCurrPg--------查询分页显示的当前页 ’vConditions----查询分页的条件 ’vOrderBy-------查询分页的排序 ’------------------------------------------------------------------------- private conn,strsql,vTbName,vPKey,vPgFields,vPgSize,vCurrPg,vConditions,vOrderBy ’类的初始化 private Sub Class_Initialize() ’当是MS Sql数据库时设置以下两个变量 Webjx.Com ’dim dbServer ’数据库服务器的名称或ip地址 ’dim dbname ’数据库的名字 dim dbPath ’若是Access数据库，此处设置其路径 dim dbUser ’数据库的登录用户名 dim dbPass ’数据库的登录密码 dim connstr dbPath = "/testasp/data/data.mdb" ’设置数据库路径 dbUser = "admin" Webjx.Com dbPass = "123456" ’若是access,并且有密码 connstr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(dbPath) &_ ";User ID=" & dbUser & ";Password=;Jet OLEDB:Database Password=" & dbPass ’若是access，并且没有密码 ’connstr = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = " & Server.MapPath(dbPath) ’若是ms-sql数据库 ’connstr = "Provider = Sqloledb; User ID = " & dbUser & "; Password = " & dbPass &_ ’ "; Initial Catalog = " & dbname & "; Data Source = " & dbServer Webjx.Com on error resume next set conn=server.CreateObject("adodb.connection") conn.open connstr errMsg "连接数据库" End Sub ’类结束 Private Sub Class_terminate() conn.close 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)>0 then vConditions=" where "&Values else vConditions=" where 1=1 " end if end property ’------------------------------------------------------- ’设置查询的排序 public property let orderBy(ByVal Values) if Len(Values)>0 then vOrderBy=" order by "&Values else vOrderBy=Values end if end property ’------------------------------------------------------------- 网页教学网 ’得到记录总数 public property get vRsCount() if vCurrPg=1 then sqlc="select count("&vPKey&") as Idcount from "&vTbName&" "&vConditions set rsc=server.CreateObject("adodb.recordset") rsc.open sqlc,conn,0,1 RsNum=rsc("IdCount") rsc.close set rsc=nothing if RsNum>0 then response.Cookies("iRecord")=RsNum vRsCount=RsNum else vRsCount=0 end if else vRsCount=request.Cookies("iRecord") end if end property ’得到总页数 public property get vPgCount() 网页教学网 iRsCount2=vRsCount() if iRsCount2 mod vPgSize =0 then vPgCount=int(iRsCount2/vPgSize) else vPgCount=int(iRsCount2/vPgSize)+1 end if end property ’查询数据库 Public Function rsDB() on error resume next ’简单的查询出结果 ’ set rsDB = Server.CreateObject("ADODB.RecordSet") ’ rsDB.Open strsql,conn,1,3 Set rsDB=conn.Execute(strsql) errMsg "查询数据库" End Function ’添加，更新，删除数据库记录 public Function upDB() on error resume next conn.execute(strsql) errMsg "编辑数据库记录" end Function ’------------------------------------------------------------------------- Webjx.Com ’用来实现分页的记录集函数 public function pageRs() on error resume next dim startRs startRs=(vCurrPg-1)*vPgSize ’------------------------------------------------------------------------------------------- ’使用此语句的话要根据参数修改代码，具体的是若排序为asc则<改为>,min改为max ’ if startRs=0 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 " Webjx.Com ’ strsql=strsql&vTbName&" "&vConditions&" "&vOrderBy&") as idTable) "&vOrderBy ’ end if ’--------------------------------------------------------------- if startRs=0 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 Webjx.Com end if ’------------------------------------------------------------------- set pageRs=server.CreateObject("adodb.recordset") pageRs.open strsql,conn,0,1 errMsg "记录分页" end function ’------sql用存储过程分页------------------------------------------------------ public function sqlPage() on error resume next Set sqlPage=server.CreateObject("Adodb.RecordSet") Set Cm=Server.CreateObject("Adodb.Command") Cm.CommandType = 4 Cm.ActiveConnection = conn Cm.CommandText="sp_Util_Page" Cm.parameters(1) = vPgFields Cm.parameters(2) = vTbName Cm.parameters(3) = vConditions Cm.parameters(4) = vOrderBy Cm.parameters(5) = vPKey Cm.parameters(6) = vCurrPg Cm.parameters(7) = vPgSize Cm.parameters(8) = vRsCount() Cm.parameters(9) = "" sqlPage.CursorLocation = 3 Webjx.Com sqlPage.LockType = 1 sqlPage.Open Cm errMsg "记录分页" end function ’---------------------------------------------------------------------------- ’关闭记录集objRs ’---------------------------------------------------------------------------- Public Function cRs(ByVal ObjRs) ObjRs.close() Set ObjRs = Nothing End Function ’----------------------分页的页码导航--------------------------------------- public function pageNav() iRsCount=vRsCount()’总记录数 mypage=vCurrPg’当前页数 PgCount=vPgCount()’总页数 prePage=mypage-1 if prePage<1 then prePage=1 end if nextPage=mypage+1 if nextPage>PgCount then nextPage=PgCount end if pagestr="<div id=""fy""><span id=""rpc"">总共有"&iRsCount&"条记录 "&mypage&"/"&PgCount&"</span>" Webjx.Com pagestr=pagestr&"<a href=’?currpage=1’ class=’aW’>首页</a><a href=’?currpage="&prePage&"’ class=’aW’>前一页</a>" if (mypage-1) mod 4=0 then firstPage=mypage elseif int((mypage-1)/4)=0 then firstPage=1 else firstPage=int((mypage-1)/4)*4+1 end if endPage=firstPage+4 astr="" for i=firstPage to endPage astr=astr&"<a href=’?currpage="&i&"’" if Cstr(mypage)=Cstr(i) then astr=astr&" id=’currP’" end if astr=astr&">"&i&"</a>" if i>PgCount-1 then exit for next astr=astr&"<a href=’?currpage="&nextPage&"’ class=’aW’>后一页</a><a href=’?currpage="&PgCount&"’ class=’aW’>尾页</a></div>" pagestr=pagestr&astr pageNav=pagestr end function ’输出带分页功能的table Function showTb(ByVal TbTil) Webjx.Com set rsTb=pageRs()’若是存储过程就调用sqlPage() tbRs= rsTb.getrows() cRs(rsTb) iTblRow=Ubound(tbRs,2) iTblCol=Ubound(TbTil) tbStr="<table border=’0’ cellspacing=’0’ cellpadding=’0’><tbody>" for r1=0 to iTblCol tr1=tr1&"<td width=’"&split(TbTil(r1),"|")(1)&"’>"&split(TbTil(r1),"|")(0)&"</td>" next tr1="<tr>"&tr1&"</tr>" for ri=0 to iTblRow for ci=0 to iTblCol td=td&"<td width=’"&split(TbTil(ci),"|")(1)&"’>"&tbRs(ci,ri)&"</td>" next tr=tr&"<tr>"&td&"</tr>" td=null next TbTil=null tbRs=null response.Write(tbStr&tr1&tr&"<tr><td colspan=’"&iTblCol+1&"’>"&pageNav()&"</td></tr></tbody></table>") tbStr=null tr1=null tr=null End Function ’打印sql语句，以便语句有错误时检查 Public Sub prnSql() response.Write(strsql) End Sub ’------------------------------------------------------------------------- ’容错函数 ’------------------------------------------------------------------------- Private Function errMsg(errMsg) If Err.number<>0 Then ’出现问题可利用此处代码打印出描述信息，方便调试。可注释掉 response.Write(Cstr(Err.description)&" ") Err.Clear Response.Write "<font color=’#FF0000’>"&errMsg&"出错</font>" ’注释 Response.End() End If End Function ’------------------------------------------------------------------------- ’容错函数结束 ’------------------------------------------------------------------------- End Class %> |
