sqlserver 分页代码

来源：互联网发布：qq整人软件编辑：程序博客网时间：2024/06/08 09:04

SQL存储过程: 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

page40.inc

<%dim text,checkpagetext="0123456789"for i=1 to len(request("page")) checkpage=instr(1,text,mid(request("page"),i,1)) if checkpage=0 then exit for end ifnextIf checkpage<>0 then If NOT IsEmpty(request("page")) Then m_page=Cint(request("page")) If m_page < 1 Then m_page = 1 Else m_page= 1 End IfElse m_page=1End ifm_pagesize=40'每页的条数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.Executeset rs = rs.NextRecordSetm_pageCount = cmd.Parameters("@pageCount").value m_recordCount = cmd.Parameters("@recordCount").valueif m_pageCount = 0 then m_pageCount = 1if m_page>m_pageCount then response.Redirect("ProList.asp?page="&m_pageCount&"") end ifset rs = cmd.Execute%>

function.asp

<%'Designed By L.C.H'前台调用东东%><%'L.C.H-001建立连接数据库记录Function RsName(Rs_Name,Sql,A,B)Set Rs_Name=Server.CreateObject("Adodb.RecordSet")Rs_Name.open Sql,conn,A,BEnd Function'************************************************************************'L.C.H-002释放记录集Sub CloseRs()rs.closeset rs=nothingEnd Sub'************************************************************************'L.C.H-003释放connSub CloseConn()conn.closeset conn=nothingEnd Sub'************************************************************************'L.C.H-004显示错误提示且返回上一页Function ShowError(a)response.write("<mce:script language=javascript></mce:script>")response.end()End Function'************************************************************************'L.C.H-005显示错误提示且重新载入另一页Function ShowErrorTo(a,b)response.write("<mce:script language=javascript></mce:script>")response.end()End Function'************************************************************************'L.C.H-006显示错误提示且关闭该窗口Function ShowErrorClose(a)response.write("<mce:script language=javascript></mce:script>")response.end()End Function'************************************************************************'L.C.H-007分页调用Function PageGo(sql)response.write "<table width='100%' border='1' align='center' cellPadding='0' cellSpacing='0' bordercolor='#71CC71' bgcolor='#E9F8E9' class='12'>"response.write "<form method=Post action='"&Myself&"?"&sql&"'>"response.write "<tr><td height='24' bordercolor='#E9F8E9'><div align='center'>" If currentpage > 1 Then response.write "<a href=""&Myself&"?"&sql&"page="+cstr(1)+"" mce_href=""&Myself&"?"&sql&"page="+cstr(1)+"">首页</a>-" Response.write "<a href=""&Myself&"?"&sql&"page="+Cstr(currentpage-1)+"" mce_href=""&Myself&"?"&sql&"page="+Cstr(currentpage-1)+"">前页</a>-" Else Response.write "首页-" Response.write "前页-" End if If currentpage < rs.PageCount Then Response.write "<a href=""&Myself&"?"&sql&"page="+Cstr(currentPage+1)+"" mce_href=""&Myself&"?"&sql&"page="+Cstr(currentPage+1)+"">后页</a>-" Response.write "<a href=""&Myself&"?"&sql&"page="+Cstr(rs.PageCount)+"" mce_href=""&Myself&"?"&sql&"page="+Cstr(rs.PageCount)+"">尾页</a>  " Else Response.write "后页-" Response.write "尾页  " End ifResponse.write "页次:" & "" & Cstr(CurrentPage) & "" & "/" & Cstr(rs.pagecount) & " "Response.write "" & Cstr(MaxPerPage) & "" & "条/页&nbsp" & "共" & "" & Cstr(rs.RecordCount) & "" & "条信息  "response.write "转到：<input type='text' name='page' size=4 maxlength=4 class='Input' value="&Currentpage&"> "response.write "<input type='submit' value='转到' name='cndok' class='button'>  " response.write "</div></td></tr></form></table>"End Function'L.C.H-007-1分页调用Function MyPage(sql)%><table width='100%' border='0' align='center' cellPadding='0' cellSpacing='0' bgcolor='#efefef'><form name="form3" action="<%=Myself%>?<%=sql%>" method="post"> <tr> <td height="24" align="center">共<%=m_pageCount%>页这是第[<%=m_page%>]页总共[<%=m_recordCount%>]条信息 <%if m_page<>1 then%> <A HREF="<%=Myself%" mce_HREF="<%=Myself%">?<%=sql%>Page=1>首页</A> <A HREF="<%=Myself%" mce_HREF="<%=Myself%">?<%=sql%>Page=<%=(m_Page-1)%>>上一页</a> <%else%> 首页上一页 <%end if If m_page <> m_pageCount Then%> <A HREF="<%=Myself%" mce_HREF="<%=Myself%">?<%=sql%>Page=<%=(m_Page+1)%>>下一页</A> <A HREF="<%=Myself%" mce_HREF="<%=Myself%">?<%=sql%>Page=<%=m_pageCount%>>尾页</A> <%else%> 下一页尾页 <%end if%> 　转到第 <input name="page" type="text" class="input" id="page" value="<%=m_page%>" size="3"> 页 <input type="submit" name="Submit" value=" GO " class="button"> </td> </tr></form> </table><%End Function'************************************************************************'L.C.H-008禁止外部提交Sub Remote()if not (instr(1,Request.ServerVariables("http_Referer"),Request.ServerVariables ("SERVER_NAME"),1)=8) thenCall ShowError("参数错误！")end if end Sub'************************************************************************'L.C.H-010是否为数字Function IsDigit(a)if isnumeric(a)=false thenresponse.redirect("index.asp")Call ShowError("参数错误！")end ifend Function'************************************************************************'L.C.H-011传递ID的有效性Function Isid(a)if a="" or isnumeric(a)=false thenresponse.redirect("index.asp")Call ShowError("参数错误！")end ifend Function'************************************************************************'L.C.H-012关闭当前弹出窗口，并且刷新父窗口Sub Refresh()response.write("<mce:script language='javascript'></mce:script>")response.end()end sub '************************************************************************'L.C.H-013打开弹出窗口，并且刷新父窗口Sub Refresh2()response.write("<mce:script language='javascript'></mce:script>")end sub '************************************************************************'取得大类名称Function BclassName(a)set tempname=conn.execute("select bclass from [wksc_bclass] where id="&a&"") response.write(tempname(0)) tempname.close:set tempname=nothing end Function '************************************************************************'取得小类名称Function SclassName(a)set tempname=conn.execute("select sclass from [wksc_sclass] where id="&a&"") response.write(tempname(0)) tempname.close:set tempname=nothing end Function '************************************************************************function HTMLEncode(fString)fString = replace(fString, ">", ">")fString = replace(fString, "<", "<")fString = Replace(fString, CHR(32), " ")fString = Replace(fString, CHR(13), "")fString = Replace(fString, CHR(10) & CHR(10), "")fString = Replace(fString, CHR(10), " ")fString = Replace(fString,"'", "''")HTMLEncode = fStringend functionfunction HTMLEncodeGo(fString)fString = replace(fString, ">", ">")fString = replace(fString, "<", "<")fString = Replace(fString, " ",CHR(32))fString = Replace(fString, " ",CHR(13))fString = Replace(fString, " ",CHR(13))fString = Replace(fString, " ",CHR(13))fString = Replace(fString, " ",CHR(13))HTMLEncodeGo = fStringend function'************************************************************************'读取省份名称Function MyProvince(a)select case acase 1response.write("浙江")case 2response.write("天津")case 3response.write("上海")case 4response.write("重庆")case 5response.write("北京")case 6response.write("广东")case 7response.write("江苏")case 8response.write("福建")case 9response.write("湖南")case 10response.write("湖北")case 11response.write("山东")case 12response.write("辽宁")case 13response.write("吉林")case 14response.write("云南")case 15response.write("四川")case 16response.write("安徽")case 17response.write("江西")case 18response.write("黑龙江")case 19response.write("河北")case 20response.write("陕西")case 21response.write("海南")case 22response.write("河南")case 23response.write("山西")case 24response.write("内蒙古")case 25response.write("广西")case 26response.write("贵州")case 27response.write("宁夏")case 28response.write("青海")case 29response.write("新疆")case 30response.write("西藏")case 31response.write("甘肃")case 32response.write("台湾")case 33response.write("香港")case 34response.write("澳门")case 35response.write("国外")case 36response.write("其他")end selectend function'L.C.H-025随机产生订单号Function orderid(digits)dim char_array(16)char_array(0) = "0"char_array(1) = "1"char_array(2) = "2"char_array(3) = "3"char_array(4) = "4"char_array(5) = "5"char_array(6) = "6"char_array(7) = "7"char_array(8) = "8"char_array(9) = "9"randomizedo while len(output) < digitsnum = char_array(Int((10) * Rnd + 0))output = output + numloophead = now()head = replace(head,"-","")head = replace(head," ","") head = replace(head,":","")head = replace(head,"PM","")head = replace(head,"AM","")head = replace(head,"上午","")head = replace(head,"下午","")head = mid(head,3,10)orderid = head + outputEnd Function'************************************************************************Function MyStatusName(a)select case acase 1response.Write("新订单，等待客服处理")case 2response.Write("会员自行取消")case 3response.Write("无效单，已取消")case 4response.Write("已确认订单，请支付货款")case 5response.Write("已收到货款，交至仓库安排")case 6response.Write("已从仓库发出，请耐心等待")case 7response.Write("交易完成，合作愉快")end selectend function'************************************************************************Function MyPowerNameGo(a)set temp1=conn.execute("select powername from [wksc_userpower] where powerlevel="&a&" and agentid="&session("wksc_agentid")&"")response.Write(temp1(0))temp1.close:set temp1=nothingend function'************************************************************************Function MyRealNameGo(a)if len(a)=2 thenresponse.Write(a)elseif len(a)>=3 thenresponse.Write(right(a,2))elseresponse.Write(a)end ifend function%>