<%
'平台：Win2000+IIS5.0+Sql2000
'附言：升级上一次版本，上一次版为SQL版本，没有利用上存储过程
'上一版本 :[原创]优秀的百万级数据分页代码——(忙乎了半天)(http://bbs.bccn.net/thread-175543-1-1.html)
'----------------------------------------以下代码为网页代码
Response.Buffer = True
Dim SqlLocalName,SqlUsername,SqlPassword,SqlDatabaseName
Dim ConnStr,Conn
'''''''''''''''''''''''''''''''''SQL数据库 ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
SqlLocalName =(local)'连接IP[ 本地用 (local) 外地用IP ]
SqlUsername=sa '数据库用户名
SqlPassword=XXXXXX '用户密码
SqlDatabaseName=NewsTable '数据库名
ConnStr = Provider=Sqloledb;User ID= & SqlUsername & ; Password= & SqlPassword & ; Initial Catalog = & SqlDatabaseName & ; Data Source= & SqlLocalName & ;
'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
On Error Resume Next
Set Conn=Server.CreateObject(ADODB.Connection)
Conn.open ConnStr
If Err Then
err.Clear
Set Conn = Nothing
Response.Write <div style='font-size:14px;color=#ff0000' align='center'>数据库连接出错，请检查数据库连接字串</div>
Response.End
End If
'本类进行实例化代码，不将所有可能性条件分页考虑进去。本类仅作参考
'--------------------------------------------------------------------------------------------
'PageNo-当前页;PageSizeX-分页大小;PageCountX-总页数;FieldCount-总记录数;StrWhere-查询条件
Dim PageNo,PageSizeX,PageCountX,FieldCount,StrWhere
'进行赋值
PageNo=trim(Request.Querystring(PageNo))
PageSizeX=30'定义分页大小
if isnumeric(PageNo) or PageNo= then'如果没有Page值，进行初始化值
PageNo=abs(PageNo)
if PageNo=0 then PageNo=1
else
PageNo=1
end if
StrWhere=(ClassCode=0101 and Hits>20)'注意，此处注意字符的长度
set recom = server.createobject(adodb.command)
recom.activeconnection = Conn
recom.commandtype = 4
recom.commandtext = News_Class
recom.Parameters.Appendrecom.CreateParameter(@StrWhere,202,1,100,StrWhere)
recom.Parameters.Appendrecom.CreateParameter(@PageSize,3,1,,PageSizeX)
recom.Parameters.Appendrecom.CreateParameter(@PageIndex,3,1,,PageNo)
set rs = recom.execute ()
if rs.eof then
set rs=nothing : set recom=nothing: Conn.Close : set Conn=nothing
response.write <script LANGUAGE='javascript'>alert('当前没有找到任何记录，请返回重新操作！');history.go(-1);</script>
response.end
end if
'显示数据
do while not rs.eof
response.write&rs(Title)& 添加时间：&rs(AddTime)&<br/>
rs.movenext
loop
'取得记录总数，计算页数
set rs = rs.NextRecordset
if rs(countx) > 0 then
FieldCount=rs(countx)
else
FieldCount=0
end if
if (FieldCount mod PageSizeX)>0 then
PageCountX=((FieldCount - (FieldCount mod PageSizeX))/ PageSizeX)+1
else
PageCountX= (FieldCount / PageSizeX)
end if
'进行关闭和释放相关资源
set rs=nothing : set recom=nothing: Conn.Close : set Conn=nothing
'显示分页
if PageNo<=1 then
Response.Write <font color='#FF0000'>[首页] [上一页]</font>
else
Response.Write[<a href='?PageNo=1' target='_self' title='首页'><font color='#FF0000'>首页</font></a>]
Response.Write[<a href='?PageNo=&(PageNo-1)&' target='_self' title='上一页'><font color='#FF0000'>上一页</font></a>]
end if
if PageNo>=PageCountX then
Response.Write <font color='#FF0000'>[下一页] [尾页]</font>
else
Response.Write[<a href='?PageNo=&(PageNo+1)&'target='_self' title='下一页'><font color='#FF0000'>下一页</font></a>]
Response.Write[<a href='?PageNo=&PageCountX&' target='_self' title='尾页'><font color='#FF0000'>尾页</font></a>]
end if
Response.Write[页次 第<font color=red>&PageNo&</font>页/共<font color=red> & PageCountX &</font>页]
Response.Write [共<font color=red>&FieldCount&</font>条 <font color=red>& PageSizeX & </font>条/页]
Response.Write转到 & <input id=zhuanpage name=zhuanpage size=2 value=&PageNo&> & 页<input type=submit value=Go onclick=gotoPage()>
Response.Write</td></tr></table>
Response.Write<script>function gotoPage(){var pg=document.getElementById('zhuanpage').value;if(isNaN(pg)||pg>&PageCountX&||pg<0){window.alert('请正确输入页码,只能为正整数!且不能大于&PageCountX&');return false;}else{window.location.href='?PageNo='+document.getElementById('zhuanpage').value+'';return true;}}</script>
%>
'--------------------------------------------------------以下代码为存储过程代码
CREATE procedureNews_Class
--资讯新闻百万级分页
(
@StrWhere varchar(100),
@PageSize int,
@PageIndexint
)
AS
declare@strSQLvarchar(2000) -- 主语句
declare@strCountSQL varchar(2000) -- 总记录主语句
declare@strTmpvarchar(1000) -- 临时变量
Set @strTmp = Select top + str(@PageSize) + Title,AddTime from Tb_News --此处注意，需几个字段读几个字段
if @StrWhere<>''
Begin
Set @strSQL=@strTmp + where ID < (select min(ID) from (select top + str((@PageIndex-1)*@PageSize)+ ID from Tb_News Where +@StrWhere+order by ID desc)as tblTmp ) and +@StrWhere+ order by ID desc
set @strCountSQL=select count(ID) as countx from Tb_News Where +@StrWhere+
End
else
Begin
Set @strSQL=@strTmp + where ID < (select min(ID) from (select top + str((@PageIndex-1)*@PageSize)+ ID from Tb_News order by ID desc)as tblTmp ) order by ID desc
set @strCountSQL=select count(ID) as countx from Tb_News
End
if @PageIndex = 1
if @StrWhere<>''
Begin
Set @strSQL=@strTmp + Where +@StrWhere+ order by ID desc
End
else
Begin
Set @strSQL=@strTmp + order by ID desc
End
exec (@strSQL)
exec (@strCountSQL)
GO