使用变量输入表值

Question

我不能使用变量作为表的vale，尽管直接值是存储的。我得到的错误是“INSERT INTO Statement.How中的语法错误我能克服这个吗？”

sDBPAth = App.Path & "\SETMDBPATH.mdb"
sConStr = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                  "Data Source=" & sDBPAth & ";" & _
                 "Jet OLEDB:Engine Type=4;"
                 ' Type=4 to create an Access97 mdb, If omitted Access2000 mdb

' ------------------------
' Create New ADOX Object
' ------------------------
 Set oDB = New ADOX.Catalog
 oDB.Create sConStr

 Set oCn = New ADODB.Connection
 oCn.ConnectionString = sConStr
 oCn.Open

 Set oCM = New ADODB.Command
 oCM.ActiveConnection = oCn
 oCM.CommandText = "CREATE TABLE MDBPATH(" & _
        "MDBPATH TEXT(40)      NOT NULL," & _
        "pass  TEXT(10))"
 oCM.Execute

' Populate the table.
 oCn.Execute "INSERT INTO MDBPATH VALUES (sDBPAth, 1,)" 'If 'sDBPath' is used the word sDBPath is stored not the variable value
'
' ------------------------
' Release / Destroy Objects
' ------------------------
 If Not oCM Is Nothing Then Set oCM = Nothing
 If Not oCn Is Nothing Then Set oCn = Nothing
 If Not oDB Is Nothing Then Set oDB = Nothing

 End Sub

Answer 1

您需要修改您的SQL语句以允许应用程序替换sDBPath值

' Populate the table. 
 oCn.Execute "INSERT INTO MDBPATH VALUES ('" & sDBPAth & "', 1)"

Answer 2

试一试

' Populate the table.
 oCn.Execute "INSERT INTO MDBPATH VALUES ('" & sDBPAth & "', 1)"

您在末尾有一个额外的逗号，并且您需要在字符串之外传递变量。

在这种情况下，由于没有传入用户输入，因此相对来说比较节省，但如果继续构建像上面这样INSERT语句，就很容易受到SQL Injection attacks.的攻击

Answer 3

您需要在字符串之外调用该变量。

oCn.Execute "INSERT INTO MDBPATH VALUES ('" & sDBPAth & "', 1)"