Variable Error connecting sql server 2005

Posted on 2007-10-15
Last Modified: 2010-03-19
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!
Question by:carlaferreira2
    LVL 16

    Expert Comment

    INSERT INTO[MSP_PMO_Teste]([id])VALUES('" & a & "')
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    yes, like this:

        MyDatabase.Execute "INSERT INTO[MSP_PMO_Teste]([id])VALUES(" & a & ")"
    LVL 16

    Expert Comment

    MyDatabase.Execute "INSERT INTO[MSP_PMO_Teste]([id])VALUES('" & a & "')"
    LVL 142

    Accepted Solution

    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...)

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
    Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
    It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
    To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    18 Experts available now in Live!

    Get 1:1 Help Now