在VBA中将记录集存储到阵列 [英] Storing Recordset to Array in VBA

问题描述

我有一个动态函数来调用存储过程，并将记录集存储在一个数组中，我想在另一个子程序中使用它.但是我没有在这样的数组中得到结果:

I have a dynamic function to call a stored procedure and store the recordset in an array, which I want to use in another sub. But I don't get the result in an array like this:

Array(0,0) = 1
Array(0,1) = Miller
Array(1,0) = 2
Array(1,1) = Jones
Array(2,0) = 3
Array(2,1) = Jackson
....

我的数组结果如下:

Array(0,0) = 1
Array(1,0) = Miller
Array(0,1) = 2
Array(1,1) = Jones
Array(0,2) = 3
Array(1,2) = Jackson
....

为了解该过程，我向您展示了 SQL语句:

To understand the process I'm showing you the SQL-statement:

CREATE PROCEDURE dbo.sp_GetAllPersons
AS
BEGIN
    SET NOCOUNT ON;

    SELECT DISTINCT u.ID, u.Name
    FROM dbo.v_Users u
END
GO

获取记录集并将其存储在数组中的函数:

The function to get the recordset and store it in an array:

Public Function fGetDataBySProc(ByVal sProcName As String) As Variant
    ...
    
    Dim Cmd As New ADODB.Command
    With Cmd
        .ActiveConnection = cn
        .CommandText = sProcName
        .CommandType = adCmdStoredProc
    End With
    
    Dim ObjRs As New ADODB.Recordset: Set ObjRs = Cmd.Execute
    Dim ArrData() As Variant
    
    If Not ObjRs.EOF Then
        ArrData = ObjRs.GetRows(ObjRs.RecordCount)
    End If
    
    ObjRs.Close
    cn.Close
    
    fGetDataBySProc = ArrData
End Function

调用该函数的子项:

Public Sub cbFillPersons()
    Dim sProcString As String: sProcString = "dbo.sp_GetAllPersons"
    Dim ArrData As Variant: ArrData = fGetDataBySProc(sProcString)   
    Dim i as Integer
    
    ' Just for testing
    For i = LBound(ArrData) To UBound(ArrData)
        Debug.Print "AddItem: " & ArrData(0, i)
        Debug.Print "List: " & ArrData(1, i)
    Next
End Sub

我不知道我在做什么错.也许是 .GetRows()方法?

I don't know, what I'm doing wrong. Maybe it is the .GetRows()-method?

推荐答案

转置2D数组

• 您可以使用 getTransposedArray 函数转置结果数组.

  然后，您的 fGetDataBySProc 函数中的最后一行将是:

  Then the last line in your fGetDataBySProc function would be:

   fGetDataBySProc = getTransposedArray(ArrData) 
  

功能

Function getTransposedArray(Data As Variant) _
         As Variant
    
    Dim LB2 As Long
    LB2 = LBound(Data, 2)
    Dim UB2 As Long
    UB2 = UBound(Data, 2)
    
    Dim Result As Variant
    ReDim Result(LB2 To UB2, LBound(Data, 1) To UBound(Data, 1))
    
    Dim i As Long
    Dim j As Long
    
    For i = LBound(Data, 1) To UBound(Data, 1)
        For j = LB2 To UB2
            Result(j, i) = Data(i, j)
        Next j
    Next i
    
    getTransposedArray = Result
                 
End Function

这篇关于在VBA中将记录集存储到阵列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！