@H_301_0@目前大家用vb中的datagrid分頁顯示數據庫內容的方法都很類似
@H_301_0@就是先使用了ADODB的分頁功能,以提供一個游標定位,自動將數據分頁
@H_301_0@但是分頁後如果直接將數據源送給datagrid就會出現當前頁內容顯示的同時下邊的記錄也會顯示!!!也就是說datagrid顯示的數據稍多於我們想看到的內容
@H_301_0@我畫了個圖來說明這個現象(假如我們想每頁顯示10條記錄,而31,32,33條記錄也可能顯示在datagrid上顯示出來)
@H_301_0@首先:我們先來看看這個帶缺陷分頁的實現方法
@H_301_0@'-------define hong------------
@H_301_0@Dim ConStr As String
@H_301_0@Dim cn As ADODB.Connection
@H_301_0@Dim rs As ADODB.Recordset
@H_301_0@Dim statestring As String
@H_301_0@Dim ALL_CON As String
@H_301_0@'page use this
@H_301_0@Dim myCurrentPage As Integer
@H_301_0@Dim myPageCount As Integer
@H_301_0@初始化部分代碼
@H_301_0@'------------------------connect-database-----------
@H_301_0@Set cn = New ADODB.Connection
@H_301_0@Set rs = New ADODB.Recordset
@H_301_0@'--------------------connect the database--------------
@H_301_0@ConStr = ALL_CON
@H_301_0@ '------------------change
@H_301_0@cn.Open ConStr
@H_301_0@cn.CursorLocation = adUseClient
@H_301_0@DataGrid1.Refresh
@H_301_0@ Select Case cn.State
@H_301_0@ Case adStateClose
@H_301_0@ statestring = "adStateClosed"
@H_301_0@ Case adStateOpen
@H_301_0@ statestring = "adStateOpen"
@H_301_0@ End Select
@H_301_0@ If statestring = "adStateClosed" Then
@H_301_0@MsgBox "硈钡ア毖",statestring
@H_301_0@Else
@H_301_0@End If
@H_301_0@'----------open the table------------------
@H_301_0@rs.Open "Select * from userr",cn,adOpenKeyset,adLockReadOnly,1
@H_301_0@ '----------------------change-----------------
@H_301_0@Text1.Text = rs.RecordCount '總記錄數
@H_301_0@rs.PageSize = 10 ‘每頁記錄數
@H_301_0@myPageCount = rs.PageCount '頁數
@H_301_0@myCurrentPage = rs.AbsolutePage '設置當前是第幾頁
@H_301_0@Set DataGrid1.DataSource = rs 'show on the table
@H_301_0@DataGrid1.Refresh
@H_301_0@這樣你就會發現第一頁下面的幾條記錄也出來了
@H_301_0@解決辦法:
@H_301_0@解決辦法大概有這幾種
@H_301_0@1, 把數據分頁的內容一個一個賦值給datagrid的表格,這是沒問題的可以做到
@H_301_0@2, 將分頁的rs內容放到一個緩衝的數據表裏面然後顯示緩衝數據表(select into 很方便)
@H_301_0@3, 將分頁的rs內容放到一個新的rs裏面然後把datagrid的數據源設成這個新的rs
@H_301_0@這裡我們看一下第三種方法,其餘兩種類似本操作
@H_301_0@資料庫:test2000.mdb
表:numbers
欄位:Id(自動編號),anumber(數位)
因為DataGrid控制項我們採用直接綁定記錄集來顯示資料.所以分頁處理我們採用了間接的辦法,定義另一個記錄集objrs,將分頁後的記錄集付給objrs.然後綁定DataGrid
''效果還不錯,我加了詳細地注釋,像pagesize,AbsolutePage的用法可參考msdn
VB中新建工程,form中添加DataGrid控制項,按鈕cmdPrevIoUs和cmdNext,文本框txtPage
''引用microsoft active data object 2.x object library
Option Explicit
Dim conn As ADODB.Connection
Dim lCurrentPage As Long
Private Sub cmdNext_Click()
lCurrentPage = lCurrentPage + 1
Call Loadcontrol(lCurrentPage)
End Sub
Private Sub cmdPrevIoUs_Click()
If lCurrentPage > 1 Then
lCurrentPage = lCurrentPage - 1
Call Loadcontrol(lCurrentPage)
End If
End Sub
Private Sub Form_Load()
Set conn = New ADODB.Connection
conn.CursorLocation = adUseClient
conn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\test2000.mdb;"
lCurrentPage = 1
Call Loadcontrol(lCurrentPage)
End Sub
Private Sub Loadcontrol(lPage As Long)
Dim adoPrimaryRS As ADODB.Recordset
Dim lPageCount As Long
Dim nPageSize As Integer
Dim lCount As Long
''每頁顯示的紀錄
nPageSize = 10
Set adoPrimaryRS = New ADODB.Recordset
adoPrimaryRS.Open "select * from numbers",conn,adOpenStatic,adLockOptimistic
adoPrimaryRS.PageSize = nPageSize
''頁數
lPageCount = adoPrimaryRS.PageCount
If lCurrentPage > lPageCount Then
lCurrentPage = lPageCount
End If
adoPrimaryRS.AbsolutePage = lCurrentPage
''定義另一個記錄集
Dim objrs As New ADODB.Recordset
''添加欄位名稱
For lCount = 0 To adoPrimaryRS.Fields.Count - 1
objrs.Fields.Append adoPrimaryRS.Fields(lCount).Name,adVarChar,adoPrimaryRS.Fields(lCount).DefinedSize
Next
''打開記錄集
objrs.Open
''將指定記錄數迴圈添加到objrs中
For lCount = 1 To nPageSize
objrs.AddNew
objrs!id = adoPrimaryRS!id
objrs!anumber = adoPrimaryRS!anumber
adoPrimaryRS.MoveNext
Next
''綁定
Set DataGrid1.DataSource = objrs
''在文本框顯示頁數
txtPage = lPage & "/" & adoPrimaryRS.PageCount
End Sub
Private Sub Form_Unload(Cancel As Integer)
If Not conn Is Nothing Then
conn.Close
End If
Set conn = Nothing
End Sub
‘文本框中輸入頁數,回車跳轉到指定位置
Private Sub txtPage_KeyDown(KeyCode As Integer,Shift As Integer)
lCurrentPage = Val(txtPage.Text)
Call Loadcontrol(lCurrentPage)
End Sub
@H_301_0@本文參考資料:http://topic.csdn.net/t/20020612/08/796414.html 原文链接:https://www.f2er.com/vb/260640.html