FetchArray function - 2 dimensional arrays

[da_silvy]

Function FetchArray(strQuery As String, conn As ADODB.Connection) As Variant
Dim resBuff() As Variant
Dim nRS As ADODB.Recordset
Dim fld As ADODB.Field
 
Dim iRow As Integer, iField As Integer
 
Set nRS = New ADODB.Recordset
nRS.CursorLocation = adUseServer
 
nRS.Open strQuery, conn
nRS.MoveFirst
 
iRow = -1
iField = -1
 
Do Until nRS.EOF
    iRow = iRow + 1
    For Each fld In nRS.Fields
        iField = iField + 1
[b]        resBuff(iRow, iField) = fld.Value[/b]
        Debug.Print resBuff(iRow, iField)
    Next
Loop
End Function

It seems to have trouble on ^ that line.

I tried resBuff(iRow)(iField) = fld.Value but I can't even remember how to do 2d arrays in vb now.

I was hoping to re create some of the PHP->mysql functions in vb because i hate recordsets

The idea of the above is to load a query into an array.

Any ideas?

[swatty]

Function FetchArray(strQuery As String, conn As ADODB.Connection) As Variant
'got to now how many fields you got in the rs
   Dim resBuff(1, 20) As Variant
   Dim nRS As ADODB.Recordset
   Dim fld As ADODB.Field
   
   Dim iRow As Integer, iField As Integer
   
   Set nRS = New ADODB.Recordset
   nRS.CursorLocation = adUseServer
   
   nRS.Open strQuery, conn
   nRS.MoveFirst
   
   iRow = -1
   iField = -1
   
   If nRS.RecordCount Then
      nRS.MoveLast
      nRS.MoveFirst
   End If
   ReDim resBuff(nRS.RecordCount, 20)
   Do Until nRS.EOF
       iRow = iRow + 1
       For Each fld In nRS.Fields
           iField = iField + 1
           resBuff(iRow, iField) = fld.Value
           Debug.Print resBuff(iRow, iField)
       Next
   Loop
End Function

[da_silvy]

Array already dimensioned

[da_silvy]

Also, it returns recordcount as -1 but i know there are rows returned

[Frans C]

Why make it so difficult?

Function FetchArray(strQuery As String, conn As ADODB.Connection) As Variant
   Dim nRS As ADODB.Recordset
   
   Set nRS = New ADODB.Recordset
   nRS.CursorLocation = adUseServer
   
   nRS.Open strQuery, conn
   FetchArray = nRS.GetRows
   nRS.Close
   Set nRS = Nothing
End Function

EDIT: added some cleanup code

[da_silvy]

It doesn't return it in 2 dimensions though

I wanted:

res(0, 2) to return the 3rd field in the first row, similar to PHP

etc

res(1, 1) 2nd row 2nd field

any more ideas?

[Frans C]

GetRows should return a two dimensional array, but in reverse order then you want. The first dimension is the field, and the second dimension the rownumber, so res(0,2) should return the first field in the third row.
If you want it the other way round, you would have to populate the array yourself. Because you can only redim Preserve the second dimension, you would have to get an accurate recordcount before you start to populate the array. Not all data providers support the recordcount property though (that is why you get -1 for recordcount).

[da_silvy]

How do i get the ubound of the second array then?

ubound(myarray(1)) didn't seem to work?

[da_silvy]

Solved;

UBound(myArray, 2) did the trick

Thanks for the help FransC