在vb中實現分頁處理數據庫

發布: 2007-7-14 20:28 | 作者: 佚名 | 來源: 網絡轉載 | 查看: 36次 | 進入軟件測試論壇討論

作者：王勛

當數據量太大時，最好的辦法是分頁處理數據庫中的數據
'PROVIDER=Microsoft.Jet.OLEDB.3.51;Data Source=E:\wang_xun\work\program\code\my_code\PrjPagination\bank.mdb;
Private pTotal As Integer
Private pCurrent As Integer
Private pCon As ADODB.Connection
Private pSize As Integer
Private Sub cmdFirst_Click()
pCurrent = 1
Call InitListViewData
Call InitDisplay
End Sub

Private Sub cmdGo_Click()
pCurrent = CInt(txtVal)
Call InitListViewData
Call InitDisplay
End Sub

Private Sub cmdLast_Click()
pCurrent = pTotal
Call InitListViewData
Call InitDisplay
End Sub

Private Sub cmdNext_Click()
pCurrent = pCurrent + 1
Call InitListViewData
Call InitDisplay
End Sub

Private Sub cmdPrevious_Click()
pCurrent = pCurrent - 1
Call InitListViewData
Call InitDisplay
End Sub

Private Sub Form_Load()
pSize = 50
Call AddHeader
Call MakeCon
Call InitListViewData
Call InitDisplay
End Sub

Private Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
Cancel = 0
End Sub

Private Sub Form_Unload(Cancel As Integer)
Call ClearVar
End Sub

Sub ClearVar()
pTotal = 0
pCurrent = 0
Set pCon = Nothing
End Sub
Sub InitDisplay()
If pCurrent > pTotal Then
Call cmdFirst_Click
End If
Select Case pTotal
Case Is > 0
Call InitGoButton
Select Case pCurrent
Case 1
cmdFirst.Enabled = False
cmdLast.Enabled = True
cmdPrevious.Enabled = False
cmdNext.Enabled = True
Case Is < pTotal And pCurrent > 1
cmdFirst.Enabled = True
cmdLast.Enabled = True
cmdPrevious.Enabled = True
cmdNext.Enabled = True
Case Is = pTotal
cmdFirst.Enabled = True
cmdLast.Enabled = False
cmdPrevious.Enabled = True
cmdNext.Enabled = False
Case Else
cmdFirst.Enabled = False
cmdLast.Enabled = False
cmdPrevious.Enabled = False
cmdNext.Enabled = False
End Select
Case Else
cmdFirst.Enabled = False
cmdLast.Enabled = False
cmdPrevious.Enabled = False
cmdNext.Enabled = False
cmdGo.Enabled = False
End Select
labPage.Caption = "共" & Str(pTotal) & "頁，當前是第" & Str(pCurrent) & "頁"
End Sub

Private Sub txtnum_Change()
pSize = CInt(txtnum.Text)
End Sub

Private Sub txtnum_ValidationError(InvalidText As String, StartPosition As Integer)
pSize = 1
End Sub

'Private Sub txtNum_Change()
'If Not (KeyAscii > 47 And KeyAscii < 58) Then
' Beep
' KeyAscii = 0
'End If
'End Sub

Private Sub txtVal_Change()
Call InitGoButton
End Sub

Private Sub txtVal_KeyPress(KeyAscii As Integer)
If Not (KeyAscii > 47 And KeyAscii < 58) Then
Beep
KeyAscii = 0
End If
End Sub
Sub InitGoButton()
If Len(txtVal) > 0 Then
If CInt(txtVal) > 0 And CInt(txtVal) <= pTotal Then
cmdGo.Enabled = True
Else
cmdGo.Enabled = False
End If
Else
cmdGo.Enabled = False
End If
End Sub
Sub AddHeader()
With lv
.View = lvwReport
.ColumnHeaders.Clear
.ColumnHeaders.Add , , "ID", 800
.ColumnHeaders.Add , , "名稱", 2000
End With
End Sub
Sub MakeCon()
Dim a As New ADODB.Connection
'a.Open "PROVIDER=Microsoft.Jet.OLEDB.3.51;Data Source=E:\wang_xun\work\program\code\my_code\PrjPagination\bank.mdb;"
a.Open "PROVIDER=Microsoft.Jet.OLEDB.3.51;Data Source=" & App.Path & "\bank.mdb;"
If Not a Is Nothing Then
Set pCon = a
End If
Set a = Nothing
End Sub
Sub InitListViewData()
Dim rs As ADODB.Recordset
Dim s$, lp%, intSize%, sKey$
Dim m As ListItem
lv.ListItems.Clear
intSize = pSize
s = "select * from sort"
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
'Set rs = pCon.Execute(s)
rs.Open s, pCon, , , adCmdText
rs.PageSize = intSize
pTotal = rs.PageCount
If pTotal = 0 Then '空
pCurrent = 0
Else
If pCurrent = 0 Then pCurrent = 1
rs.AbsolutePage = pCurrent
For lp = 1 To rs.PageSize
If rs.EOF Then Exit For
sKey = CStr(rs(0))
Set m = lv.ListItems.Add(, , sKey)
If Not IsNull(rs(1)) Then m.SubItems(1) = Trim(rs(1))
rs.MoveNext
Next
End If
Set rs = Nothing
End Sub