row自动增加行数 运行慢 还会死机
我读取sql数据库 本来想做分页 没做出来 就想flex2.Rows = flex2.Rows + 1 一次查询有1万多条信息 运行慢 还会死机谁有好一点的办法
版主在吗 拜求指点了
Dim PageNow As Long
Dim PageMax As Long
Private Sub cmdfri_Click()
PageNow = 1
Call flex2PullFY(flex2, ssql1, 6, PageNow)
Call flex2PullFY(flex2, ssql2, 6, PageNow)
Label1.Caption = "共" & PageMax & "页 当前是第 " & PageNow & " 页"
End Sub
' 分页显示内容
Private Sub cmdlas_Click()
PageNow = PageMax
Call flex2PullFY(flex2, ssql1, 6, PageNow)
Call flex2PullFY(flex2, ssql2, 6, PageNow)
Label1.Caption = "共" & PageMax & "页 当前是第 " & PageNow & " 页"
End Sub
Private Sub cmdNext_Click()
If PageNow < PageMax Then
PageNow = PageNow + 1
Call flex2PullFY(flex2, ssql1, 6, PageNow)
Call flex2PullFY(flex2, ssql2, 6, PageNow)
Label1.Caption = "共" & PageMax & "页 当前是第 " & PageNow & " 页"
Else
MsgBox "超出范围"
End Sub
Private Sub cmdPre_Click()
If PageNow > 1 Then
PageNow = PageNow - 1
Call flex2PullFY(flex2, ssql1, 6, PageNow)
Call flex2PullFY(flex2, ssql2, 6, PageNow)
Label1.Caption = "共" & PageMax & "页 当前是第 " & PageNow & " 页"
Else
MsgBox "超出范围"
End If
End Sub
Private Sub Command2_Click()
On Error GoTo 0
Dim sPro, sSer, sCon, sDsn, DSNName
Dim conn1, oRs1, oCom1, sSql1, m, conn2, oRs2, oCom2, sSql2
' Dim conn1, oRs1, oCom1, sSql1, m,
Dim oList, oItem, i, j
Dim WinCC As Object
Set WinCC = CreateObject("WinCC-Runtime-Project")
Dim database
database = WinCC.GetValue("@DatasourceNameRT")
sPro = "Provider=WinCCOLEDBProvider.1;"
sDsn = "Catalog= " & database & ";"
sSer = "Data Source=.\WinCC;User ID=sa;"
sCon = sPro & sDsn & sSer
Text1.Text = DateAdd("h", -8, DTPicker1)
Text2.Text = DateAdd("h", -8, DTPicker2)
sSql1 = "TAG:R,('PV\F1'),'" & Text1.Text & "','" & Text2.Text & "'"
sSql2 = "TAG:R,('PV\F2'),'" & Text1.Text & "','" & Text2.Text & "'"
Set conn1 = CreateObject("ADODB.Connection")
conn1.ConnectionString = sCon
conn1.CursorLocation = 3
conn1.Open
Set oRs1 = CreateObject("ADODB.Recordset")
Set oCom1 = CreateObject(")
= 1
Set oCom1.ActiveConnection = conn1
= sSql1
Set oRs1 = oCom1.Execute
Set conn2 = CreateObject("ADODB.Connection")
conn2.ConnectionString = sCon
conn2.CursorLocation = 3
conn2.Open
Set oRs2 = CreateObject("ADODB.Recordset")
Set oCom2 = CreateObject(")
= 1
Set oCom2.ActiveConnection = conn2
= sSql2
Set oRs2 = oCom2.Execute
flex2.Clear
If (Not oRs1.EOF) Then
oRs1.MoveFirst
i = 1
Do While Not oRs1.EOF
flex2.TextMatrix(i, 1) = i
flex2.TextMatrix(i, 2) = DateAdd("h", 8, oRs1.Fields(1).Value)
flex2.TextMatrix(i, 3) = CStr(oRs1.Fields(2).Value)
flex2.TextMatrix(i, 4) = CStr(oRs2.Fields(2).Value)
oRs1.MoveNext
oRs2.MoveNext
i = i + 1
Loop
oRs1.Close
oRs2.Close
End If
Call flex2PullFY(flex2, ssql1, 6, PageNow)
Call flex2PullFY(flex2, ssql2, 6, PageNow)
PageNow = 1
Label1.Caption = "共" & PageMax & "页 当前是第 " & PageNow & " 页"
ConnectErr:
flex2.Refresh
Set oRs1 = Nothing
Set oCom1 = Nothing
Set conn1 = Nothing
Set oRs2 = Nothing
Set oCom2 = Nothing
Set conn2 = Nothing
End Sub
Private Sub Form_Activate()
flex2.Cols = 5
'设置列标头。
s$ = "^|^序号 |^ 时间 |^ 压力 |^ 温度 "
flex2.FormatString = s$
End Sub
'=====================================================================
'填充指定表单的内容MSFlexG表单名称，MYSQL查询语句，PageSize每页记录数，PageN显示指定页
Public Function flex2PullFY(flex2 As MSHFlexGrid, MYSQL As String, PageSize As Integer, PageN As Long)
BooNull = False
Dim Tmpi, Tmpj As Long
'以下部分进行数据表打开操作===========================================
sCon.Open MYSQL, sCon, adOpenKeyset, adLockReadOnly, adCmdText
'=====================================================================
'如果数据不为空，则进行读取操作=======================================
'数据显示窗口的行数等于记录集记录数加上固定1行（数据名）==============
'数据显示窗口的列数等于记录集表的数据项个数减去需要隐藏的数据项=======
'（固定行默认是记录集中第一个字段）===================================
If sCon.BOF And sCon.EOF Then
' MsgBox "数据库中无符合条件的记录", , "提示"
flex2.Clear
flex2.Rows = 0
flex2.Cols = 0
Else
sCon.PageSize = PageSize
PageMax = sCon.PageCount
sCon.MoveLast
sCon.MoveFirst
flex2.Rows = sCon.PageSize + 1
flex2.Cols = sCon.PageSize + 1
If sCon.RecordCount > 3000 Then MsgBox "读取数据量较大，请耐心等待", , "提示"
sCon.AbsolutePage = PageN
For Tmpi = 1 To sCon.PageSize
flex2.TextMatrix(Tmpi, 0) = Str(Tmpi)
For Tmpj = 1 To sCon.Fields.Count
If IsNull(Trim(sCon.Fields(Tmpj - 1).Value)) Then '控制填充内容不能为空
flex2.TextMatrix(Tmpi, Tmpj) = ""
Else
flex2.TextMatrix(Tmpi, Tmpj) = Trim(sCon.Fields(Tmpj - 1).Value)
End If
Next Tmpj
sCon.MoveNext
Next Tmpi
sCon.Close
Set sCon = Nothing
End If
flex2.Refresh
End Function