CODEWITHSUNDEEP
sqlvbams-access
VLados
VLados

Reputation: 41

how to use a integer variable inside SQL INSERT

Cannot insert a variable using INSERT in MS ACCESS.

the insert works great when I insert a hard-coded value such as

 dbs.Execute "INSERT INTO Flights " _
                 & "(FlightID) VALUES " _
                 & "('2');"

But fails with a variable 

Private Sub MainSaveBtn_Click()
    Dim dbs As Database
    Set dbs = CurrentDb
    Dim id As Integer

    id = 20
    Debug.Print id
    dbs.Execute "INSERT INTO Flights " _
                 & "(FlightID) VALUES " _
                 & "('id');"
    dbs.Close

End Sub

*Flights table has only one Integer colum named FlightID

What am i missing here?

Upvotes: 1

Views: 3085

Answers (3)

Md. Suman Kabir
Md. Suman Kabir

Reputation: 5453

Do it like below :

Private Sub MainSaveBtn_Click()
    Dim dbs As Database
    Set dbs = CurrentDb
    Dim id As Integer

    id = 20
    Debug.Print id
    dbs.Execute "INSERT INTO Flights " _
                 & "(FlightID) VALUES " _
                 & "(" & CStr(id) & ");"
    dbs.Close

End Sub

Also the one worked for you is not fully correct! It should be without the single quotation as the column type is Integer like below :

 dbs.Execute "INSERT INTO Flights " _
                 & "(FlightID) VALUES " _
                 & "(2);"

Upvotes: 2

Bouke
Bouke

Reputation: 1591

The 'id' should be a integer variable and not the string 'id'. The correct SQL statement is listed below.

dbs.Execute "INSERT INTO Flights " _
          & "(FlightID) VALUES " _
          & "(" & CStr(id) & ");"

Upvotes: 1

TotoNaBendo
TotoNaBendo

Reputation: 33

It look like you are trying to insert "id" in the database. Try to concat your id in the String with the "+" symbol. Or just use a prepared statement to inject your variables in

Upvotes: 0

Related Questions