Link to home
Start Free TrialLog in
Avatar of carlaferreira2
carlaferreira2

asked on

Variable Error connecting sql server 2005

Hi,
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!
Avatar of SQL_SERVER_DBA
SQL_SERVER_DBA
Flag of United States of America image

INSERT INTO[MSP_PMO_Teste]([id])VALUES('" & a & "')
Avatar of Guy Hengel [angelIII / a3]
yes, like this:

    MyDatabase.Execute "INSERT INTO[MSP_PMO_Teste]([id])VALUES(" & a & ")"
MyDatabase.Execute "INSERT INTO[MSP_PMO_Teste]([id])VALUES('" & a & "')"
ASKER CERTIFIED SOLUTION
Avatar of Guy Hengel [angelIII / a3]
Guy Hengel [angelIII / a3]
Flag of Luxembourg image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial