我有一个动态函数来调用存储过程,并将记录集存储在一个数组中,我想在另一个子数组中使用它。但是我没有在这样的数组中得到结果:
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-statement
:
CREATE PROCEDURE dbo.sp_GetAllPersons
AS
BEGIN
SET NOCOUNT ON;
SELECT DISTINCT u.ID, u.Name
FROM dbo.v_Users u
END
GO
获取记录集并将其存储在数组中的函数:
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()
-方法?
您可以使用getTransposedArray
函数转置结果数组。
然后,fGetDataBySProc
函数的最后一行将是:
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
本文收集自互联网,转载请注明来源。
如有侵权,请联系[email protected] 删除。
我来说两句