Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles
(untagged)

How to Get an ADO RecordSet into an Array.

0.00/5 (No votes)
20 Oct 2005 1  
How to convert an ADO RecordSet to and Array.

Introduction

Often it is required to get the records retrieved in an SQL fetch into a recordset to an array. This is made possible using the GetRows method of the Recordset Object which inturn returns a variant array.

 

The procedure is explained below

 

Dim dbA As ADODB.Connection

Dim rsA As ADODB.Recordset

 

    Set dbA = New ADODB.Connection

    Set rsA = New ADODB.Recordset

    dbA.Open "DSN=tstDSN;UID=sa;PWD=pass;"

 

Dim csSql As String

csSql = "SELECT    * From EmployeeTable�

 

            Set rsA = dbA.Execute(csSql)

            If rsA.EOF = True And rsA.BOF = True Then

                GoTo exithandler

            End If

 

'Get this to Array.

Dim nNoOfReords as Integer

arrRecordArray = rsA.GetRows

nNoOfReords= UBound(vRecordArray, 2)+1

 

�Now arrRecordArray contains the recordset in array format.

 

Consider the employee table with fileds empID, empName, empAge such that

 

empID              empName        empAge

----------------------------------------------

1                      Smith                24

2                      Baiju                26

3                      Ragi                 26

4                      Pramod            23

5                      Bincy                25

----------------------------------------------

Then the variant array structure will be

 

arrRecordArray(0,0)=1,  arrRecordArray(1,0)=Smith, arrRecordArray(2,0)=24

arrRecordArray(0,1)=2,  arrRecordArray(1,1)=Baiju, arrRecordArray(2,1)=26

 

i.e. the first argument specifies the FieldIndex and the second one specifies the RecordIndex.

 

The number of records returned may be found as

nNoOfReords= UBound(vRecordArray, 2)+1

 

hope this piece of information will be useful for you.

 

By Smith.S.Raj

Software Engineer

M-Squared, Technopark.

 

License

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here