Click here to Skip to main content
Click here to Skip to main content
Go to top

How to Get an ADO RecordSet into an Array.

, 20 Oct 2005
Rate this:
Please Sign up or sign in to vote.
How to convert an ADO RecordSet to and Array.
<!-- Article Starts - DO NOT ADD HTML/BODY START TAGS-->

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

Share

About the Author

Smith_TVPM
Software Developer
India India
Software Engineer,
Technopark, Kerala.
 
Rx 135 Owner
Yamaha Fan.

Comments and Discussions

 
QuestionQuestions About "vRecordarray" PinmemberJuan Rincon25-May-10 15:50 
GeneralThanks!! Pinmemberwinmedia11-Mar-07 3:15 
Generalmost of us knew about this 7 years ago... PinsussAnonymous21-Oct-05 7:34 
GeneralRe: most of us knew about this 7 years ago... PinmemberSmith_TVPM27-Oct-05 7:22 
GeneralRe: most of us knew about this 7 years ago... PinmemberBabs Iwarere27-Dec-07 21:56 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140916.1 | Last Updated 20 Oct 2005
Article Copyright 2005 by Smith_TVPM
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid