1,216
社区成员
发帖
与我相关
我的任务
分享
CREATE PROCEDURE [dbo].[P_Pages]
@PCount int , --请求页数
@PageSize int, --页大小
@Table nvarchar(100), --查询表名
@Fields nvarchar(500), --查询字段
@Where nvarchar(500), --查询条件
@Order nvarchar(100) --排序字段
AS
begin
declare @i int,@px int,@py int
declare @pMax int,@sql nvarchar(2000)
--总记录数
set @sql='select @i=count(1) from '+@Table
exec sp_executesql @sql, N'@i int out,@Table nvarchar(100)', @i out, @Table;
--总页数
set @pMax=floor(@i/@pagesize)+1
if @i%@pagesize=0 set @pMax=@pMax-1;
if @PageSize<2 set @PageSize=2
--每页记录始末
if @pcount<1 set @pcount=1
if @pcount>@pMax set @pcount=@pMax
select @px=(@pcount-1)*@pagesize,@py=@pcount*@pagesize;
set @sql='SELECT '+ @Fields +' FROM(
SELECT TOP '+CAST(@py AS NVARCHAR) +' ROW_NUMBER() OVER(ORDER BY '+@Order+') AS ROWID,*
FROM '+@Table+' Where '+@Where+') AS tmp1 WHERE ROWID>'+CAST(@px AS NVARCHAR)
print (@sql);
exec (@sql);
end
测试:
exec P_pages 10,5,'account','*','','name asc'
Dim db As New ADODB.Connection, RS As New ADODB.Recordset, ZJL As Long, MYJL As Integer, DQYS As Integer, ZYS As Integer 'ZJL 是总记录变量, MYJL 是每页显示记录属变量,DQYS 是当前显示页数变量,ZYS 是总页数变量,均是模块级变量。
Private Sub KKK(db) '数据库连接过程
db.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & App.Path & "\ABC.accdb;Jet OLEDB:Database Password=;"
db.Open
End Sub
Private Sub Combo1_Click() '转到第几页的过程
DQYS = Val(Combo1.Text)
Call MSHF
End Sub
Private Sub Command1_Click()
'第一页
Combo1.Text = ""
DQYS = 1
Call MSHF
End Sub
Private Sub Command2_Click()
'下一页
Combo1.Text = ""
DQYS = DQYS + 1
If DQYS >= ZYS Then DQYS = ZYS
Call MSHF
End Sub
Private Sub Command3_Click()
'上一页
Combo1.Text = ""
DQYS = DQYS - 1
If DQYS <= 1 Then DQYS = 1
Call MSHF
End Sub
Private Sub Command4_Click()
'最后一页
Combo1.Text = ""
DQYS = ZYS
Call MSHF
End Sub
Private Sub Command5_Click()
End
End Sub
Private Sub Form_Load()
Combo1.Text = ""
MYJL = 15 '每页显示的记录数,根据你的需要修改即可
Call KKK(db)
RS.Open "select * from 数据表1 ", db, 3, 2
ZJL = RS.RecordCount '得到数据表的总记录
RS.Close
Set RS = Nothing
db.Close
Set db = Nothing
ZYS = ZJL \ MYJL '得到页数,ZYS定义的是整形数据,而且运算符是整除,因此不会有小数,也不会四舍五入
If ZJL Mod MYJL = 0 Then '判断页数是否是整数
ZYS = ZYS '是整数,页数正确
Else
ZYS = ZYS + 1 '不是整数,页数+1
End If
DQYS = 1
Call MSHF
Combo1.Clear
For I = 1 To ZYS
Combo1.AddItem I
Next I
End Sub
Private Sub MSHF() '在 MSHFlexGrid1 控件显示记录过程
Label2.Caption = ""
Label3.Caption = ""
Label4.Caption = ""
Dim JLS As Integer
If DQYS < ZYS Then
JLS = MYJL '如果显示页数小于总页数,显示记录是每页应该显示的记录
ElseIf DQYS = ZYS Then
JLS = ZJL - (DQYS - 1) * MYJL '如果显示页数等于总页数,显示记录是总记录减去已经显示的记录,即显示最后一页剩余的记录
End If
MSHFlexGrid1.Clear
MSHFlexGrid1.Visible = True
With MSHFlexGrid1 '这个块的代码就不需要注释了吧
.Cols = 5
.Rows = JLS + 1
.TextMatrix(0, 0) = Space(1) & "序号"
.TextMatrix(0, 1) = Space(5) & "名称"
.TextMatrix(0, 2) = Space(5) & "规格"
.TextMatrix(0, 3) = Space(3) & "数量"
.TextMatrix(0, 4) = Space(5) & "单位"
.ColWidth(0) = 800
.ColWidth(1) = 1800
.ColWidth(2) = 1800
.ColWidth(3) = 1000
.ColWidth(4) = 1800 '7695
Call KKK(db)
RS.Open "select * from 数据表1 ", db, 3, 2
For I = 1 To (DQYS - 1) * MYJL
RS.MoveNext '移动记录集指针到相应页数的第一条记录
Next I
For I = 1 To JLS
.TextMatrix(I, 0) = I
.TextMatrix(I, 1) = RS!名称
.TextMatrix(I, 2) = RS!规格
.TextMatrix(I, 3) = RS!数量
.TextMatrix(I, 4) = RS!单位
RS.MoveNext
Next I
RS.Close
Set RS = Nothing
db.Close
Set db = Nothing
End With
Label2.Caption = ZJL '在标签显示总记录数
Label3.Caption = ZYS '在标签显示总页数
Label4.Caption = DQYS '在标签显示当前页数
End Sub