how to use a integer variable inside SQL INSERT

Question

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?

You are trying to insert the text 'id' into an integer column. — pritaeas
– pritaeas, Commented Jul 31, 2018 at 11:35
Are Flight and ID fields in a table or variables in your code? — ashleedawg
– ashleedawg, Commented Jul 31, 2018 at 11:35
id is an integer variable im trying to insert its value in the table — VLados
– VLados, Commented Jul 31, 2018 at 11:45

Md. Suman Kabir · Accepted Answer · 2018-07-31 11:55:26Z

2

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);"

answered Jul 31, 2018 at 11:55

Md. Suman Kabir

5,4515 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bouke · Accepted Answer · 2018-07-31 11:48:43Z

1

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) & ");"

answered Jul 31, 2018 at 11:48

Bouke

1,6151 gold badge16 silver badges26 bronze badges

3 Comments

Bouke Over a year ago

Feeling stupid means you really understand and is actually a good thing. Please mark the question as answered.

VLados Over a year ago

Bouke. I have a problem with understanding the syntax.. How would the last statement change if i want to add an additional string variable holding a name to this INSERT?

Bouke Over a year ago

Than it would be something like this: dbs.Execute "INSERT INTO Flights " _ & "(FlightID, FlightName) VALUES " _ & "(" & CStr(id) & ",'" & FlightNameVariable & "');" Note that FlightNameVariable should be surrounded by single quotes, because it's a string value. Basic SQL syntax is as follows: INSERT INTO TableName(Column1, Column2) VALUES (ValueColumn1, ValueColumn2)

TotoNaBendo · Accepted Answer · 2018-07-31 11:42:37Z

0

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

edited Jul 31, 2018 at 11:42

answered Jul 31, 2018 at 11:37

TotoNaBendo

338 bronze badges

Collectives™ on Stack Overflow

how to use a integer variable inside SQL INSERT

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related