Populating Array with a Recordset, Error When only one Record Returned

[M_S_Jones]

Hello all,

I'm working on a project in VB6, but the code that I have issue with is basically the type of thing that I have recycled from old VBA projects, so I've posted it in this category. I'm populating a multi-dimensional array from a recordset, and it works absolutely fine with my test data. I have several different recordsets which contain various numbers of records, some over a hundred, some with none at all. Every recordset which contains none or more than one works absolutely fine, it's just the scenario where there is just one record.

In this scenario, the second column cannot be populated for some reason, and I am given a subscript out of range error message. Here is my code:

If rstRecordSet.EOF = False Then
    varRecCnt = rstRecordSet.RecordCount
    ReDim Paths(1 To varRecCnt, 1 To varRecCnt)

    rstRecordSet.MoveFirst
    
    Do
    Paths(rstRecordSet.AbsolutePosition, 1) = rstRecordSet.Fields(0)
    Paths(rstRecordSet.AbsolutePosition, 2) = rstRecordSet.Fields(1)
    rstRecordSet.MoveNext
    Loop Until rstRecordSet.EOF = True
    rstRecordSet.Close
  Else
    MsgBox "No records were returned using the query " & cmdCommand.CommandText
End If

At first I suspected it was the code that moved to the next record, as if there was only one record and before it got to the loop until EOF it was told to move to next, it would generate an error, but after testing I've found that this isn't the error. I have tried removing the line that populates the array position 1,2 and then my record is entered into the array successfully. But it's strange because I have a recordset which contains four records, and both columns are populated for each of the four records, the second column never causes a problem, except for my two recordsets which only have one record each.

Thanks for any help you can offer,

Matthew

 

[gemma-the-husky]

this looks like a problem

Paths(rstRecordSet.AbsolutePosition, 2) = rstRecordSet.Fields(1)

if you only have one record, the redim is allocating an array (1,1)

so a reference to paths(whatever,2) will fail

---------
maybe the redim should be

redim myarray (recordcount,2)

 

[M_S_Jones]

Of course, thanks for that. I thought I was missing something obvious. I've changed it now so that the first parameter (the number of rows) is the same as the record count, and the second parameter (the number of columns) is 2. After a quick test of a couple of recordsets (including one with just one record), this appears to work.

Thanks again,

Matthew

 

[DJkarl]

Just my 2 cents here, but for a simple array like that you may want to look at the GetRows method of the recordset object, it returns an array for you.

 

[M_S_Jones]

Thanks for the advice. Is there any advantage to using that, other than ease of typing? Would that be faster to run?

Thanks,

Matthew

 

[DJkarl]

Nothing concrete to offer in terms of performance, but I have noticed that it seems to be slightly faster IMO. Of course I have no metrics to back that up, just my perception.

 

[M_S_Jones]

O.K., thank you for your suggestion.

Matthew