我的SQL查询的应用程序怎么总是会崩溃呢?
而其总是在执行SqlPm过程时崩溃
简单介绍一下应用程序的执行过程:
1.在用户选择连接好数据库
2.选择查询模板
3.选择时间段
4.生成查询队列(批量查询)
5.执行查询(结果输出到文本)
该部分通过TIMER控件 控制查询执行的开始时间。现在的问题是:在执行3次或者更多的查询时,应用程序突然跳出错误,里面的详细信息看不懂,只知道涉及ntdll.dll。出现错误时应该都是运行在SqlPm模块。SqlPm模块我在其他程序中也用过,从来没有出现过错误的。哪位高手能够指点一下?!
而其总是在执行SqlPm过程时崩溃
简单介绍一下应用程序的执行过程:
1.在用户选择连接好数据库
2.选择查询模板
3.选择时间段
4.生成查询队列(批量查询)
5.执行查询(结果输出到文本)
该部分通过TIMER控件 控制查询执行的开始时间。现在的问题是:在执行3次或者更多的查询时,应用程序突然跳出错误,里面的详细信息看不懂,只知道涉及ntdll.dll。出现错误时应该都是运行在SqlPm模块。SqlPm模块我在其他程序中也用过,从来没有出现过错误的。哪位高手能够指点一下?!
If frmMain.MSFlexGrid2.TextMatrix(ActiveQuery, 2) > Now - 1 / 24 Then '等待OMC数据时间
Exit Sub
End If
If Minute(Now) < StartTime Or Minute(Now) > EndTime Then '等待可执行时间段
Exit Sub
End If
DoQuery '执行查询End Sub
***********************************************Sub DoQuery()
Dim i, j As Long
Dim SqlString As String
Dim DateTime As String
Dim Fn As String
Dim key(0) As String
Dim Obj As Object
Dim FileObj As Object
Dim StartTime As Long
Dim EndTime As Long
Dim SqlRs As Variant
Dim LastTimeIdx As Integer
Dim TimeCond As String
Dim ActiveCn As ADODB.Connection
frmMain.Timer1.Enabled = False
Set Obj = CreateObject("Scripting.FileSystemObject")
With frmMain.MSFlexGrid2
If .TextMatrix(ActiveQuery, 3) <> "完成" Then
On Error GoTo quitme
.TextMatrix(ActiveQuery, 3) = "取数"
.TextMatrix(ActiveQuery, 4) = Now
.Refresh
Open .TextMatrix(ActiveQuery, 7) For Binary As #1
SqlString = Space(LOF(1))
Get #1, , SqlString
Close #1
TimeCond = .TextMatrix(ActiveQuery, 2)
TimeCond = "= '" & TimeCond & "'"
SqlString = CreateSqlString(key, "", "", TimeCond, 1, SqlString)
Fn = App.Path & "\Output\" & OnlineCn(.TextMatrix(ActiveQuery, 0)).Dsn & "_" & Obj.getbasename(.TextMatrix(ActiveQuery, 7)) & "_" & Format(.TextMatrix(ActiveQuery, 2), "yyyymmddhh") & ".txt"
LastTimeIdx = LastTimeCheck(OnlineCn, .TextMatrix(ActiveQuery, 0))
If LastTimeIdx <> -1 Then
OnlineCn(LastTimeIdx).Cn.Close
OnlineCn(LastTimeIdx).Cn.Open
OnlineCn(LastTimeIdx).LastTime = Now
End If
Set ActiveCn = OnlineCn(.TextMatrix(ActiveQuery, 0)).Cn
SqlPm SqlString, ActiveCn, False, Fn
End If
frmMain.Caption = "【" & ActiveQuery & "/" & .Rows - 1 & "】统计收集"
End WithActiveQuery = ActiveQuery + 1
If ActiveQuery > QueryNum - 1 Then '判断是否完成
frmMain.Command5.Caption = "执行"
frmMain.Command1.Enabled = True
Exit Sub
End If
frmMain.Timer1.Enabled = TrueExit Sub
quitme:
Close
frmMain.MSFlexGrid2.TextMatrix(i, 3) = "Error:" & Err.Number & " " & Err.Description
frmMain.MSFlexGrid2.Refresh
End Sub
**************************************************
Public Sub SqlPm(strSql As String, Cn As ADODB.Connection, Transposed As Boolean, FileName As String)
Dim strCn As String '字符串变量
Dim TmpRslt As Variant
Dim Rslt() As Variant
Dim EndRow As Long
Dim EndClmn As Long
Dim ClmnName() As Variant
Dim YN As Integer
Dim i As Long
Dim j As LongDim rs As New ADODB.Recordset '定义记录集对象,保存数据表
'定义数据库链接字符串
On Error GoTo quitme
'############################# 执行命令 ###########################
Set rs = Cn.Execute(strSql)
If rs.State = 0 Then '非查询语句,rs没有结果
frmMain.MSFlexGrid2.TextMatrix(ActiveQuery, 3) = "完成"
frmMain.MSFlexGrid2.TextMatrix(ActiveQuery, 5) = Now
frmMain.MSFlexGrid2.TextMatrix(ActiveQuery, 6) = Round((Now - CDate(frmMain.MSFlexGrid2.TextMatrix(ActiveQuery, 4))) * 3600 * 24) & "s"
frmMain.MSFlexGrid2.Refresh
Exit Sub
End If
'############################# 获取查询结果 ###########################
TmpRslt = rs.GetRows
EndRow = UBound(TmpRslt)
EndClmn = UBound(TmpRslt, 2)
'############################# 合并字段名和结果数组 ###########################
If Transposed = False Then '记录以行形式显示
ReDim Rslt(EndClmn + 1, EndRow)
For i = 0 To EndRow
Rslt(0, i) = rs.Fields(i).Name
Next
For i = 0 To EndClmn
For j = 0 To EndRow
Rslt(i + 1, j) = TmpRslt(j, i)
Next
Next
Else '记录以列形式显示
ReDim Rslt(EndRow, EndClmn + 1)
For i = 0 To EndRow
Rslt(i, 0) = rs.Fields(i).Name
Next
For i = 0 To EndRow
For j = 0 To EndClmn
Rslt(i, j + 1) = TmpRslt(i, j)
Next
Next
End If
EndRow = UBound(Rslt)
EndClmn = UBound(Rslt, 2)
rs.Close
Set rs = Nothing
'############################# 输出到文件 ###########################
OutputTextFile Rslt, True, Transposed, FileName
Erase ClmnName, Rslt, TmpRslt
frmMain.MSFlexGrid2.TextMatrix(ActiveQuery, 3) = "完成"
frmMain.MSFlexGrid2.TextMatrix(ActiveQuery, 5) = Now
frmMain.MSFlexGrid2.TextMatrix(ActiveQuery, 6) = Round((Now - CDate(frmMain.MSFlexGrid2.TextMatrix(ActiveQuery, 4))) * 3600 * 24) & "s"
frmMain.MSFlexGrid2.Refresh
Exit Sub
quitme:
If rs.State = 1 Then
rs.Close
Set rs = Nothing
End If
frmMain.MSFlexGrid2.TextMatrix(ActiveQuery, 3) = "Error:" & Err.Number & " " & Err.Description
frmMain.MSFlexGrid2.TextMatrix(ActiveQuery, 5) = Now
frmMain.MSFlexGrid2.TextMatrix(ActiveQuery, 6) = Round((Now - CDate(frmMain.MSFlexGrid2.TextMatrix(ActiveQuery, 4))) * 3600 * 24) & "s"
frmMain.MSFlexGrid2.Refresh
End Sub******************************************************
Sub OutputTextFile(PMArray As Variant, SaveField As Boolean, Transposed As Boolean, FileName As String)Dim RsltClmn() As Variant
Dim YN As Integer
Dim FileObj As Variant
Dim EndRow As Long
Dim EndClmn As Long
Dim i As Long
Dim j As Long
Dim OfstI As Long
Dim OfstJ As LongEndRow = UBound(PMArray)
EndClmn = UBound(PMArray, 2)If SaveField = 0 Then '是否写入字段
If Transposed Then
OfstJ = 1
Else
OfstI = 1
End If
End If
ReDim RsltClmn(EndClmn - OfstJ)
Open FileName For Output As #1
For i = OfstI To EndRow
For j = OfstJ To EndClmn
If IsNull(PMArray(i, j)) Then
RsltClmn(j - OfstJ) = ""
Else
RsltClmn(j - OfstJ) = PMArray(i, j)
End If
Next
Print #1, Join(RsltClmn, Chr(9))
Next
Close #1
Erase RsltClmn
End Sub