Database to Array

Question

I am trying to write a VBScript UFT test that gets a set of data from a table (it will be 100 records). I am only going to select one column as you can see from the query below.

SELECT TOP 100 l_Name FROM lessee

I was able to get the first record to display in a message box but I only did this as a test. What I'm trying to do now is add each record into an array so I can later loop through them to change the value of a WebEdit Textbox.

The following is my current code but I'm having a little difficulty figuring this out.

Dim DBQuery 
DBQuery = "Select top 100 l_Name from lessee"

objConnection.Open "Provider=sqloledb.1;Server=TestServer;User Id=User;Password=Test123;Database=DBTest"

objRecordSet.Open DBQuery,objConnection

' Return the Result Set  '''
For Each element In ObjRecordset.Fields.Item
    Value = objRecordSet.fields.item(element)               
    MsgBox Value
Next

' Release the Resources  '''
objRecordSet.Close        
objConnection.Close     

Set objConnection = Nothing
Set objRecordSet = Nothing

I was assuming looping through the returned records would work.

score 3 · Accepted Answer · edited May 23 '17 at 12:15

What @trincot says is correct to fix the issue of looping through the ADODB.Recordset but if you want to take the recordset and put into an Array there is a far easier way.

Dim data
'... (Recordset connection and execution code omitted)
If Not objRecordset.EOF Then data = objRecordset.GetRows()

GetRows() returns a Two Dimensional Array. The first element contains the column and the second contains the row so for example to access the second column of the fifth row using the data array above you would use

If IsArray(data) Then
  value = data(1, 4)
End If

Note: Array variables elements start at zero as the ordinal so the second column is 1 and the fifth row is 4.

You can iterate through the record data using a For loop like;

Dim row, rows

If IsArray(data) Then
  rows = UBound(data, 2)
  For row = 0 To rows
    'First column of the current row
    WScript.Echo data(0, row)
    'Second column of the current row
    WScript.Echo data(1, row)
    '... etc
  Next
End If

score 2 · Answer 2 · answered Mar 01 '16 at 19:38

Your loop will only execute once, because you are not iterating over the records, but over the columns (fields), of which you only have one. When you then retrieve the value for that column, you get the value of the current (i.e. first) record.

As there are no more columns, the loop ends.

Here is how you iterate over the records:

Do While Not objRecordSet.EOF
    value = objRecordSet("l_Name")

    ' do something with value
    '
    objRecordSet.MoveNext
Loop

Database to Array

2 Answers2

Linked