分页代码如下,其中上一页的SQL有问题,所是ORDER附近有错误,请高手指点!!Option Explicit
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String
Dim PageCount As Long '总页数
Dim PageNum As Long '当前页数
Dim maxID, minID
Private Sub Form_Load()
'连接数据库
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data Source=" & App.Path & "\db1.mdb;Persist " _
& "Security Info=False;Jet OLEDB:Database Password="
'计算分页总数
sql = "select count(*) as cnt from tb "
Set rs = New ADODB.Recordset
rs.Open sql, cn, adOpenKeyset, adLockOptimistic
If rs.RecordCount = 0 Then Exit Sub
PageCount = Abs(Int(rs!cnt / (-20)))
PageNum = 1
Set rs = Nothing'显示第一页
sql = "select top 20 * from tb order by id"
Set rs = New ADODB.Recordset
rs.Open sql, cn, adOpenKeyset, adLockOptimistic
If rs.RecordCount = 0 Then Exit Sub
Set fg.DataSource = rs
rs.MoveLast
maxID = rs!id
Set rs = Nothing
End SubPrivate Sub cmdFirst_Click() '第一页
If PageNum = 1 Then Exit Sub
sql = "select top 20 * from tb order by id"
Set rs = New ADODB.Recordset
rs.Open sql, cn, adOpenKeyset, adLockOptimistic
If rs.RecordCount = 0 Then Exit Sub
Set fg.DataSource = rs
rs.MoveLast
maxID = rs!id
PageNum = 1
Set rs = Nothing
End SubPrivate Sub cmdDown_Click() '下一页
If PageNum = PageCount Then Exit Sub
PageNum = PageNum + 1
sql = "select top 20 * from tb " & _
"where id > " & maxID & _
" order by id "
Set rs = New ADODB.Recordset
rs.Open sql, cn, adOpenKeyset, adLockOptimistic
If rs.RecordCount = 0 Then Exit Sub
Set fg.DataSource = rs
rs.MoveLast
maxID = rs!id
rs.MoveFirst
minID = rs!id
Set rs = Nothing
End SubPrivate Sub cmdUp_Click() '上一页
If PageNum = 1 Then Exit Sub
PageNum = PageNum - 1
sql = "select * from (select top 20 * from tb " & _
"where id < " & minID & _
" order by id desc) order by id"
Set rs = New ADODB.Recordset
rs.Open sql, cn, adOpenKeyset, adLockOptimistic
If rs.RecordCount = 0 Then Exit Sub
Set fg.DataSource = rs
rs.MoveLast
maxID = rs!id
rs.MoveFirst
minID = rs!id
Set rs = Nothing
End SubPrivate Sub cmdLast_Click() '最后一页
Dim mTop As Integer
If PageNum = PageCount Then Exit Sub
mTop = PageCount Mod 20
If mTop = 0 Then
sql = "select * from (select top 20 * from tb " & _
"order by id desc) as t order by id"
Else
sql = "select * from (select top " & mTop & " * from tb " & _
"order by id desc) as t order by id"
End If
Set rs = New ADODB.Recordset
rs.Open sql, cn, adOpenKeyset, adLockOptimistic
If rs.RecordCount = 0 Then Exit Sub
Set fg.DataSource = rs
rs.MoveLast
maxID = rs!id
rs.MoveFirst
minID = rs!id
PageNum = PageCount
Set rs = Nothing
End Sub
Private Sub Form_Unload(Cancel As Integer)
Set cn = Nothing
End
End Sub
Dim cn As ADODB.Connection
Dim rs As ADODB.Recordset
Dim sql As String
Dim PageCount As Long '总页数
Dim PageNum As Long '当前页数
Dim maxID, minID
Private Sub Form_Load()
'连接数据库
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " _
& "Data Source=" & App.Path & "\db1.mdb;Persist " _
& "Security Info=False;Jet OLEDB:Database Password="
'计算分页总数
sql = "select count(*) as cnt from tb "
Set rs = New ADODB.Recordset
rs.Open sql, cn, adOpenKeyset, adLockOptimistic
If rs.RecordCount = 0 Then Exit Sub
PageCount = Abs(Int(rs!cnt / (-20)))
PageNum = 1
Set rs = Nothing'显示第一页
sql = "select top 20 * from tb order by id"
Set rs = New ADODB.Recordset
rs.Open sql, cn, adOpenKeyset, adLockOptimistic
If rs.RecordCount = 0 Then Exit Sub
Set fg.DataSource = rs
rs.MoveLast
maxID = rs!id
Set rs = Nothing
End SubPrivate Sub cmdFirst_Click() '第一页
If PageNum = 1 Then Exit Sub
sql = "select top 20 * from tb order by id"
Set rs = New ADODB.Recordset
rs.Open sql, cn, adOpenKeyset, adLockOptimistic
If rs.RecordCount = 0 Then Exit Sub
Set fg.DataSource = rs
rs.MoveLast
maxID = rs!id
PageNum = 1
Set rs = Nothing
End SubPrivate Sub cmdDown_Click() '下一页
If PageNum = PageCount Then Exit Sub
PageNum = PageNum + 1
sql = "select top 20 * from tb " & _
"where id > " & maxID & _
" order by id "
Set rs = New ADODB.Recordset
rs.Open sql, cn, adOpenKeyset, adLockOptimistic
If rs.RecordCount = 0 Then Exit Sub
Set fg.DataSource = rs
rs.MoveLast
maxID = rs!id
rs.MoveFirst
minID = rs!id
Set rs = Nothing
End SubPrivate Sub cmdUp_Click() '上一页
If PageNum = 1 Then Exit Sub
PageNum = PageNum - 1
sql = "select * from (select top 20 * from tb " & _
"where id < " & minID & _
" order by id desc) order by id"
Set rs = New ADODB.Recordset
rs.Open sql, cn, adOpenKeyset, adLockOptimistic
If rs.RecordCount = 0 Then Exit Sub
Set fg.DataSource = rs
rs.MoveLast
maxID = rs!id
rs.MoveFirst
minID = rs!id
Set rs = Nothing
End SubPrivate Sub cmdLast_Click() '最后一页
Dim mTop As Integer
If PageNum = PageCount Then Exit Sub
mTop = PageCount Mod 20
If mTop = 0 Then
sql = "select * from (select top 20 * from tb " & _
"order by id desc) as t order by id"
Else
sql = "select * from (select top " & mTop & " * from tb " & _
"order by id desc) as t order by id"
End If
Set rs = New ADODB.Recordset
rs.Open sql, cn, adOpenKeyset, adLockOptimistic
If rs.RecordCount = 0 Then Exit Sub
Set fg.DataSource = rs
rs.MoveLast
maxID = rs!id
rs.MoveFirst
minID = rs!id
PageNum = PageCount
Set rs = Nothing
End Sub
Private Sub Form_Unload(Cancel As Integer)
Set cn = Nothing
End
End Sub
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货