1 如何在datagrid列表中显示数据库中的数据?
Private Sub ShowData1(sqlstr As String)
Dim Cn As New ADODB.Connection
Dim Cmd As New ADODB.Command
Dim rs1 As New ADODB.Recordset
Dim DsRows As Integer
Screen.MousePointer = 11
On Error Resume Next
Cn.CursorLocation = adUseClient
Cn.Open ConStr
Set Cmd.ActiveConnection = Cn
Cmd.CommandText = sqlstr
rs1.Open Cmd, , adOpenKeyset, adLockOptimistic
Dim j As Integer
On Error GoTo ErrMess
DsRows = rs1.RecordCount + 1
Grid1.Cols = rs1.Fields.Count
Grid1.ColWidth(Grid1.Cols - 1) = 1 '列宽为1,不让用户看到
Grid1.Row = 0
For i = 0 To rs1.Fields.Count - 1 '减去最后一列
Grid1.Col = i
Grid1.Text = rs1.Fields(i).Name
Next i
Grid1.Rows = rs1.RecordCount + 1
Grid1.Cols = rs1.Fields.Count
i = 1
Do While Not rs1.EOF
For j = 0 To rs1.Fields.Count - 1
If j = rs1.Fields.Count - 1 Then
Grid1.TextMatrix(i, j) = GetObjId(CStr(rs1(0)))
Else
If Not IsNull(rs1(j).Value) Then
Grid1.TextMatrix(i, j) = CStr(rs1(j))
End If
End If
Next
rs1.MoveNext
i = i + 1
Loop
rs1.Close
Set rs1 = Nothing
Cn.Close
Set Cn = Nothing
StatusBar1.Panels.Item(2).Text = "共有" + " " + Str(DsRows) + " " + "条记录"
StatusBar1.Panels.Item(1).Text = "交调点"
Screen.MousePointer = 1
Exit Sub
ErrMess:
MsgBox ("查询错误,请与系统管理员联系!")
Screen.MousePointer = 1
Set rs1 = Nothing
rs1.Close
Cn.Close
Set Cn = Nothing
End Sub
2 如何给datagrid分页?
'---------分页显示过程---------------------
Public Function EveryRowDisplay(sql As String, formatStr As String, rowSum As Integer, colSum As Integer, StartP As Integer) As String
fMainForm.txtmsg.Visible = True
fMainForm.Map1.MousePointer = miHourglassCursor
Screen.MousePointer = 11
DoEvents
'''''''''''''''''''返回数据总页数
Dim Cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim n As Integer
Dim i As Integer
On Error Resume Next
Cn.CursorLocation = adUseClient
Cn.Open ConStr
rs.CursorLocation = adUseClient
rs.Open sql, Cn, 1, 2
If Not rs.EOF Then
Dim pageSum As Integer '''''取总页数
Dim Rcounts As Long ''''当数据总数小于设定行时取得显示数据的最大行
Dim RsP As Integer '''''获得当前纪录的位置
rs.PageSize = rowSum
Rcounts = rs.RecordCount
If (Rcounts / rowSum) - 1 > 0 Then
pageSum = ((Rcounts - 1) / rowSum) + 1
Else
pageSum = 1
End If
If StartP > rs.PageCount Then
StartP = rs.PageCount
End If
If StartP < 1 Then
StartP = 1
End If
rs.AbsolutePage = StartP
Grid1.Clear
With Grid1
.Cols = rs.Fields.Count
.Rows = rowSum + 1
.Row = 0
For i = 0 To rs.Fields.Count - 1
.Col = i
.Text = rs.Fields(i).Name
Next i
StatusBar1.Panels.Item(2).Text = "共有" + " " + Str(rs.RecordCount) + " " + "条记录"
.Cols = rs.Fields.Count
.Rows = rowSum + 1
For i = 1 To rowSum
For j = 0 To colSum - 1
.TextMatrix(i, j) = rs.Fields(j) & ""
Next
rs.MoveNext
If rs.EOF Then '判断记录是否了到最后一条
GoTo DSPend
End If
Next
End With
Else
Grid1.Clear
Grid1.FormatString = formatStr
End If
DSPend:
rs.Close
Cn.Close
Set rs = Nothing
Set Cn = Nothing
If pageSum = 0 Then
EveryRowDisplay = 0 & "/" & pageSum
Else
EveryRowDisplay = StartP & "/" & pageSum
End If
fMainForm.txtmsg.Visible = False
fMainForm.Map1.MousePointer = miToolTypePoint
Screen.MousePointer = 1
End Function
-----------------第一次调用---------
Label1.Caption = EveryRowDisplay(sqlstr, "", 5000, 35, 1)----------------当前页/总页数
Text1.Text = Split(Trim(Label1.Caption), "/")(0)-------------当前页或要跳转的页
If Trim(Text1.Text) = "0" Then
Command1.Enabled = False
End If
-------------------翻前页------------------
If Split(Trim(Label1.Caption), "/")(0) > 1 Then
Label1.Caption = EveryRowDisplay(sqlstr, "", 5000, 35, Int(Trim(Split(Trim(Label1.Caption), "/")(0) - 1)))
End If
-------------------翻后页------------------
If Int(Split(Trim(Label1.Caption), "/")(0)) < Int(Split(Trim(Label1.Caption), "/")(1)) Then
Label1.Caption = EveryRowDisplay(sqlstr, "", 5000, 35, Int(Trim(Split(Trim(Label1.Caption), "/")(0) + 1)))
End If
-----------------跳转页--------------------
If Trim(Text1.Text) <= Split(Trim(Label1.Caption), "/")(1) And Int(Trim(Text1.Text)) > 0 Then
Label1.Caption = EveryRowDisplay(sqlstr, "", 5000, 35, Int(Trim(Text1.Text)))
End If