vbscript将数组插入access数据库

Question

我有一个简单的任务和一个复杂的过程。我正在创建一个跟踪程序来跟踪应用程序的使用情况以及其他相关信息。我首先将应用程序中的数据记录在一个临时文本文件中，一旦检索到数据，该文件就会被删除。数据用逗号分隔，以便可以存储在CSV文件中。此CSV文件将用于快速提取特定信息以供审查。这些数据还将永久存储在2010 Access数据库中。我已经能够将数据存储在文本文件中。我已经能够创建一个VBScript来读取文本文件中的数据，并将其复制到CSV文件中。我需要做的是弄清楚为什么，当我在将数据插入数据库的脚本上方放置了一个消息框时，我可以在消息框中看到信息，但它不会打印到数据库，我也不会收到任何错误消息。

这是VBScript代码：

' Set constants for reading, writing, and appending files
Const ForReading = 1, ForWriting = 2, ForAppending = 8
' Sets up the object variables.
Dim objExcel, objFSO, objTextFile, objCSVFile, objTrackingFolder
' Sets up the integer variables.
Dim intPathYPos
' Sets up the all the string variables for the program.
Dim Desktop, todaysDate, usageDate, myDay, myMonth, myYear, UserIDPath, myMessage
Dim strTextFile, strHeadLine, strTextLine, strCSVFile, UserID, strTrackingFolder, strConnect, strSQL, strSplitData, testDisplay
Dim message
'This creates the required Objects
Set objExcel = CreateObject("Excel.application")
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set conn = CreateObject("ADODB.Connection")
Set WshShell = WScript.CreateObject("WScript.Shell")
Desktop = WshShell.ExpandEnvironmentStrings("%USERPROFILE%") & "" & "Desktop"
UserIDPath = WshShell.ExpandEnvironmentStrings("%USERPROFILE%")
'------------------Calls up the Process Tracking Submission sub-------------------
call ProcessTrackingSubmission
sub ProcessTrackingSubmission()
intPathYPos = InStr(4,UserIDPath,"")
intPathYPos = intPathYPos + 1
UserID = Mid(UserIDPath, intPathYPos, 10)
'msgbox(RTrim(UserID))
'exit sub
' Set date for date stamp in file name and sheet name
todaysDate = Date()
myMonth = Month(todaysDate)
If Len(myMonth)=1 Then myMonth="0" & myMonth
myDay = Day(todaysDate)
If Len(myDay)=1 Then myDay="0" & myDay
myYear = Right(Year(todaysDate), 2)
usageDate = myMonth & myDay & myYear
' Set up the origin and destination files
strTextFile = Desktop & "MacroTracker.txt"
strTrackingFolder = "E:My Storage Files" & UserID
strCSVFile = strTrackingFolder & "TrackingTesting" & usageDate & ".csv"
strHeadLine = "App Name,User ID,Ran At,Data 1,Data 2,Data 3,Data 4,Data 5,Data 6,Data 7,Data 8"
Set objTextFile = objFSO.OpenTextFile(strTextFile)
Wscript.Sleep 600
' Read the entire origin file
Do Until objTextFile.AtEndOfStream
    strTextLine = objTextFile.ReadLine
Loop
Wscript.Sleep 600
objTextFile.Close
If (objFSO.FolderExists(strTrackingFolder)) Then
    If (objFSO.FileExists(strCSVFile)) Then
        ' Create object for appending current TXT file to CSV file
        Set objCSVFile = objFSO.OpenTextFile(strCSVFile, ForAppending, True)
    Else
        ' Create CSV file to write to with today's date
        Set objCSVFile = objFSO.CreateTextFile(strCSVFile, True)
        Wscript.Sleep 1000
        ' Write initial header for the CSV file
        objCSVFile.WriteLine strHeadLine
    End If
Else
    Set objTrackingFolder = objFSO.CreateFolder(strTrackingFolder)
    If (objFSO.FileExists(strCSVFile)) Then
        ' Create object for appending current TXT file to CSV file
        Set objCSVFile = objFSO.OpenTextFile(strCSVFile, ForAppending, True)
    Else
        ' Create CSV file to write to with today's date
        Set objCSVFile = objFSO.CreateTextFile(strCSVFile, True)
        Wscript.Sleep 1000
        ' Write initial header for the CSV file
        objCSVFile.WriteLine strHeadLine
    End If
