ASP+SQL+Server带条件查询的分页存储过程及其ASP调用实例

ASP+SQL Server 带条件查询的分页存储过程及其 ASP 调用实例 (该例子已经在环境 IIS+ASP+SQLServer 调试过可用，供 Web 初学者直接试用， 如有问题请及时留言指正) 1。

准备 Server SQL 建立数据库表 Diary
数据库表 Diary 建立代码： if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[xDiary]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[xDiary] GO CREATE TABLE [dbo].[xDiary] ( [DiaryID] [int] IDENTITY (1, 1) NOT NULL , [DiaryDate] [smalldatetime] NOT NULL , [DiaryName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL , [DiaryInfo] [ntext] COLLATE Chinese_PRC_CI_AS NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO
2.创建记录总集的存储过程 cn_RecordCount
存储过程 Cn_RecordCount 代码： CREATE PROCEDURE [dbo].[cn_RecordCount] --返回记录总集的存储过程 @TableName nvarchar(100), --数据库表名 @strWhere varchar(500), --筛选条件 @count int output --记录集总数 AS declare @sqlStr nvarchar(1000) if @strWhere!='' set @sqlStr=N'select @count=count(*) from ' +@TableName+' where 1=1 '+@strWhere else set @sqlStr=N'select @count=count(*) from '+@TableName exec sp_executesql @sqlstr,N'@count int output',@count output GO
3.分页的存储过程 cn_PageView


分页的存储过程 Cn_PageView： CREATE PROCEDURE [dbo].[cn_PageView] @tablename varchar(200), @strGetFields varchar(200), @PageIndex int, @PageSize int, @strWhere varchar(100), @strOrder varchar(100), @intOrder bit AS begin declare @strSql varchar(500) declare @strTemp varchar(100) declare @strOrders varchar(50) declare @table varchar(70) if @intOrder = 0 begin set @strTemp='>(select max' set @strOrders=' order by '+@strOrder+' asc ' end else begin set @strTemp='<(select min' set @strOrders=' order by '+@strOrder+' desc ' end if @PageIndex=1 begin if @strWhere='' begin set @strSql='select top '+str(@PageSize)+@strGetFields+' from '+@tablename+' ' +@strOrders end else begin set @strSql='select top '+str(@PageSize)+@strGetFields+' from '+@tablename+' where '+@strWhere+' '+@strOrders end end else begin


set @strSql = 'select top'+str(@pageSize)+' '+@strGetFields+' from '+@tablename+' where '+@strOrder+' '+@strTemp+' ('+@strOrder+')' +' from (select top '+str((@pageIndex-1)*@pageSize)+' '+@strGetFields+' from '+@tablename+ ' '+@strOrders+ ') as tempTable ) '+@strOrders --set @strSql='select top '+str(@PageSize)+' '+@strGetFields+' from '+@tablename+' where '+@strOrder+' '+@strTemp+' (' +@strOrder+') ' --+' from (select top '+str((@PageIndex-1)*@PageSize)+' '+@strGetFields+' from '+@tablename+' '+@strOrders+') as TempTable) '+@strOrders if @strWhere!=' ' begin set @strSql= 'select top '+str(@pageSize)+ ' '+@strGetFields+' from '+@tablename+ ' where '+@strOrder+ ' '+@strTemp+' ('+@strOrder+') ' +' from(select top '+str((@pageIndex-1)*@pageSize)+' '+@strGetFields+' from '+@tablename+' where '+@strWhere+' ' +@strOrders+') as tempTable) and '+@strWhere+' '+@strOrders end end exec(@strSql) end GO
4.ASP 页面调用存储分页实例
ASP 页面调用存储分页代码： <%Self=Request.ServerVariables("Script_Name") DataSource="你的服务器名" Catalog="你的数据库名" UID="sa" PWD="" Connstr="Provider=SQLOLEDB;Data Source="&DataSource&";Initial CataLog="&Catalog&";UID="&UID&";PWD="&PWD&";" set rs=Server.CreateObject("ADODB.Recordset") set Conn=Server.CreateObject("ADODB.Connection") Conn.Open ConnStr%> <%function MyLabel(En,CH)%><span style="cursor:hand;" title="<%=En%>"><%=CH%></span><%end function%> <table width="999" align="center" border="0" cellpadding="0" cellspacing="0"> <tr valign="top"> <td > <% dim


Kind,Key,TableName,strGetFields,PageNo,PageSize,strWhere,strWhere2,strOrder TableName="xDiary" strGetFields=" DiaryID,DiaryName,DiaryInfo,DiaryDate " strOrder="DiaryDate" PageNo=int(request("PageNo")) PageSize=10 Kind=Request("Kind") Key=Request("Key") if Key<>"" then strWhere=" and "&Kind&" like '%"&Key&"%' " strWhere2=" "&Kind&" like ''%"&Key&"%'' " else strWhere="" strWhere2="" end if Set Comm=Server.CreateObject("mand") Set Comm.ActiveConnection=Conn mandText="cn_RecordCount" mandType=4 Comm.Prepared=true Comm.Parameters.Append Comm.CreateParameter("@TableName",200,1,500,TableName) Comm.Parameters.Append Comm.CreateParameter("@strWhere",200,1,500,strWhere) Comm.Parameters.Append Comm.CreateParameter("@count",3,2) Comm.Execute RecordCount=Comm.Parameters("@count").value set Comm=nothing if RecordCount mod PageSize=0 then PageCount=RecordCount\PageSize else PageCount=RecordCount\PageSize+1 end if if PageNo="" or PageNo<=0 then PageNo=1 end if sql="exec cn_PageView '"&TableName&"','"&strGetFields&"',"&PageNo&","&PageSize&",'"&strWhere2&"','" &strOrder&"',1"


set rs=Server.Createobject("ADODB.Recordset") 'MySQL(sql) %> <table width="100%" align="center" border="0" cellpadding="1" cellspacing="0"style="border-bottom:0 double <%=CapColor%>"> <form method=post action="<%=Self%>" name="PageForm"> <tr > <td >&nbsp;</td> <td width="100"> <select name="Kind"> <option value="DiaryName"<%if Kind="DiaryName"then%>selected<%end if%>>By Topic</option> <option value="DiaryInfo"<%if Kind="DiaryInfo"then%>selected<%end if%>>By Content</option> </select></td> <td width="100"><input type="text" name="Key" value='<%=Key%>'style="border:1 solid <%=CapColor%>" size="50"></td> <td width="100" align="right"><input type="submit" value="查找发现(Discover)" style="border:1 solid <%=CapColor%>;background-color:<%=DataColor%>" >&nbsp;</td> </tr> </table> <table width="100%" align="center" border="1" cellpadding="3" cellspacing="0" bordercolorlight="<%=DataColor%>" bordercolordark="<%=BackColor%>" > <tr align="center" > <th nowrap >记事 ID</th> <th nowrap >记事日期</th> <th nowrap >记事主题</th> <th nowrap>记事内容</th> </tr> <%rs.open sql,conn,1,1 if not rs.eof then i=1 do while not rs.eof%> <tr align="center" height="32" bgcolor="<%'=AlterColor(i,BackColor)%>"> <td nowrap width="10"><%=rs("DiaryID")%></td> <td nowrap width="100"><%=rs("DiaryDate")%></td> <td ><%=rs("DiaryName")%></td> <td ><%=left(rs("DiaryInfo"),30)%></td> </tr> <%rs.MoveNext i=i+1


loop%> <table width="100%" border="0" cellspacing="2" cellpadding="3" style="border-left:5 double <%=CapColor%>;border-right:5 double <%=CapColor%>;border-top:1 double <%=DataColor%>;border-bottom:1 double <%=DataColor%>"> <tr align="right" bgcolor="<%=BackColor%>"> <td nowrap> Records=<%=MyLabel(CH,RecordCount)%>&nbsp;Pages=<%=MyLabel(CH,PageCo unt)%>&nbsp;PageNo=<%=MyLabel(CH,PageNo)%> &nbsp; <%if PageNo<>1 then%> <a href="Javascript:document.PageForm.PageNo.value=1;document.PageForm.submit ();"><%=MyLabel("首页","First")%></a> <%else %> <%=MyLabel("首页","First")%> <%end if%>&nbsp; <%if PageNo>1 then %> <a href="javascript:document.PageForm.PageNo.value--;document.PageForm.submit() ;"><%=MyLabel("上页","Prev")%></a> <%else%> <%=MyLabel("上页","Prev")%> <%end if%>&nbsp; <%if PageNo+1>PageCount then %> <%=MyLabel("下页","Next")%> <%else%> <a href="Javascript:document.PageForm.PageNo.value++;document.PageForm.submit ();"><%=MyLabel("下页","Next")%></a> <%end if %>&nbsp; <%if PageNo+1>PageCount then %> <%=MyLabel("末页","Last")%> <%else %> <a href="Javascript:document.PageForm.PageNo.value=<%=PageCount%>;document. PageForm.submit();"><%=MyLabel("末页","Last")%></a> <%end if%> </td> <td width="30"><input type="text" name="PageNo" value="<%=PageNo%>"


size="2" maxlength="3"></td> <td width="30"><input style="vertical-align:bottom;" type="image" src="/Image/Icon_Go.gif" onClick="JavaScript: PageForm.submit()"></td> </tr> </table> <%else response.write "<tr><td colspan=""8"">No Data！</td></tr>" end if%> </table> </td> </tr> </table>







。