Database to Array

0
0

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.

  • You must to post comments
0
0

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
  • You must to post comments
Showing 1 result
Your Answer
Post as a guest by filling out the fields below or if you already have an account.
Name*
E-mail*
Website