USE [Newspaper] GO /****** 对象: StoredProcedure [dbo].[mypage] 脚本日期: 03/30/2011 21:44:51 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[mypage] ( @TableName varchar(50), --表名 @ReFieldsStr varchar(200) = '*', --字段名(全部字段为*) @OrderString varchar(200), --排序字段(必须!支持多字段不用加order by) @WhereString varchar(500) =N'', --条件语句(不用加where) @PageSize int, --每页多少条记录 @PageIndex int = 1 , --指定当前为第几页 @TotalRecord int output --返回总记录数 ) AS BEGIN --处理开始点和结束点 Declare @StartRecord int; Declare @EndRecord int; Declare @TotalCountSql nvarchar(500); Declare @SqlString nvarchar(2000); set @StartRecord = (@PageIndex-1)*@PageSize + 1 set @EndRecord = @StartRecord + @PageSize - 1 SET @TotalCountSql= N'select @TotalRecord = count(*) from ' + @TableName;--总记录数语句 SET @SqlString = N'(select row_number() over (order by '+ @OrderString +') as rowId,'+@ReFieldsStr+' from '+ @TableName;--查询语句 -- IF (@WhereString! = '' or @WhereString!=null) BEGIN SET @TotalCountSql=@TotalCountSql + ' where '+ @WhereString; SET @SqlString =@SqlString+ ' where '+ @WhereString; END --第一次执行得到 --IF(@TotalRecord is null) -- BEGIN EXEC sp_executesql @totalCountSql,N'@TotalRecord int out',@TotalRecord output;--返回总记录数 -- END ----执行主语句 set @SqlString ='select * from ' + @SqlString + ') as t where rowId between ' + ltrim(str(@StartRecord)) + ' and ' + ltrim(str(@EndRecord)); Exec(@SqlString) END Dim rs,objCmd,page,pgcount,total,Maxperpage,j Set objCmd=Server.CreateObject("ADODB.Command") Set rs = Server.CreateObject("ADODB.Recordset") page = Request.QueryString("page") If page = "" Or CLng(page) < 1 Then page = 1 Maxperpage=100 objCmd.ActiveConnection=conn objCmd.CommandText="mypage" objCmd.CommandType=4 objCmd.Prepared= true 'Command的创建参数格式:参数名、参数数据类型、方向、数据大小、参数值 objCmd.Parameters.append objCmd.CreateParameter("@TableName",200,1,5,"Cards") objCmd.Parameters.append objCmd.CreateParameter("@ReFieldsStr",200,1,20,"cardid,cardpsw") objCmd.Parameters.append objCmd.CreateParameter("@OrderString",200,1,4,"id") objCmd.Parameters.append objCmd.CreateParameter("@WhereString",200,1,4,"") objCmd.Parameters.append objCmd.CreateParameter("@pageSize",3,1,,Maxperpage) objCmd.Parameters.append objCmd.CreateParameter("@pageIndex",3,1,,page) objCmd.Parameters.append objCmd.CreateParameter("@totalrecord",3,4) Set rs=objCmd.Execute() if Not rs.EOF then Response.Write("<ul>") while Not rs.EOF Response.Write("<li>"&rs("cardid")&"</li>") rs.MoveNext wend Response.Write("</ul>") else Response.Write("no data") end if rs.close '要想取得返回值,必须关闭rs set rs=nothing total=objCmd("@totalrecord") set objCmd=nothing '----------------------------- '得到总页数 '----------------------------- if total mod Maxperpage = 0 then pgcount=total/Maxperpage else pgcount=(total/Maxperpage)+1 end if If CLng(page) > pgcount Then page = pgcount If page = 1 Then Response.Write("上一页") Else Response.Write("<a href=""?page="&page-1&""">上一页</a>") End If if pgcount>page+4 then endpage=page+4 else endpage=pgcount end if For i=page-4 To endpage if Not i<1 then if i = CLng(page) then strTemp=strTemp & " " & "<span style=""color:#FF0000;font-weight:bold"">" & i & "</span> " else strTemp=strTemp & " " & "<a href=""?page="&i&""">" & i & "</a> " end if end if Next Response.Write(strTemp) If pgcount-page < 1 Then Response.Write(" 下一页") Else Response.Write(" <a href=""?page="&page+1&""">下一页</a>") End If response.Write(" <select name='turnPage' οnchange='window.location.href=""?page=""+this.value'>") for j=1 to pgcount if cstr(j)=page then response.Write("<option value='"&j&"' selected>"&j&"</option>") else response.Write("<option value='"&j&"'>"&j&"</option>") end if next Response.Write("</select>") else Response.Write("暂无相关内容!") end if