通过ASP记录进行分页的完美解决方案

<%@ 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 视图中检索所有列。
   '要优化性能：
   '- 使用存储过程、视图或在 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，所以它重试的记录数仅等于
   '记录集的 CacheSize 指定的数量。

   '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

     '当记录集打开时，adUseClient 的 CursorLocation
     ' 将检索所有的记录。
     'adUseServer 允许沿用 CacheSize
     rst.CursorLocation = adUseServer

     '在使用服务器端游标时，CacheSize 
     '限制了取回的行数。我们将只抓取正在显示的
     '的记录的数目 - 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>"

     '建立表的列标题
      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

   '写出记录的当前页
   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

    '本版本提供了更丰富的用户导航，但是
    '依赖于 RecordCount 和 PageCount，
    '它抵消了为服务器端游标
    '指定 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 和使用服务器端游标时，
     '该方法特别有效，因为它不使用 RecordCount
     '和 PageCount。需要用户具有经验。

     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>