Forum Discussion

GAutomation's avatar
GAutomation
Frequent Contributor
6 years ago

dynamically run the DB query in keyword driven framework

I am trying to pass the sql stament in the excel. But getting "wrong number of arguments or invalid property assignment"

  • tristaanogre's avatar
    tristaanogre
    6 years ago

    Correct... because there is no "Item" sub-property off of the field by name.

     

    If you're going to loop via the index, then you should use

     

    Qry1.FieldByNumber(i).Value

     

    note:

     

    In the following help file for the CreateADOQuery method (https://support.smartbear.com/testcomplete/docs/reference/program-objects/ado/createadoquery.html) there is a link to the wiki for the Delphi ADO component objects which contains information for all fields and methods for the various objects.  This is going to be your resource for any objects that return the Delphi VCL ADO objects.

  • GAutomation's avatar
    GAutomation
    Frequent Contributor
    6 years ago

    after chaning the code to 

    Qry1.SQL=value it worked. But i need help to display the results that query is returning in log results. How can i achieve that.

     

    Function Sq_Qry(value )

     

    'Create a query
    '
    Set Qry1 = ADO.CreateADOQuery

    'Specify the connection string

    Qry1.ConnectionString = "Provider=IBMDA400.DataSource.1;Persist Security Info=False;User ID=xxx;Password=exxx;Data Source=xxx"

    'Specify the SQL expression

    'Qry1.SQL="Select * FROM schema.tablename"


    Qry1.SQL=value


    Set Qryresult = qry1
    'Log.Message(Qryresult)
    'Execute the query

    Qry1.Open()
    'Process results and insert data into the test log

    Qry1.First()

    'while Not Qry1.EOF

    'return Qryresult
    'Log.Message(Qry1.SQL.Value)
    'Qry1.Next()

    'Wend

    'Closes the query
    Qry1.Close()

    End Function

    • tristaanogre's avatar
      tristaanogre
      Esteemed Contributor
      6 years ago

      Try

       

      Qry1.Fields.Item('<column name>').Value

       

      This should return the contents of the column name indicated.

    • GAutomation's avatar
      GAutomation
      Frequent Contributor
      6 years ago

      Thanks Robert,

      I tried adding that and its giving me :object does not support this method or poerpoerty "Qry1.Fields"

       

      Updated my code as below

       

      Function Sq_Qry(value )
      'Create a query
      '
      Set Qry1 = ADO.CreateADOQuery

      'Specify the connection string

      Qry1.ConnectionString = "Provider=IBMDA400.DataSource.1;Persist Security Info=False;User ID=XXX;Password=XXX;Data Source=XXX"
      'Specify the SQL expression
      'Qry1.SQL="Select * FROM schema.table"
      Qry1.SQL=value

      'Execute the query
      Qry1.Open()
      'Process results and insert data into the test log
      Qry1.First()

      'Obtains field names

      while Not Qry1.EOF
      for i=0 to Qry1.Fields.Count
      Set test=Qry1.Fields.Item(i).Value
      Next


      'Log.Message(Qry1.SQL.Value)
      Qry1.Next()
      Wend
      Log.Message(test)
      'Closes the query
      Qry1.Close()
      errCode =0
      End Function

      • tristaanogre's avatar
        tristaanogre
        Esteemed Contributor
        6 years ago

        My bad... try  instead

         

        Qry1.FieldByName('<column name>').Value