2009年3月28日   星期六   风晴   愉悦   隐私度(0)   
ASP分页存储过程及ASP页面其调用实例!!-mmxhero|邱少日记|邱少网|邱少时空站ASP分页存储过程及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 >
<%'**************Active Information*******************%>
    <%
     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("ADODB.Command")
     Set Comm.ActiveConnection=Conn
     Comm.CommandText="cn_RecordCount"
     Comm.CommandType=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,PageCount)%>&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>
<%'**************Active Information*******************%>
</td>
</tr>
</table>

mmxhero 写于 上海市浦东区利津路385弄89号302室  

 

网友的评论意见或建议
还没有评论!
欢迎
参与
评论
您尚未登录网站,欢迎发表评论或留下意见建议,注册成为本站会员。
 
 
最近来访的网友

3.80.32.33

216.244.66.241

42.156.254.95

42.156.137.17

5.9.98.130

42.236.99.86

42.236.10.108

5.9.82.183

42.156.137.97

46.229.168.135

54.36.148.50

106.11.159.73

106.11.156.45

207.180.241.15

42.120.160.83

54.36.148.40

42.120.161.71

203.208.60.100

54.36.148.107

42.236.99.58
邱少网 ©1998-2019 神族网络信息分享无限公司|个人公益事业非经营网站
站长邮箱:mmxhero@163.com    站长QQ:156369596  
【最近访客=238 】  
mmxhero(M8,N0) PandaGuoxue(M1,N0) sgsddszy(M2,N0) sd17173(M1,N0) mashaojun(M1,N3) 321456987(M1,N0)
xutanyuan(M3,N0) outoTinfo(M1,N0) 100kvodka(M1,N0) kmmmmp(M1,N0)