DataReader做為數據源手動分頁

最新推荐文章于 2025-08-20 16:21:11 发布

转载最新推荐文章于 2025-08-20 16:21:11 发布 · 66 阅读

本文介绍了一个使用 ASP.NET 和 OleDb 进行数据库连接及数据分页显示的例子。通过设置每页显示记录数和当前页数，可以实现对数据库中用户信息的有效分页展示。

<%@ Import Namespace="System.Data" %>
<%@ Import NameSpace="System.Data.OleDb" %>
<html>
<head runat=server>
<title></title>
</head>
<body>
<%
Dim objCon As OleDbConnection
Dim objCmd As OleDbCommand
Dim objDataReader As OleDbDataReader
Dim intFCount, intI As Integer
Dim pageNo, pageSize As String
Dim intPageNo, intPageSize, intStartRec, intStopRec As Integer
Dim intMaxPageCount, intMaxRec, intCount As Integer
' 取得目前資料表記錄的頁數
pageNo = Request.QueryString("PageNo")
If pageNo = "" Then
   intPageNo = 1
Else
   intPageNo = Convert.ToInt32(pageNo)
End If
'//取得每一頁顯示的記錄數

pageSize = Request.QueryString("PageSize")
If pageSize = "" Then
   intPageSize = 2
Else
   intPageSize = Convert.ToInt32(pageSize)
End If
' OLEDB提供者字串
Dim strDbCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
               Server.Mappath("Users.mdb")
' 建立Connection物件
objCon = New OleDbConnection(strDbCon)
objCon.Open() ' 開啟資料庫連結
' 建立Command物件的SQL指令
objCmd = New OleDbCommand()
objCmd.CommandText = "SELECT Count(*) FROM Users"
objCmd.Connection = objCon
' 取得DataReader物件的記錄數
objDataReader = objCmd.ExecuteReader()
objDataReader.Read()
intMaxRec = objDataReader.GetValue(0)
objDataReader.Close() ' 關閉DataReader物件
' 第二次取得記錄內容
objCmd.CommandText = "SELECT * FROM Users"
objDataReader = objCmd.ExecuteReader()
' 取得欄位數目
intFCount = objDataReader.FieldCount - 1
' 是否有查詢到記錄
If intMaxRec > 0 Then
  ' 計算開始的記錄
  intStartRec = intPageSize * (intPageNo - 1) + 1
  ' 計算結束的記錄
  intStopRec = intStartRec + intPageSize - 1
  ' 計算頁數
  intMaxPageCount = intMaxRec \ intPageSize
  If (intMaxRec MOD intPageSize) > 0 Then
    intMaxPageCount = intMaxPageCount + 1
  End If
  Response.Write("<table border=1><tr>")
  ' 顯示資料庫的欄位名稱
  For intI = 0 to intFCount
      Response.Write("<td><b>" & objDataReader.GetName(intI) & "</b></td>")
  Next
  Response.Write("</tr>")
  intCount = 0
  ' 顯示資料表的記錄
  While objDataReader.Read() AND intCount < intStopRec
    intCount = intCount + 1
    If intCount >= intStartRec Then
      Response.Write("<tr>")
      ' 顯示每筆記錄的欄位
      For intI = 0 to intFCount
         If objDataReader.IsDBNull(intI) = False Then
           Response.Write("<td valign=""top"">" & objDataReader.Item(intI) & "</td>")
         Else
           Response.Write("<td valign=""top"">---</td>")
         End If
      Next
      Response.Write("</tr>")
    End If
  End While
  Response.Write("</table>")
  objDataReader.Close() ' 關閉DataReader
  Response.Write("一共有" & intMaxRec & "筆<br>")
  ' 目前的頁數
  Response.Write("目前為第" & intPageNo & "頁/總共有" & intMaxPageCount & "頁<br>")
  ' 建立數字的超連結
  Dim strURL, intPreviousPageNo, intNextPageNo
  For intI = 1 To intMaxPageCount
    strURL = "<a href='Ch10-2-3.aspx?PageNo=" & intI
    strURL = strURL & "&PageSize=" & intPageSize & "'>" & intI & "</a>"
    Response.Write(strURL & " ")
    If intI mod 10 = 0 Then
       Response.Write("<br>")
    End If
  next
  ' 上一頁的超連結
  intPreviousPageNo = intPageNo - 1
  If intPreviousPageNo > 0 Then
    strURL = "<a href='Ch10-2-3.aspx?PageNo=" & intPreviousPageNo
    strURL = strURL & "&PageSize=" & intPageSize & "'>上一頁</a>"
    Response.Write(strURL & " ")
  End If
  ' 下一頁的超連結
  intNextPageNo = intPageNo + 1
  If intNextPageNo <= intMaxPageCount Then
    strURL = "<a href='Ch10-2-3.aspx?PageNo=" & intNextPageNo
    strURL = strURL & "&PageSize=" & intPageSize & "'>下一頁</a>"
    Response.Write(strURL & " ")
  End If
End If
objCon.Close() ' 關閉資料庫連結
%>
</body>
</html>