Forum Discussion

endorium's avatar
endorium
Frequent Contributor
8 years ago

Using project variable with in SQL query

Hi

 

I am writing some workbook tests. In this on one line I need to insert a line into the DB. Problem is I want to use a project variable to specify the data I want to insert.

 

I have variables for the computer its running on and the DB. I need IPADDRESS to use a project variable.

Any way to do this. I have had a good search and cant find anything.

 

 

"use "........DB"
insert into Listeners (IpAddress,PortNumber,IsDeleted)
Values ('IPADDRESS','50002','False')"

  • tristaanogre's avatar
    tristaanogre
    Esteemed Contributor
    8 years ago

    How are you executing your SQL code? 

     

    If you're using ADO objects, then it's a matter of formatting your SQL Query string to concatenate the variable in the necessary place.

     

    Something like below. The code is untested so I wouldn't copy and paste as is, but notice the concatenated string assignment to Qry.SQL.:

     

    function Testquery()
{
  var Qry;
  // Create a query
  Qry = ADO.CreateADOQuery();
  // Specify the connection string
  Qry.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
  "Data Source=C:\\Microsoft Visual Studio\\VB98\\biblio.mdb";
  // Specify the SQL expression
  Qry.SQL = "insert into Listeners (IpAddress,PortNumber,IsDeleted)
Values (\'" + Project.Variables.IPADDRESS + "\','50002','False')";
  // Execute the query
  Qry.Execute();
  // Closes the query
  Qry.Close();
}