End If
' Write an append line of data to the CSV file
objCSVFile.WriteLine strTextLine
 Wscript.Sleep 600
strDataLine = Split(strTextLine, ",")
strAppName = strDataLine(0)
strUserID = strDataLine(1)
strRanAt = strDataLine(2)
strData1 = strDataLine(3)
strData2 = strDataLine(4)
strData3 = strDataLine(5)
strData4 = strDataLine(6)
strData5 = strDataLine(7)
strData6 = strDataLine(8)
strData7 = strDataLine(9)
strData8 = strDataLine(10)
' Connect to the database
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:My Storage FilesTracking Apps.mdb"
conn.Open strConnect
Wscript.Sleep 600
' Write data to table
    if strAppName = "Hello Application - version 1" Then
strSQL = "INSERT INTO [Macro Tracking] ([App Name], [User ID], [Ran At], [Data 2], [Data 6]) VALUES ([" & strAppName & "], [" & strUserID & "], [" & strRanAt & "], [" & strData2 & "], [" & strData6 & "])"
    end if
Wscript.Sleep 600
objCSVFile.Close
conn.Close
' Wait for file to be written to
Wscript.Sleep 600
' Delete origin file to prevent user tampering
objFSO.DeleteFile(strTextFile)
end sub

如有任何帮助，我们将不胜感激。我使用过HTML数据库，所以我对SQL应该是什么样子有一个想法，但我从未用VBScript写过一个，我在互联网上发现的所有东西都不起作用。

Answer 1

您定义了strSQL，但不执行SQL语句，添加

conn.Execute strSQL

在strSQL = ...行之后

Answer 2

除了roland已经指出的之外，VBScript不会扩展字符串中的变量。"... VALUES (strSplitData(0), ...)"中的strSplitData(0)只是文字字符串"strSplitData(0)"，而不是数组strSplitData的第一个字段的值。您可以通过如下连接来构建查询字符串：

strSQL = "INSERT INTO [Macro Tracking] " & _
         "([App Name], [User ID], [Ran At], [Data 1], [Data 2], " & _
         "[Data 3], [Data 4], [Data 5], [Data 6], [Data 7], [Data 8]) " & _
         "VALUES (" & _
         strSplitData(0) & ", " & _
         strSplitData(1) & ", " & _
         strSplitData(2) & ", " & _
         strSplitData(3) & ", " & _
         strSplitData(4) & ", " & _
         strSplitData(5) & ", " & _
         strSplitData(6) & ", " & _
         strSplitData(7) & ", " & _
         strSplitData(8) & ", " & _
         strSplitData(9) & ", " & _
         strSplitData(10) & ")"

然而，这样做不是一个好主意，所以忘记我提到过了。最好使用参数化查询（AKA prepared语句）：

db = "E:My Storage FilesTrackingApps.mdb"
strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & db & _
             ";User Id=admin;Password=;"
Set conn = CreateObject("ADODB.Connection")
conn.Open strConnect
Set cmd = CreateObject("ADODB.Command")
Set cmd.ActiveConnection = conn
cmd.CommandText = "INSERT INTO [Macro Tracking] ([App Name], [User ID], " & _
  "[Ran At], [Data 1], [Data 2], [Data 3], [Data 4], [Data 5], [Data 6], " & _
  "[Data 7], [Data 8]) VALUES (?,?,?,?,?,?,?,?,?,?,?)"
Set p1 = cmd.CreateParameter("@p1", 3, 1, 0, 0)
cmd.Parameters("@p1") = strSplitData(0)
cmd.Parameters.Append p1
Set p2 = cmd.CreateParameter("@p2", 3, 1, 0, 0)
cmd.Parameters("@p2") = strSplitData(1)
cmd.Parameters.Append p2
...
Set p11 = cmd.CreateParameter("@p11", 3, 1, 0, 0)
cmd.Parameters("@p11") = strSplitData(10)
cmd.Parameters.Append p11
cmd.Execute

根据需要调整CreateParameter()调用的参数。