通過ASP記錄進(jìn)行分頁的完美解決方案

<%@ Language=VBScript %>
<% Option Explicit %>
<SCRIPT LANGUAGE=VBScript RUNAT=SERVER>
  '確保引用 ADO Typelib 或使用 ADOVBS.Inc
  Dim iPageNum, iRowsPerPage

  Main
    Sub Main()
   Dim rst
   Dim sSQL, sConnString

   If Request.QueryString("iPageNum") = "" Then
     iPageNum = 1
   Else
     iPageNum = Request.QueryString("iPageNum")
     iPageNum = CInt(iPageNum)
   End If

   iRowsPerPage = 10

   sConnString = "Provider=SQLOLEDB.1;password=Xyz123;user id=WebUser;" & _
         "Initial Catalog=NorthWind;Data Source=MySQLServer;" & _
         "network=dbmssocn;"

   '下列 SQL 從 SQL 視圖中檢索所有列。
   '要優(yōu)化性能：
   '- 使用存儲(chǔ)過程、視圖或在 SELECT 中指定列
   '- 使用限制返回的記錄的條件（例如，WHERE 子句）
   sSQL = "SELECT CategoryName, ProductName, QuantityPerUnit,"
   sSQL = sSQL & "UnitsInStock, Discontinued"
      sSQL = sSQL & " FROM [Products By Category]"

   Set rst = GetRecords(sConnString, sSQL)

   WriteTableHeader rst
   WriteTableBody rst, iRowsPerPage, iPageNum
   ShowNavBar rst

   'ShowFastNavBar 方法不使用 RecordCount
   '或 PageCount，所以它重試的記錄數(shù)僅等于
   '記錄集的 CacheSize 指定的數(shù)量。

   'ShowFastNavBar rst

   CleanUp rst
  End Sub

  Function GetRecords(sConnString, sSQL)
  Dim cnn
  Dim rst

    set cnn = Server.CreateObject("ADODB.CONNECTION")
    cnn.ConnectionString = sConnString
    nn.Open

    Set rst = Server.CreateObject("ADODB.RECORDSET")

    Set rst.ActiveConnection = cnn

     '當(dāng)記錄集打開時(shí)，adUseClient 的 CursorLocation
     ' 將檢索所有的記錄。
     'adUseServer 允許沿用 CacheSize
     rst.CursorLocation = adUseServer

     '在使用服務(wù)器端游標(biāo)時(shí)，CacheSize 
     '限制了取回的行數(shù)。我們將只抓取正在顯示的
     '的記錄的數(shù)目 - iRowsPerPage
     rst.CacheSize = iRowsPerPage

     rst.Open sSQL,,adOpenStatic, adLockReadOnly牋?
     Set GetRecords = rst
    end Function

    Sub WriteTableHeader(rst)
    Dim fld

     Response.Write "<TABLE WIDTH=80% BORDER=1>"
     Response.Write "<TR>"

     '建立表的列標(biāo)題
      For Each fld in rst.Fields
       Response.Write "<TD><B>" & fld.Name & "</B></TD>"
    Next
    Response.Write "</TR>"
   End Sub

   Sub WriteTableBody(rst, iRowsPerPage, iPageNum)
   Dim iLoop
   Dim fld

   iLoop = 1

   rst.PageSize = iRowsPerPage
   rst.AbsolutePage = iPageNum

   '寫出記錄的當(dāng)前頁
   Do While (Not rst.EOF) and (iLoop <= iRowsPerPage)
     Response.Write "<TR>"
      For Each fld in rst.Fields
       Response.Write "<TD>" & fld.value & "</TD>"
        Next
         iLoop = iLoop + 1
         rst.MoveNext
         Response.Write "</TR>"
     Loop
     Response.Write "</TABLE>"
   End Sub

   Sub ShowNavBar(rst)
   Dim iPageCount
   Dim iLoop
   Dim sScriptName

    '本版本提供了更豐富的用戶導(dǎo)航，但是
    '依賴于 RecordCount 和 PageCount，
    '它抵消了為服務(wù)器端游標(biāo)
    '指定 CacheSize 的好處。

    Response.Write "<BR><BR>"
    sScriptName = Request.ServerVariables("SCRIPT_NAME")

    If iPageNum > 1 Then
      Response.Write " <a href=" & sScriptName & "?iPageNum="
      Response.Write (iPageNum -1) & "><< Previous</a>"
    End If

    iPageCount = rst.PageCount
    Do Until iLoop > iPageCount
    f iLoop = iPageNum Then
       Response.Write " <B>" & CStr(iLoop) & "</B>"
      Else
       Response.Write " <a href=" & sScriptName & "?iPageNum=" & _
       Cstr(iLoop) & ">" & iLoop & "</a>"
       End If
       iLoop = iLoop + 1
    Loop

    If Not rst.EOF Then
     Response.Write " <a href=" & sScriptName & "?iPageNum="
     Response.Write (iPageNum +1) & "> Next >></a><BR>"
    Else
       Response.Write "<BR>"
    End If

    Response.Write "Page " & iPageNum & " of " & iPageCount & "<BR>"
    Response.Write rst.RecordCount & " Records" 牋?
   End Sub

   Sub ShowFastNavBar(rst)
   Dim iPageCount
   Dim iLoop
   Dim sScriptName

     '在指定 CacheSize 和使用服務(wù)器端游標(biāo)時(shí)，
     '該方法特別有效，因?yàn)樗�不使�?RecordCount
     '和 PageCount。需要用戶具有經(jīng)驗(yàn)。

     Response.Write "<BR><BR>"
     sScriptName = Request.ServerVariables("SCRIPT_NAME")

     If iPageNum > 1 Then
      Response.Write " <a href=" & sScriptName & "?iPageNum="
      Response.Write (iPageNum -1) & "><< Previous</a>"
    End If

    If Not rst.EOF Then
      Response.Write " <a href=" & sScriptName & "?iPageNum="
      Response.Write (iPageNum +1) & "> Next >></a><BR>"
    Else
      Response.Write "<BR>"
    End If

    Response.Write "Page " & iPageNum

   End Sub

   Sub CleanUp(rst)
     If Not rst Is Nothing then
       If rst.state = adStateOpen then rst.close
       set rst = nothing
     End If
   End Sub

</SCRIPT>