数据库到阵列 [英] Database to Array

问题描述

我正在尝试编写一个VBScript UFT测试，该测试从表中获取一组数据(将是100条记录).从下面的查询中可以看到，我只会选择一列.

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

我能够获得第一个记录显示在消息框中，但我只是作为测试来做.我现在想做的是将每个记录添加到数组中，以便以后可以遍历它们以更改WebEdit文本框的值.

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.

推荐答案

@trincot

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()返回一个二维数组.第一个元素包含列，第二个元素包含行，例如，使用上面要使用的data数组访问第五行的第二列

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

注意: Array变量元素的序号从零开始，因此第二列为1，第五行为4.

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

您可以使用For这样的循环来遍历记录数据；

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

这篇关于数据库到阵列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！