用戶登錄  |  用戶注冊
首 頁源碼下載網絡學院最新源碼源碼排行屏蔽廣告
當前位置:新興網絡 > 網絡學院 > Asp編程 > Asp 實例

ASP 海量數據分頁存儲過程代碼

減小字體 增大字體 作者:天涯聽雨  來源:本站整理  發布時間:2010-07-17 12:55:10
以下是ASP網頁代碼:
ASP code復制代碼
<%
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.Append recom.CreateParameter("@StrWhere", 202, 1, 100, StrWhere)
recom.Parameters.Append recom.CreateParameter("@PageSize", 3, 1, , PageSizeX)
recom.Parameters.Append recom.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")&"&nbsp;&nbsp;&nbsp;添加時間:"&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>"
%>

以下是存儲過程:

SQL code復制代碼
CREATE procedure  News_Class
--資訊新聞百萬級分頁
(
@StrWhere     varchar(100),
@PageSize     int,        
@PageIndex    int       
)
AS
declare  @strSQL        varchar(2000)     -- 主語句
declare  @strCountSQL   varchar(2000)     -- 總記錄主語句
declare  @strTmp        varchar(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

Tags:ASP 海量數據 分頁 存儲過程

作者:天涯聽雨
  • 好的評價 如果您覺得此文章好,就請您
      0%(0)
  • 差的評價 如果您覺得此文章差,就請您
      0%(0)

網絡學院評論評論內容只代表網友觀點,與本站立場無關!

   評論摘要(共 1 條,得分 85 分,平均 85 分) 查看完整評論
[回復] 1semnpabemep   打分:85 分  發表時間:2010-11-21
· http://mordijiew.us
美国百家乐注册