dynamically run the DB query in keyword driven framework

[GAutomation]

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

[GAutomation]

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]

Try

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

This should return the contents of the column name indicated.

[GAutomation]

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]

My bad... try  instead

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

[GAutomation]

Robert,

I tried with that also same error. should i use ADOConnection instead of query?

tried with field,fields,fieldbuname,fieldcount

it is not recongnising qry1.Fields

[tristaanogre]

Right... notice that I changed the code to not use Qry1.Fields at all.  

It's Qry1.FieldByName instead... that's the proper code for that object.

https://support.smartbear.com/testcomplete/docs/reference/program-objects/ado/createadoquery.html

[GAutomation]

Thanks Robert,

It works fine if i give the specfic column name and it is displaying that column name values. 

but in my query i am giving select * from table name which will pul all column values right?

so in the log results too i want all the column values

if i given Log.message Qry1.FieldByName.item(i).Value it is not working.

[tristaanogre · Accepted solution]

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]

Thanks Robert,

below code worked

Do while Not Qry1.EOF
for i=0 to Qry1.FieldCount -1
'Log.Message("Column Name:" &Qry1.Field(i).FieldName & " |"&" " &"Column Value:"&Qry1.Field(i).Value &"|"& vbNewLine

Next

Qry.Next

Loop