vb6 - 我对这个可视化基本程序的理解正确吗？

Question

我以前从未在 Visual Basic 中编程过，我的老板只是让我编辑这个 Visual Basic 程序。该程序从文件列表中读取，并将其中的数据(一些数字)放入我的老板 sql 数据库中。他要我编辑这个程序，所以如果这些文件中的数字超出范围，程序将忽略这些文件。我试图了解程序是如何做到这一点的:

Dim totalVolume1 As Integer, totalVolume2 As Integer, nLane1 As Integer, nLane2 As Integer
Dim totalVolSpeed1 As Double, totalVolSpeed2 As Double
Dim totalSpeed1 As Double, totalSpeed2 As Double, totalOccu1 As Double, totalOccu2 As Double
Dim nRunNum As Integer, NumberOfRunPerMinute As Integer, nWaitTime As Long, RetVal As Long
Dim nFileDir As Integer
Dim Sqlcommand1 As String, Sqlcommand2 As String
Dim FileSize() As Long
Dim FTP_DateTime As Date
NumberOfRunPerMinute = 3


'For nRunNum = 1 To NumberOfRunPerMinute
Do While True
    'OutputTxt "Running the " & nRunNum & " time(s) of this minute."

    nFileNum1 = FreeFile
    Open "c:\" & FileSQLRegular For Output As #nFileNum1
    nFileDir = FreeFile
    Open "c:\" & FileSQLDirection & "" For Output As #nFileDir
    OutputTxt "Start to look for files to import."
    cFileList = LookForFiles(cFileBaseFolder, "*.txt")
    OutputTxt "Finished looking for new files."
    Erase FileSize
    ReDim FileSize(LBound(cFileList) To UBound(cFileList))
    OutputTxt "Start to check file lengths."
    For i = 1 To UBound(cFileList)
        FileSize(i) = FileLen(MyDir(cDataFolder) & cFileList(i))
    Next
    OutputTxt "Done checking file lengths."


    For i = 1 To UBound(cFileList)
        Debug.Print i
        nFileNum2 = FreeFile
        OutputTxt "Start to process file '" & cFileList(i) & "'"
        Open cFileBaseFolder & cFileList(i) For Input As #nFileNum2
        FTP_DateTime = FileDateTime(cFileBaseFolder & cFileList(i))
        OutputTxt "DateTime of file '" & cFileList(i) & "' is '" & Format(FTP_DateTime, "yyyy-mm-dd hh:mm:ss") & "'"
        If Not EOF(nFileNum2) Then
            Line Input #nFileNum2, TmpString
            nDetID = Val(Right(TmpString, Len(TmpString) - 4))
            Do While Not EOF(nFileNum2)
                detData = RetrieveData(nFileNum2, nDetID)
                If Not detData.DayID = 0 Then
                    With detData
                        If .ValidattionID = 1 Then
                            OutputTxt "Retrieved 1 record!"

                            totalVolume1 = 0
                            totalVolume2 = 0
                            totalVolSpeed1 = 0
                            totalVolSpeed2 = 0
                            totalOccu1 = 0
                            totalOccu2 = 0
                            nLane1 = 0
                            nLane2 = 0
                            For j = 1 To nDetectorCount(.DetID)
                                TmpDataID = CStrN(.YearID, 4) & CStrN(.DayID, 3) & CStrN(.SecondTimeID, 5) & CStrN(.DetID, 4) & CStrN(j, 2)
                                SqlCommand = _
                                    "@""" & Format(.Date, "yyyy-mm-dd hh:mm:ss") & """,""" & .YearID & """,""" & .DayID & """,""" & .SecondTimeID & _
                                    """,""" & .DetID & """,""" & j & """,""" & .Speed(j) & """,""" & .Volume(j) & _
                                    """,""" & .Occupancy(j) & """,""" & TmpDataID & """,""" & Format(FTP_DateTime, "yyyy-mm-dd hh:mm:ss") & """;"
                                Print #nFileNum1, SqlCommand

                                If .Speed(j) >= 0 And .Speed(j) <= 90 Then
                                    If DetLaneDir(.DetID, j) = 1 Then
                                        totalVolume1 = totalVolume1 + .Volume(j)
                                        totalVolSpeed1 = totalVolSpeed1 + .Volume(j) * 1# * .Speed(j)
                                        totalOccu1 = totalOccu1 + .Occupancy(j)
                                        nLane1 = nLane1 + 1
                                    ElseIf DetLaneDir(.DetID, j) = 2 Then
                                        totalVolume2 = totalVolume2 + .Volume(j)
                                        totalVolSpeed2 = totalVolSpeed2 + .Volume(j) * 1# * .Speed(j)
                                        totalOccu2 = totalOccu2 + .Occupancy(j)
                                        nLane2 = nLane2 + 1
                                    End If
                                End If
                            Next

                            If totalVolume1 = 0 Then
                                totalOccu1 = 0
                                totalSpeed1 = 240
                            Else
                                totalOccu1 = totalOccu1 / nLane1
                                totalSpeed1 = totalVolSpeed1 / totalVolume1
                            End If

                            If totalVolume2 = 0 Then
                                totalOccu2 = 0
                                totalSpeed2 = 240
                            Else
                                totalOccu2 = totalOccu2 / nLane2
                                totalSpeed2 = totalVolSpeed2 / totalVolume2
                            End If

                            TmpDataID1 = CStrN(.YearID, 4) & CStrN(.DayID, 3) & CStrN(.SecondTimeID, 5) & CStrN(.DetID, 4) & CStrN(1, 2)
                            TmpDataID2 = CStrN(.YearID, 4) & CStrN(.DayID, 3) & CStrN(.SecondTimeID, 5) & CStrN(.DetID, 4) & CStrN(2, 2)
                            Sqlcommand1 = "@""" & Format(.Date, "yyyy-mm-dd hh:mm:ss") & """,""" & .YearID & """,""" & .DayID & """,""" & .SecondTimeID & _
                                    """,""" & .DetID & """,""" & 1 & """,""" & totalSpeed1 & """,""" & totalVolume1 & _
                                    """,""" & totalOccu1 & """,""" & TmpDataID1 & """,""d01"";"
                            Sqlcommand2 = "@""" & Format(.Date, "yyyy-mm-dd hh:mm:ss") & """,""" & .YearID & """,""" & .DayID & """,""" & .SecondTimeID & _
                                    """,""" & .DetID & """,""" & 2 & """,""" & totalSpeed2 & """,""" & totalVolume2 & _
                                    """,""" & totalOccu2 & """,""" & TmpDataID2 & """,""d01"";"
                            Print #nFileDir, Sqlcommand1
                            Print #nFileDir, Sqlcommand2
                        Else
                            'MsgBox "Not validated!"
                            Debug.Print "Not validated!"
                        End If
                    End With
                End If
            Loop
        End If
        Close (nFileNum2)
    Next
    Close (nFileNum1)
    Close (nFileDir)
    If FileLen("c:\" & FileSQLRegular & "") > 0 Then
        OutputTxt "Importing data to database real_time_data..."
        cnnMain_New.Execute ("LOAD DATA LOCAL INFILE 'c:\\" & FileSQLRegular & "' INTO TABLE real_time_data FIELDS TERMINATED BY ',' ENCLOSED BY '""' LINES STARTING BY '@' TERMINATED BY ';' (DATE_TIME, YEAR_ID, DAY_ID, SECOND_ID, DET_ID, LANE_ID, SPEED, VOLUME, OCCUPANCY, DATA_ID, PROCESSED_DATE_TIME, DATA_TYPE)")
        OutputTxt "Done importing data to database..."
        OutputTxt "Importing data to database real_time_data_two_day..."
        cnnMain_New.Execute ("LOAD DATA LOCAL INFILE 'c:\\" & FileSQLRegular & "' INTO TABLE real_time_data_two_day FIELDS TERMINATED BY ',' ENCLOSED BY '""' LINES STARTING BY '@' TERMINATED BY ';' (DATE_TIME, YEAR_ID, DAY_ID, SECOND_ID, DET_ID, LANE_ID, SPEED, VOLUME, OCCUPANCY, DATA_ID, PROCESSED_DATE_TIME, DATA_TYPE)")
        OutputTxt "Done importing data to database..."
    Else
        OutputTxt "No file found to process!"
    End If
    If FileLen("c:\" & FileSQLDirection & "") > 0 Then
        OutputTxt "Importing data to database real_time_data_direction..."
        cnnMain_New.Execute ("LOAD DATA LOCAL INFILE 'c:\\" & FileSQLDirection & "' INTO TABLE real_time_data_direction FIELDS TERMINATED BY ',' ENCLOSED BY '""' LINES STARTING BY '@' TERMINATED BY ';' (DATE_TIME, YEAR_ID, DAY_ID, SECOND_ID, DET_ID, Dir_ID, SPEED, VOLUME, OCCUPANCY, DATA_ID)")
        OutputTxt "Done importing data to database..."
        OutputTxt "Importing data to database real_time_data_direction_two_day..."
        cnnMain_New.Execute ("LOAD DATA LOCAL INFILE 'c:\\" & FileSQLDirection & "' INTO TABLE real_time_data_direction_two_day FIELDS TERMINATED BY ',' ENCLOSED BY '""' LINES STARTING BY '@' TERMINATED BY ';' (DATE_TIME, YEAR_ID, DAY_ID, SECOND_ID, DET_ID, Dir_ID, SPEED, VOLUME, OCCUPANCY, DATA_ID)")
        OutputTxt "Done importing data to database..."
    Else
        OutputTxt "No file found for directional data to process!"
    End If
    'cnnMain.Close
    Kill "c:\" & FileSQLRegular & ""
    Kill "c:\" & FileSQLDirection & ""
    MoveFiles cFileList, FileSize

' 
       If nRunNum < NumberOfRunPerMinute Then
'            nWaitTime = CLng((CSng(60) / NumberOfRunPerMinute - 2) * 1000)
'            OutputTxt "Start to wait for " & nWaitTime & " miliseconds."
'            RetVal = MsgWaitObj(nWaitTime)
'            OutputTxt "Finished waiting!"
'        End If
'    'Next
            nWaitTime = 2000
            OutputTxt "Start to wait for " & nWaitTime & " miliseconds."
            RetVal = MsgWaitObj(nWaitTime)
            OutputTxt "Finished waiting!"

这是我到目前为止的理解:在

For i = 1 To UBound(cFileList)

它遍历所有文件。在

For j = 1 To nDetectorCount(.DetID)

它进入每个文件并将数据存储在变量中，例如 totalvolume1、totalspeed1...

 If totalVolume1 = 0 Then
 totalOccu1 = 0
 totalSpeed1 = 240

它做了一些过滤。最后，在

 Sqlcommand1 = "@""" & Format(.Date, "yyyy-mm-dd hh:mm:ss") & """,""" & .YearID & """,""" & .DayID & """,""" & .SecondTimeID & _
                                        """,""" & .DetID & """,""" & 1 & """,""" & totalSpeed1 & """,""" & totalVolume1 & _ 

它将所有的变量放在两个字符串中，Sqlcommand1 和 Sqlcommand2，这样它们就可以放入 sql 数据库中。

到目前为止，我对这个程序的理解是否正确？这个程序然后使用

Print #nFileDir, Sqlcommand1

Print #nFileDir, Sqlcommand2

将这两个字符串放入数据库？

谢谢您的帮助。

这会解决我的问题吗:

  'if data out of range, then they are not written to text file and database
If totalVolume1 < 50 And totalVolume1 > -1 And totalVolume1 < 50 And totalVolume1 > -1 Then
       Print #nFileDir, Sqlcommand1
       Print #nFileDir, Sqlcommand2

 End If

Answer 1

号Print #nFileDir语句将数据写到一个 .csv(逗号分隔值)文件中。 (请注意，这是 真的是 糟糕的代码，即使对于想成为程序员的旧 VB6 也是如此。)

两人相关声明Print你要问的陈述是这样的:

Open "c:\" & FileSQLDirection & "" For Output As #nFileDir

这将创建一个文件编号( #nFileDir 。声明为 Integer )到一个文本文件(打开写入)，该文件在其余代码中使用 Print #nFileDir 写入。声明。然后使用 cnnNew.Execute 将生成的文本文件导入数据库。引用 FileSQLDirection 的声明. (注意 FileSQLDirection 在我上面显示的 Open 调用中。) 注意 :我提到的“文件编号”不是 Win32 文件句柄，并且与 Win32 API 调用或需要它的其他用途不兼容。请参阅下面的 Bob Riemersma 的评论。

为了做出你的老板要求的改变，你需要修正这两个之前的陈述 Print语句，以便它们仅在数据位于您的老板希望数据的范围内时才执行。在将文本分配给 SqlCommand1 之前，您需要检查这些值。和 SQLCommand2和 Print将它们写入文本文件的语句，因此无效值永远不会到达文本文件。