SPONSORS:






User Tag List

Thanks Thanks:  0
Likes Likes:  0
Dislikes Dislikes:  0
Results 1 to 6 of 6
  1. #1
    Member
    Join Date
    Jul 2008
    Posts
    146
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    How do you pass a var to a SQl Query in TC?

    Hi,

    I would appreciate if anybody can help me with this.

    I get data from the UI as follows
    var cID = Aliases.ISISMainPage.ContentFrame.SlObject("DataGr id", "").SlObject("RowsPresenter").SlObject("DataGridRo w", "").DataContext.CommandID

    and I need to pass the value into a SQL Query

    "Select sub.ID, metadata.Name AS 'Name', sub.CommandTypeName, sub.MasterCommandID FROM Command command " +
    "JOIN Command sub ON sub.MasterCommandID = command.MasterCommandID " +
    "JOIN ISISMetadata metadata ON metadata.Type = 'CommandStatus' AND metadata.id = command.StatusID " +
    "WHERE command.ID = :cID;

    The cID that I pass into the SQl is throwing an error or rather refuses to accept.

    How do I pass a parameter to the SQl statement.

    Thanks
    Riya
    Any help would be appreciate?

  2. #2
    Member
    Join Date
    Jul 2008
    Posts
    146
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: How do you pass a var to a SQl Query in TC?

    It worked.
    var cID = Aliases.ISISMainPage.ContentFrame.SlObject("DataGr id", "").SlObject("RowsPresenter").SlObject("DataGridRo w", "").DataContext.CommandID

    Qry.SQL = "Select sub.ID, metadata.Name AS 'Name', sub.CommandTypeName, sub.MasterCommandID FROM Command command " +
    "JOIN Command sub ON sub.MasterCommandID = command.MasterCommandID " +
    "JOIN ISISMetadata metadata ON metadata.Type = 'CommandStatus' AND metadata.id = command.StatusID " +
    "WHERE command.ID = :ParamcID"

    Qry.Parameters.ParamByName("ParamcID").Value = cID;

    Had to pass it using Qry.parameters statement.
    Thanks
    Riya

  3. #3
    Moderator tristaanogre's Avatar
    Join Date
    Mar 2006
    Location
    Pennsylvania, USA
    Posts
    1,792
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: How do you pass a var to a SQl Query in TC?

    I would just concatenate the string.

    <font class="small">Code:</font><hr /><pre>"Select sub.ID, metadata.Name AS 'Name', sub.CommandTypeName, sub.MasterCommandID FROM Command command " +
    "JOIN Command sub ON sub.MasterCommandID = command.MasterCommandID " +
    "JOIN ISISMetadata metadata ON metadata.Type = 'CommandStatus' AND metadata.id = command.StatusID " +
    "WHERE command.ID = " + cID;</pre><hr />
    -Robert Martin
    Automated Testing Evangelists
    TestComplete "expert"
    Definition expert - noun - Unknown drip under pressure

  4. #4
    Member
    Join Date
    Jul 2008
    Posts
    146
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: How do you pass a var to a SQl Query in TC?

    Thanks Robert.

    I had another question,
    have the following function wherein I donot want to use the value from the UI but do my calculation to get a date value and pass it as a parameter.
    The var startDate and endDate do not seem to be accepted by the query.Not sure how to pass it.

    var endDate = CommonFunctions.shortDate(x)
    Log.Message(endDate);
    Output is 11/05/2011
    var startDate = CommonFunctions.shortDate(y)
    Log.Message(startDate);
    Output is 10/30/2011

    I want to pass these values to the query as parameters. during execution it fails with "wrong arguement type values"

    sDate and eDate are coming from UI hence works

    function VerifySectionReports()
    {
    var now = new Date();
    var start = new Date(); // start range (inclusive)
    var end = new Date(); // end range (exclusive)
    var millisecondsADay = 1000 * 60 * 60 * 24;
    var x = end.setTime(now.getTime() - now.getDay() * millisecondsADay) - millisecondsADay ; // move to last sunday
    var y = start.setTime(end.getTime() - 7 * millisecondsADay); // one week before last sunday

    var endDate = CommonFunctions.shortDate(x)
    Log.Message(endDate);
    var startDate = CommonFunctions.shortDate(y)
    Log.Message(startDate);

    var sDate = Aliases.ISISMainPage.ContentFrame.SlObject("TextBl ock", "I_S_D Section", 1).DataContext.CustomDate_From
    Log.Message(sDate) ;

    var eDate = Aliases.ISISMainPage.ContentFrame.SlObject("TextBl ock", "I_S_D Section", 1).DataContext.CustomDate_To
    Log.Message(eDate );
    var Qry = ADO.CreateADOQuery();
    Qry.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=gvods;Data Source=gvfresql01\\gvqaisis";

    var id = 1012

    Qry.SQL = "SELECT ROUND(SUM(snap.AccumulatedDNI),1) AS 'AccumulatedDNI' FROM Daily_Section_Output snap " +
    "WHERE snap.SectionID = :id AND DATEDIFF(DAY, snap.DateSiteTZ, :sDate) &lt;= 0 AND DATEDIFF(DAY, snap.DateSiteTZ, :eDate )&gt; 0"; // works
    Qry.SQL = "SELECT ROUND(SUM(snap.AccumulatedDNI),1) AS 'AccumulatedDNI' FROM Daily_Section_Output snap " +
    "WHERE snap.SectionID = :id AND DATEDIFF(DAY, snap.DateSiteTZ, :startDate) &lt;= 0 AND DATEDIFF(DAY, snap.DateSiteTZ, :endDate )&gt; 0";// Does not work


    Qry.Parameters.ParamByName("sDate").Value = sDate; - works
    Qry.Parameters.ParamByName("eDate").Value = eDate;- works
    //Qry.Parameters.ParamByName("startDate").Value = startDate;
    //Qry.Parameters.ParamByName("endDate").Value = endDate;


    Qry.Parameters.ParamByName("id").Value = id;


    Qry.Open();
    Qry.First();

    var aDNI = Qry.FieldByName("AccumulatedDNI").Value

    Log.Message(aDNI);
    }

    Any help would be appreciated.

    Thanks again
    Riya

  5. #5
    Moderator tristaanogre's Avatar
    Join Date
    Mar 2006
    Location
    Pennsylvania, USA
    Posts
    1,792
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: How do you pass a var to a SQl Query in TC?

    What errors are you getting?
    -Robert Martin
    Automated Testing Evangelists
    TestComplete "expert"
    Definition expert - noun - Unknown drip under pressure

  6. #6
    Member
    Join Date
    Jul 2008
    Posts
    146
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Total Downloaded
    0

    Re: How do you pass a var to a SQl Query in TC?

    I fixed it. Passed the date as a string as I'm getting it as a string.

    var now = new Date();
    var start = new Date(); // start range (inclusive)
    var end = new Date(); // end range (exclusive)
    var millisecondsADay = 1000 * 60 * 60 * 24;
    var x = end.setTime(now.getTime() - now.getDay() * millisecondsADay) ; // move to last sunday
    var y = start.setTime(end.getTime() - 7 * millisecondsADay); // one week before last sunday

    var endDate = CommonFunctions.shortDate(x)
    var startDate = CommonFunctions.shortDate(y)
    var id = 1012
    var Qry = ADO.CreateADOQuery();

    Qry.ConnectionString = "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=gvods;Data Source=gvfresql01\\gvqaisis";
    Qry.SQL = "Select ROUND(SUM(snap.AccumulatedDNI),1) AS 'AccumulatedDNI' FROM Daily_Section_Output snap " +
    " WHERE snap.SectionID = :id AND DATEDIFF(DAY, snap.DateSiteTZ, '"+startDate+"') &lt;= 0 AND DATEDIFF(DAY, snap.DateSiteTZ, '"+endDate+"') &gt;0 ";

    Qry.Parameters.ParamByName("id").Value = id;

    Qry.Open();
    Qry.First();

    var aDNI = Qry.FieldByName("AccumulatedDNI").Value

    Log.Message(aDNI);

    Qry.Close();}

    Appreciate some more documentation on ADO.

    Thanks
    Riya

 

 

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Search Engine Optimisation provided by DragonByte SEO v2.0.36 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Resources saved on this page: MySQL 11.54%
vBulletin Optimisation provided by vB Optimise v2.6.4 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging v3.2.8 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
vBNominate (Lite) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Feedback Buttons provided by Advanced Post Thanks / Like (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Username Changing provided by Username Change (Free) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
BetaSoft Inc.
Digital Point modules: Sphinx-based search
All times are GMT -8. The time now is 01:33 AM.

Copyright BetaSoft Inc.