asp与存储过程的分页程序

最新推荐文章于 2009-04-16 13:49:00 发布

chenguang79

最新推荐文章于 2009-04-16 13:49:00 发布

阅读量1.4k

点赞数

分类专栏： asp 文章标签： asp 存储 border sql sql server generator

本文链接：https://blog.csdn.net/chenguang79/article/details/942141

版权

asp 专栏收录该内容

19 篇文章 0 订阅

订阅专栏

存储过程如下

CREATE procedure p_splitpage
@sql nvarchar(4000), --要执行的sql语句
@page int=1,    --要显示的页码
@pageSize int,  --每页的大小
@pageCount int=0 out, --总页数
@recordCount int=0 out --总记录数
as
set nocount on
declare @p1 int
exec sp_cursoropen @p1 output,@sql,@scrollopt=1,@ccopt=1,@rowcount=@pagecount output
set @recordCount = @pageCount
select @pagecount=ceiling(1.0*@pagecount/@pagesize)
,@page=(@page-1)*@pagesize+1
exec sp_cursorfetch @p1,16,@page,@pagesize
exec sp_cursorclose @p1
GO

asp页面调用如下：

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML>
<HEAD>
<TITLE> New Document </TITLE>
<META NAME="Generator" CONTENT="EditPlus">
<META NAME="Author" CONTENT="">
<META NAME="Keywords" CONTENT="">
<META NAME="Description" CONTENT="">
</HEAD>

<BODY>
<%
set conn=server.createobject("adodb.connection")
conn.open "driver={sql server};server=(local);uid=sa;pwd=chen123;database=szfq"

'Set rs1 = server.CreateObject("adodb.recordset")

    sql="select * from t_user order by id desc"
if request("page")="" or request("page")=0 or IsNumeric(request("page"))=False Or request("page")<0 then
  m_page=1
else
  m_page=cint(request("page"))
end if
m_pagesize=10'每页的条数
set cmd = server.CreateObject("adodb.command")
    cmd.ActiveConnection = conn
    cmd.CommandType = 4
    cmd.CommandText = "p_SplitPage"
    cmd.Parameters.Append cmd.CreateParameter("@sql",8,1, 4000, sql)
    cmd.Parameters.Append cmd.CreateParameter("@page",4,1, 4, m_page)
    cmd.Parameters.Append cmd.CreateParameter("@pageSize",4,1, 4, m_pageSize)
    cmd.Parameters.Append cmd.CreateParameter("@pageCount",4,2, 4, m_pageCount)
    cmd.Parameters.Append cmd.CreateParameter("@recordCount",4,2, 4, m_recordCount)
    set rs = cmd.Execute
set rs = rs.NextRecordSet
m_pageCount = cmd.Parameters("@pageCount").value
    m_recordCount = cmd.Parameters("@recordCount").value
if m_pageCount = 0 then m_pageCount = 1
if m_page>m_pageCount then
  response.Redirect("bureauser_result.asp?page="&m_pageCount&"&lxsuser_name="&lxsuser_name)
end if
set rs = cmd.Execute
%>
<table width="100%" border="0" cellspacing="1" cellpadding="0">
<%
     Do While Not rs.eof And m_pagesize > 0
%>
<tr><td><%=rs("nickname")%></td></tr>
<%
    rs.movenext
m_pagesize = m_pagesize - 1
loop
%>
                            <tr>
                              <td width="47%" align="right">查询结果共 <font color="#FF0000"><%=m_pageCount%></font>
                                页这是第 <font color="#FF0000"><%=m_page%></font> 页 </td>
                              <td width="53%" align="right">
<table width="95%" border="0" cellspacing="0" cellpadding="0">
                                  <tr>
                                    <td width="10" align="right" ><img src="feedback_images/ll.gif" width="8" height="21"></td>
                                    <td width="475" background="feedback_images/m.gif"><table width="100%" border="0" cellspacing="0" cellpadding="0">
                                        <tr>
           <%
            if m_page<>1 then
           %>
                                          <td width="23%"><img src="images/first.gif" width="8" height="8"> <A HREF=<%=Myself%>?Page=1>首页</A></td>
                <td width="31%"><img src="images/pre.gif" width="8" height="8"> <A HREF=<%=Myself%>?Page=<%=(m_Page-1)%>>上一页</a></td>
            <%
            end if
           If m_page <> m_pageCount Then
            %>
                                          <td width="24%">
            <%
               If m_page = 1 Then
            %>
             <a href="<%=Myself%>?Page=<%=m_Page%>"><%=m_Page%></a>  <a href="<%=Myself%>?Page=<%=m_Page+1%>"><%=m_Page+1%></a>  <a href="<%=Myself%>?Page=<%=m_Page+2%>"><%=m_Page+2%></a>  <a href="<%=Myself%>?Page=<%=m_Page+3%>"><%=m_Page+3%></a>  <a href="<%=Myself%>?Page=<%=m_Page+4%>"><%=m_Page+4%></a>
            <%
               End if
            %>
            <%
               If m_page = 2 then
            %>
                                            <a href="<%=Myself%>?Page=<%=m_Page-1%>"><%=m_Page-1%></a>  <a href="<%=Myself%>?Page=<%=m_Page%>"><%=m_Page%></a>  <a href="<%=Myself%>?Page=<%=m_Page+1%>"><%=m_Page+1%></a>  <a href="<%=Myself%>?Page=<%=m_Page+2%>"><%=m_Page+2%></a>  <a href="<%=Myself%>?Page=<%=m_Page+3%>"><%=m_Page+3%></a>
                                          <%
               End if
            %>

                                          <%
              If m_page <> 1 And m_page<>2 then
            %>
            <a href="<%=Myself%>?Page=<%=m_Page-2%>"><%=m_Page-2%></a> <a href="<%=Myself%>?Page=<%=m_Page-1%>"><%=m_Page-1%></a> <a href="<%=Myself%>?Page=<%=m_Page%>"><%=m_Page%></a> <a href="<%=Myself%>?Page=<%=m_Page+1%>"><%=m_Page+1%></a> <a href="<%=Myself%>?Page=<%=m_Page+2%>"><%=m_Page+2%></a> 
            <%End if%>
            <A HREF=<%=Myself%>?Page=<%=(m_Page+1)%>>下一页</A></td>
                <td width="22%"><img src="images/last.gif" width="8" height="8"><A HREF=<%=Myself%>?Page=<%=m_pageCount%>>尾页</A></td>
            <%end if%>
                                        </tr>
          <%'end if%>
                                      </table></td>
                                    <td width="11"><img src="feedback_images/rr.gif" width="10" height="21"></td>
                                  </tr>
                                </table>
                              </td>
                            </tr>
                          </table>

</BODY>
</HTML>

chenguang79

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
asp与存储过程的分页程序

存储过程如下CREATE procedure p_splitpage @sql nvarchar(4000), --要执行的sql语句@page int=1, --要显示的页码@pageSize int, --每页的大小@pageCount int=0 out, --总页数@recordCount int=0 out --总记录数asset nocount ondeclare @p
复制链接

扫一扫