Forum Discussion

mrezahoseini's avatar
mrezahoseini
Contributor
10 years ago

send parameters in Delete Query

Hello

 

Thank you in advance for your kind assistance

 

I have a problem in Delete Query, when I 'm gonna use varaiable in Delete Query instead of hard code, like this code, what shall I do??

 

function Delete()

{
var AConnection, Driver;

AConnection = ADO.CreateADOConnection();

AConnection.ConnectionString = "Provider=MSDAORA.1;Password=autocm931;User ID=autocm931;Data Source=srv28;Persist Security Info=True";

AConnection.LoginPrompt = false;

AConnection.Open();

var Driver = DDT.ExcelDriver("C:\\Add-Exchange.xls", "Sheet1");

var k = Driver.Value(0);

AConnection.Execute_("delete from exchange ex where ex.name like k");     // this line has error, because of variable k 

DDT.CloseDriver(Driver.Name);

AConnection.Close();

}

  • mrezahoseini's avatar
    mrezahoseini
    10 years ago

    yes, my problem was solved like this :

     

     

     

    function Delete()

    {
    var AConnection, Driver;
    AConnection = ADO.CreateADOConnection();
    AConnection.ConnectionString = "Provider=MSDAORA.1;Password=autocm931;User ID=autocm931;Data Source=srv28;Persist Security Info=True";
    AConnection.LoginPrompt = false;
    AConnection.Open();
    var Driver = DDT.ExcelDriver("C:\\Add-Exchange.xls", "Sheet1");
    var k = aqConvert.VarToStr(Driver.Value(0));
    var str = "delete from exchange ex where ex.name like '"+k+"'"
    AConnection.Execute_(str);
    DDT.CloseDriver(Driver.Name);
    AConnection.Close();

    }

     

     

    Thanks for ur consideration

  • tonydugay's avatar
    tonydugay
    Contributor
    10 years ago

    pass in k as a parameter to the function and build up the SQL like this:

     

    AConnection.Execute_("delete from exchange ex where ex.name like" +  k);  

  • mrezahoseini's avatar
    mrezahoseini
    Contributor
    10 years ago

    Thanks for your reply, but unfortunately it has an error like this, when it comes to your reply line:

     

    2015-02-24_13-43-46.jpg

    • tonydugay's avatar
      tonydugay
      Contributor
      10 years ago

      maybe the missing space is the issue after like - I have added it....?

       

      also maybe (depending on the DB need to add %k%?

       

      AConnection.Execute("delete from exchange ex where ex.name like " +  k);  

       

      here is a bit of code I use:

       

      Qry.SQL = "select TOP 1 * from _SitesToOwners "

      + "inner join _Sites "

      + "on _Sites.Id = _SitesToOwners.Site_Id "

      + "where _Sites.SiteName" + "  like '%(closed)%' "

      + "and _Sites.RecEndDate "

      + "order by newid()";

       

      • mrezahoseini's avatar
        mrezahoseini
        Contributor
        10 years ago

        yes, my problem was solved like this :

         

         

         

        function Delete()

        {
        var AConnection, Driver;
        AConnection = ADO.CreateADOConnection();
        AConnection.ConnectionString = "Provider=MSDAORA.1;Password=autocm931;User ID=autocm931;Data Source=srv28;Persist Security Info=True";
        AConnection.LoginPrompt = false;
        AConnection.Open();
        var Driver = DDT.ExcelDriver("C:\\Add-Exchange.xls", "Sheet1");
        var k = aqConvert.VarToStr(Driver.Value(0));
        var str = "delete from exchange ex where ex.name like '"+k+"'"
        AConnection.Execute_(str);
        DDT.CloseDriver(Driver.Name);
        AConnection.Close();

        }

         

         

        Thanks for ur consideration

  • jorgesimoes1983's avatar
    jorgesimoes1983
    Regular Contributor
    10 years ago

    I have this function to execute querys, maybe you can adjust it to suite your needs. You have also, example of usage, may you solve your problem dealing with k variable by looking at it :)

     

    e created this function to executed SQL Querys, maybe it is useful for you.

    Example of usage:

     

    sqlQueryExecute(server, "update "+database+"table set table_name= 'something' where = 1")

     

    /**
     * @method sqlQueryExecute
     * @param {string} server IP
     * @param {string} query to execute
     */
    function sqlQueryExecute(server, query)
    {
      Log.Message("IF ODBC error... check your firewall for SQL Server port 1433 inbound/outbound");
     
      var ConnectionString = "DRIVER=SQL Server;SERVER="+ server +";UID=user;PWD=user";
     
      // Creates a new connection
      var ConnDB = ADO.CreateConnection();
      var adoRecSet = ADO.CreateRecordset();
      ConnDB.ConnectionString = ConnectionString;
      ConnDB.Open();
     
      // Opens a recordset
      ConnDB.Execute(query);
      Log.Checkpoint("QUERY EXECUTED")
      // Closes the connection  
      ConnDB.Close();
    }

     

     

    Just a question... if you replace directly k by a value for instance 'text', does it work?

    If it does work, debug your variable assignment Add Watch --> Driver.Value(0);