ADO - ADO Recordset GetRows Method

The GetRows method in ADO (ActiveX Data Objects) is used to retrieve multiple rows from a Recordset and store them in a two-dimensional array. Instead of processing records one by one using methods such as MoveNext, GetRows allows an application to retrieve a group of records at once. This can make data processing more convenient and, in suitable situations, more efficient.

1. What is GetRows?

A Recordset normally represents the rows returned from a database query. When working with a Recordset, you can move through the records individually:

Do Until rs.EOF
    Response.Write rs("Name")
    rs.MoveNext
Loop

With GetRows, several records can instead be copied into an array:

data = rs.GetRows()

The returned data variable contains the selected records in an array structure.

The general syntax is:

array = recordset.GetRows(Rows, Start, Fields)

The arguments are optional:

Rows specifies how many records should be retrieved.
Start specifies the starting record.
Fields specifies which fields should be included.

2. Basic Example

Suppose a database contains a Students table:

ID	Name	Course
1	Rahul	Python
2	Anitha	Java
3	Kiran	PHP

A Recordset can be created as follows:

Set rs = Server.CreateObject("ADODB.Recordset")

rs.Open "SELECT ID, Name, Course FROM Students", conn

data = rs.GetRows()

After executing GetRows, the records are stored in the data array.

The important point is that GetRows returns a two-dimensional array, where one dimension represents fields and the other represents records.

Conceptually, the result looks like:

data(0,0) = 1
data(1,0) = "Rahul"
data(2,0) = "Python"

data(0,1) = 2
data(1,1) = "Anitha"
data(2,1) = "Java"

data(0,2) = 3
data(1,2) = "Kiran"
data(2,2) = "PHP"

The first index represents the field, while the second index represents the record.

3. Retrieving a Specific Number of Rows

The Rows argument can be used when you do not want to retrieve the entire Recordset.

For example:

data = rs.GetRows(5)

This attempts to retrieve up to five records starting from the current position of the Recordset.

If fewer than five records remain, only the available records are returned.

For example, if the Recordset contains three remaining records, the method returns those three records rather than generating additional records.

4. Specifying the Starting Record

The Start argument determines where the retrieval begins.

For example:

data = rs.GetRows(5, 2)

Here, the method attempts to retrieve five records beginning from the specified starting position.

In ADO, the Start argument can also be associated with a bookmark, depending on the Recordset configuration and provider.

A bookmark allows an application to identify a particular record and use that position as the starting point.

5. Retrieving Selected Fields

The Fields argument allows you to specify which fields should be included in the returned array.

For example:

data = rs.GetRows(10, , Array("Name", "Course"))

This requests only the Name and Course fields rather than all fields in the Recordset.

This can be useful when a Recordset contains many columns but the application needs only a small subset of them.

For example, instead of retrieving:

ID
Name
Course
Email
Phone
Address
DateOfBirth

you might retrieve only:

Name
Course

This makes the resulting array easier to work with.

6. Processing the Returned Array

Because GetRows returns an array, the application can process the data using loops.

A simplified VBScript example is:

data = rs.GetRows()

For i = 0 To UBound(data, 2)
    Response.Write data(0, i)
    Response.Write data(1, i)
    Response.Write data(2, i)
Next

Here:

UBound(data, 2)

returns the highest index of the second dimension, which represents the records.

If three fields and five records were returned, the array can conceptually be accessed as:

data(field, record)

For example:

data(0, 0)
data(1, 0)
data(2, 0)

represent the three fields of the first record.

Similarly:

data(0, 1)
data(1, 1)
data(2, 1)

represent the three fields of the second record.

7. GetRows Compared with MoveNext

A traditional Recordset loop processes records individually:

Do Until rs.EOF

    Response.Write rs("Name")

    rs.MoveNext

Loop

The application reads the current record and then moves to the next record.

With GetRows:

data = rs.GetRows()

multiple records are retrieved into an array.

The difference can be summarized as follows:

Feature	Traditional Recordset Loop	GetRows
Processing	One record at a time	Multiple records
Storage	Recordset remains active	Data copied to an array
Navigation	Uses methods such as `MoveNext`	Uses array indexes
Data access	Field-based Recordset access	Array-based access
Useful for	Sequential processing	Bulk data processing

8. Advantages of GetRows

One important advantage is that it allows data to be retrieved from the Recordset into an array. Once the data has been copied, the application can process the array without repeatedly accessing the current Recordset position.

It can also simplify situations where data needs to be passed to another part of an application in array form.

Another advantage is selective field retrieval. By specifying fields, an application can retrieve only the columns that it needs.

It can also be useful when preparing data for presentation, transformation, or further in-memory processing.

9. Important Consideration About Memory

Although GetRows can be convenient, retrieving a very large number of records into an array can consume considerable memory.

For example:

data = rs.GetRows()

on a Recordset containing hundreds of thousands of rows can create a very large array.

For large datasets, it can be preferable to process records in smaller groups rather than loading everything into memory at once.

For example:

data = rs.GetRows(100)

can be used to retrieve a limited number of rows at a time.

10. What Happens to the Recordset Position?

After GetRows retrieves records, the current position of the Recordset is generally advanced past the records that were retrieved.

For example:

data = rs.GetRows(10)

attempts to retrieve ten records from the current position. Afterward, the Recordset's position reflects the movement caused by retrieving those records.

This is important when the application intends to continue processing the same Recordset after calling GetRows.

11. Handling Empty Recordsets

An application should also consider the possibility that the Recordset contains no records.

For example:

If rs.EOF And rs.BOF Then
    Response.Write "No records found."
Else
    data = rs.GetRows()
End If

Checking for an empty Recordset before retrieving the data makes the application more robust.

12. Practical Example

Consider a student management application that retrieves student names and courses:

Set rs = Server.CreateObject("ADODB.Recordset")

rs.Open "SELECT Name, Course FROM Students", conn

If Not (rs.BOF And rs.EOF) Then

    data = rs.GetRows()

    For i = 0 To UBound(data, 2)
        Response.Write "Name: " & data(0, i)
        Response.Write "Course: " & data(1, i)
    Next

End If

rs.Close
Set rs = Nothing

In this example, the database returns the student records, GetRows copies them into an array, and the application accesses the values through array indexes.

13. Key Points to Remember

GetRows is an ADO Recordset method designed for retrieving multiple records into an array. Its basic syntax is:

array = recordset.GetRows(Rows, Start, Fields)

The returned array is two-dimensional, with the first dimension representing fields and the second representing records. The Rows parameter controls how many records are retrieved, Start controls where retrieval begins, and Fields allows specific columns to be selected.

The method is particularly useful when an application needs to perform bulk or in-memory processing of Recordset data. However, retrieving very large datasets at once can consume significant memory, so the number of rows should be considered carefully.

Overall, ADO GetRows provides a convenient way to convert Recordset data into an array, making multiple database records easier to process programmatically.