*/ ------------------------------------------------ --------------------------------------
*/ 出自：建站學http://www.jzxue.com
*/ 作者： 天涯聽雨E-mail：[email protected]：809252803
*/ 時間： 2007-10-6編程論壇首發
*/ 聲明： 尊重作者勞動，轉載請保留本段文字
*/ ------------------------------------------------ --------------------------------------
<%
'名稱：百萬數據進行分頁
'作者：天涯聽雨
'時間：2007年10月6日
'平台：Win2000+IIS5.0+Sql2000
'附言：第一次寫就運用簡單一些，大家不要罵……
'------------------------------------------------- ---------連接數據庫
Response.Buffer = True
Dim SqlLocalName,SqlUsername,SqlPassword,SqlDatabaseName
Dim ConnStr,Conn
'''''''''''''''''''''''''''''' SQL數據庫'''''''''''''''''' ''''''''''''''''''''''''''''''''''''''''''
SqlLocalName =(local) '連接IP[ 本地用(local) 外地用IP ]
SqlUsername=sa '數據庫用戶名
SqlPassword=XXXXXX '用戶密碼
SqlDatabaseName=News2008 '數據庫名
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
'------------------------------------------------- ---------連接數據庫完成
'定義變量
'TblName-表名；FldName-排列字段名；FieldName-需讀取的字段名；PageSizeX-分頁大小；PageCountX-總頁數；PageNo-當前頁;OrderType-排序方式；StrWhere-條件語句
'StrSql-總執行語句；FieldCount-總記錄數；StrTmp,StrOrder-臨時變量
Dim TblName,FldName,FieldName,PageSizeX,PageCountX,PageNo,OrderType,StrWhere
Dim StrSql,FieldCount,StrTmp,StrOrder,TopField
'進行賦值
PageNo=trim(Request.Querystring(PageNo))
if isnumeric(PageNo) or PageNo= then'如果沒有Page值，進行初始化值
PageNo=abs(PageNo)
if PageNo=0 then PageNo=1
else
PageNo=1
end if
TblName = TableNews
FldName = ID'說明，排序字段值在數據庫不能有重複:建議是主鍵
FieldName=ID,Title,AddTime
PageSizeX=30
OrderType=1 '0-為降序1-升序
StrWhere= (Code='0101') and (Hits>100) '注意: 一個條件就用()雙括號括起來第一個不用and
TopField=(PageNo-1)*PageSizeX'TOP多少條
if OrderType=0 then
StrTmp = <(Select min
StrOrder = Order BY [&FldName&] desc
else
StrTmp = >(Select max
StrOrder = Order BY [&FldName&] asc
end if
'定義sql語句
StrSql=Select Top &Clng(PageSizeX)& & FieldName & From &TblName&
if PageNo=1 then'如果是第一頁時，執行如下代碼查庫速度快
if StrWhere<> then
StrSql=StrSql& Where &StrWhere & StrOrder&
else
StrSql=StrSql & StrOrder
end if
else
if StrWhere<> then
StrSql=StrSql& Where & FldName & StrTmp&(&FldName&) From (Select top & Clng(TopField)& &FldName & From&TblName& Where & StrWhere & StrOrder &) as tblTmp) and & StrWhere & StrOrder&
else
StrSql=StrSql& Where & FldName & StrTmp&(&FldName&) From (Select top & Clng(TopField)& &FldName & From&TblName & StrOrder &) as tblTmp) &StrOrder&
end if
end if
Set rs=Conn.exeCute(StrSql) '執行查詢
if rs.bof and rs.eof then
Conn.Close : set Conn=nothing
response.write <script LANGUAGE='javascript'>alert('當前沒有找到任何記錄，請返回重新操作！');history.go(-1);</script>
response.end
end if
if StrWhere<> then '求總記錄數
FieldCount=Conn.exeCute(Select Count(&FldName&) From &TblName& Where &StrWhere&)(0)
else
FieldCount=Conn.exeCute(Select Count(&FldName&) From &TblName&)(0)
end if
if (FieldCount mod nPageSize)>0 then '求總頁數PageCountX
PageCountX=((FieldCount - (FieldCount mod PageSizeX))/ PageSizeX)+1
else
PageCountX= (FieldCount / PageSizeX)
end if
'顯示數據
do while not rs.eof
response.write&rs(Title)& 添加時間：&rs(AddTime)&<br/>
rs.movenext
loop
'進行關閉和釋放相關資源
rs.Close
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 轉到：<select name='page' size='1' onchange= javascript :window.location = '?PageNo='+this.options[this.selectedIndex].value;>
for i=1 to PageCountX
Response.Write<option value=&i&
if PageNo=i then Response.Write selected
Response.Write(>第-&i&-頁</option>)
next
Response.Write</selected>
%>