Variable Error connecting sql server 2005

When I run the code below I get a runtime error saying: "The name "&a&" is not permited in this context. Valid expressions are....."

 Dim MyDatabase As ADODB.Connection
    Dim MyCommand As ADODB.Command
    Dim MyRecordSet As ADODB.Recordset
    Dim connStr As String
    Dim a As Integer
    a = 2
    connStr = "Provider=SQLNCLI;Server=caronte;Database=ProjectServer_Reporting;Trusted_Connection=yes;"
    Set MyDatabase = New ADODB.Connection
    MyDatabase.CursorLocation = adUseClient
    'MyDatabase.Properties("Prompt") = adPromptAlways
    MyDatabase.Open (connStr)
    MyDatabase.Execute "INSERT INTO[MSP_PMO_Teste]([id])VALUES(""&a&"")"

How can I insert variables inside sql statements?

Thanks in advance!
Who is Participating?
Guy Hengel [angelIII / a3]Billing EngineerCommented:
as the value (a) is a numerical one, you don't need quotes...
neither single quotes (for strings) nor double quotes (for table and column names...)
INSERT INTO[MSP_PMO_Teste]([id])VALUES('" & a & "')
Guy Hengel [angelIII / a3]Billing EngineerCommented:
yes, like this:

    MyDatabase.Execute "INSERT INTO[MSP_PMO_Teste]([id])VALUES(" & a & ")"
MyDatabase.Execute "INSERT INTO[MSP_PMO_Teste]([id])VALUES('" & a & "')"
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.