sqlserver 分页代码

发布时间：2020-12-12 15:36:19 所属栏目：MsSql教程来源：网络整理

导读：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

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,checkpage text="0123456789" for i=1 to len(request("page")) checkpage=instr(1,text,mid(request("page"),i,1)) if checkpage=0 then exit for end if next If 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 If Else m_page=1 End if m_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,m_page) cmd.Parameters.Append cmd.CreateParameter("@pageSize",m_pageSize) cmd.Parameters.Append cmd.CreateParameter("@pageCount",2,m_pageCount) cmd.Parameters.Append cmd.CreateParameter("@recordCount",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("ProList.asp?page="&m_pageCount&"") end if set 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,B End Function '************************************************************************ 'L.C.H-002释放记录集 Sub CloseRs() rs.close set rs=nothing End Sub '************************************************************************ 'L.C.H-003释放conn Sub CloseConn() conn.close set conn=nothing End 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 if Response.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) then Call ShowError("参数错误！") end if end Sub '************************************************************************ 'L.C.H-010是否为数字 Function IsDigit(a) if isnumeric(a)=false then response.redirect("index.asp") Call ShowError("参数错误！") end if end Function '************************************************************************ 'L.C.H-011传递ID的有效性 Function Isid(a) if a="" or isnumeric(a)=false then response.redirect("index.asp") Call ShowError("参数错误！") end if end 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 = fString end function function HTMLEncodeGo(fString) fString = replace(fString,">",">") fString = replace(fString,"<","<") fString = Replace(fString," ",CHR(32)) fString = Replace(fString," ",CHR(13)) fString = Replace(fString," "," "," ",CHR(13)) HTMLEncodeGo = fString end function '************************************************************************ '读取省份名称 Function MyProvince(a) select case a case 1 response.write("浙江") case 2 response.write("天津") case 3 response.write("上海") case 4 response.write("重庆") case 5 response.write("北京") case 6 response.write("广东") case 7 response.write("江苏") case 8 response.write("福建") case 9 response.write("湖南") case 10 response.write("湖北") case 11 response.write("山东") case 12 response.write("辽宁") case 13 response.write("吉林") case 14 response.write("云南") case 15 response.write("四川") case 16 response.write("安徽") case 17 response.write("江西") case 18 response.write("黑龙江") case 19 response.write("河北") case 20 response.write("陕西") case 21 response.write("海南") case 22 response.write("河南") case 23 response.write("山西") case 24 response.write("内蒙古") case 25 response.write("广西") case 26 response.write("贵州") case 27 response.write("宁夏") case 28 response.write("青海") case 29 response.write("新疆") case 30 response.write("西藏") case 31 response.write("甘肃") case 32 response.write("台湾") case 33 response.write("香港") case 34 response.write("澳门") case 35 response.write("国外") case 36 response.write("其他") end select end 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" randomize do while len(output) < digits num = char_array(Int((10) * Rnd + 0)) output = output + num loop head = now() head = replace(head,"-","") head = replace(head," ","") head = replace(head,":","PM","AM","上午","下午","") head = mid(head,3,10) orderid = head + output End Function '************************************************************************ Function MyStatusName(a) select case a case 1 response.Write("新订单，等待客服处理") case 2 response.Write("会员自行取消") case 3 response.Write("无效单，已取消") case 4 response.Write("已确认订单，请支付货款") case 5 response.Write("已收到货款，交至仓库安排") case 6 response.Write("已从仓库发出，请耐心等待") case 7 response.Write("交易完成，合作愉快") end select end 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=nothing end function '************************************************************************ Function MyRealNameGo(a) if len(a)=2 then response.Write(a) elseif len(a)>=3 then response.Write(right(a,2)) else response.Write(a) end if end function %>

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!