在vb中实现分页处理数据库
作者：王勋
当数据量太大时，最好的办法是分页处理数据库中的数据
’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
