Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Variable Error connecting sql server 2005

Posted on 2007-10-15
Medium Priority
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
  • 2
  • 2
LVL 16

Expert Comment

ID: 20079132
INSERT INTO[MSP_PMO_Teste]([id])VALUES('" & a & "')
LVL 143

Expert Comment

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

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

Expert Comment

ID: 20079136
MyDatabase.Execute "INSERT INTO[MSP_PMO_Teste]([id])VALUES('" & a & "')"
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 200 total points
ID: 20079139
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...)

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Look below the covers at a subform control , and the form that is inside it. Explore properties and see how easy it is to aggregate, get statistics, and synchronize results for your data. A Microsoft Access subform is used to show relevant calcul…
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

580 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