<%
'平台：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