Inserting a column from an SQL query into a data column in HPE UFT

Question

This is the code in HPE UFT that successfully runs the query and displays a msg box. I would like it to store the query results or at least 1 column of the query results in the HPE UFT data table so that I can run a loop on those loan numbers later.

Set objConnection = CreateObject("ADODB.Connection")

Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.open "provider=123ABC;Server=T1;User Id=****; 
Password=****; Database=i_prod;Trusted_Connection=Yes"

sqlQuery="SELECT Table1 AS LoanNumber, lm.loanrecordid, clm.istexasconversion as TexasConversion FROM Table1 lm WITH (NOLOCK) LEFT JOIN Table2 clm WITH (NOLOCK) ON clm.lenderdatabaseid = lm.lenderdatabaseid AND clm.loanrecordid = lm.loanrecordid Where clm.istexasconversion IS NOT NULL"

objRecordSet.open sqlQuery, objConnection

value = objRecordSet.fields.item(0)               
msgbox Value


objRecordSet.Close
objConnection.Close
Set objConnection = Nothing
Set objRecordSet = Nothing

This is the query used in SQL.

 SELECT
   lm.loanid AS LoanNumber
   ,Column1
   ,column2 as Texas
   FROM table1 lm WITH (NOLOCK)
 LEFT JOIN table2 clm WITH (NOLOCK)
   ON clm.lenderdatabaseid = lm.lenderdatabaseid
   AND clm.loanrecordid = lm.loanrecordid

Desired Result Image

Answer 1

To start with, it helps if you define the parameter names initially in the table like this:

Datatable.AddParameter("LoanNumber", dtGlobal)
Datatable.AddParameter("LoanID", dtGlobal)
Datatable.AddParameter("TexasConversion", dtGlobal)

This will set the first three columns of the Global datatable with the name of the parameter you are going to insert.

Then, for ease of use, put the data in your RecordSet into an Array:

myArray = objRecordSet.GetRows ' do this before you close the recordset

And finally, loop around the two dimensional array to populate the table with data:

For myLoop = 0 to UBound(myArray, 2) ' loop over the total rows
    DataTable.SetCurrentRow(myLoop + 1) ' +1 as row count starts from 1 not 0
    Datatable("LoanNumber") = myArray(0, myLoop)
    Datatable("LoanID") = myArray(1, myLoop)
    Datatable("TexasConversion") = myArray(2, myLoop)
Next

And if you need to store a copy of the datatable (unless you plan to only use this data during the run, you will need to):

Datatable.Export("\\Path\To\File\To\Save.xlsx")

If you have any trouble following this, post a comment and I'll try to explain further.