Use variable to enter into table values

Question

I am unable to use a variable as the vale for a table, though direct values are stored. I get the error as "Syntax error in INSERT INTO Statement.How do I overcome this ?

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

Try

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

You had an extra comma at the end, and you need to pass the variable in outside of the string.

You're relatively save in this case since you're not pass in a user input, but if you continue to build INSERT statements like above, you'll be vulnerable to SQL Injection attacks.

Answer 2

You need to call the variable outside of the string.

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

Answer 3

If sDBPAth is an actual VB variable, you need to use something like:

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

so that the insert statement is constructed from the value of the variable rather than a fixed string.

In addition, you appear to have a superfluous comma in your statement and you may need quotes around both values since they're both text type.

Answer 4

You need to amend your SQL statement to allow the application to substitute the sDBPath value

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