ADO - ADO GetRows Method for Bulk Data Retrieval

The GetRows method is a useful feature of ADO (ActiveX Data Objects) that allows an application to retrieve multiple records from a Recordset at once and store them in a two-dimensional array. Instead of accessing each record individually using the MoveNext method, GetRows can transfer a group of records into memory in a single operation. This makes it particularly useful when an application needs to process or display several records efficiently.

1. What is the GetRows Method?

The GetRows method belongs to the ADO Recordset object. Its primary purpose is to copy records from the current position of a Recordset into an array.

The basic syntax is:

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

The parameters are optional:

  • Rows specifies how many records should be retrieved.

  • Start specifies the starting bookmark or record position.

  • Fields specifies which fields should be retrieved.

If the parameters are omitted, ADO retrieves records beginning at the current position of the Recordset and continues until the end of the available records.

2. Why GetRows Is Useful

Normally, records can be processed one at a time:

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

This approach requires the application to repeatedly access the Recordset and move from one record to another.

With GetRows, several records can be transferred into an array:

data = rs.GetRows()

The application can then work with the array instead of repeatedly accessing the Recordset.

This can be particularly useful when:

  • Many records need to be processed.

  • Data needs to be passed to another part of an application.

  • Temporary in-memory processing is required.

  • The application needs to separate data retrieval from data presentation.

  • Data needs to be manipulated using array operations.

3. Structure of the Returned Array

One important characteristic of GetRows is that the resulting array is arranged by fields first and records second.

Suppose a Recordset contains three fields:

ID
Name
Department

and three records:

1   Ravi    Sales
2   Anu     Finance
3   Kumar   HR

After calling:

data = rs.GetRows()

the array can conceptually be represented as:

data(0,0) = 1
data(0,1) = 2
data(0,2) = 3

data(1,0) = "Ravi"
data(1,1) = "Anu"
data(1,2) = "Kumar"

data(2,0) = "Sales"
data(2,1) = "Finance"
data(2,2) = "HR"

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

Therefore:

data(field, record)

This arrangement is important because beginners often expect the first dimension to represent the record.

4. Retrieving a Specific Number of Records

The Rows parameter allows the developer to specify how many records should be retrieved.

For example:

data = rs.GetRows(10)

This requests up to 10 records starting from the current position.

If fewer than 10 records are available, ADO returns only the available records.

This feature can be useful when an application does not need the complete Recordset at once.

5. Selecting Specific Fields

The Fields parameter allows the application to specify which fields should be included in the resulting array.

For example:

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

Instead of retrieving every field, the application requests only the Name and Department fields.

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

For example, a database table might contain:

EmployeeID
Name
Department
Address
Phone
Email
Salary
JoiningDate

If an application only needs the employee's name and department, retrieving only those fields avoids unnecessary data processing.

6. Using GetRows with the Current Record Position

GetRows normally begins retrieving records from the current position of the Recordset.

For example:

rs.MoveFirst
data = rs.GetRows(5)

The method starts from the first record and retrieves up to five records.

Similarly:

rs.MoveNext
data = rs.GetRows(5)

starts from the next available record.

Therefore, the current Recordset position can affect which records are returned.

7. Processing the Returned Data

After retrieving the records, the application can process the array using nested loops.

For example:

data = rs.GetRows()

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

Here:

UBound(data, 1)

returns the highest index of the field dimension.

Similarly:

UBound(data, 2)

returns the highest index of the record dimension.

This allows the application to determine the size of the returned array dynamically.

8. GetRows and Memory Usage

Although GetRows can improve processing efficiency, it is important to understand that the retrieved records are stored in an array in memory.

For example, retrieving 20 records containing a few fields may require very little memory. However, retrieving hundreds of thousands of records with many large fields can consume considerable memory.

Therefore, developers should choose an appropriate number of rows rather than automatically retrieving an extremely large dataset.

A better approach for large datasets may involve retrieving data in manageable portions.

9. GetRows Compared with Record-by-Record Processing

Consider two approaches.

Traditional Recordset processing:

Do Until rs.EOF
    ProcessRecord rs
    rs.MoveNext
Loop

With GetRows:

data = rs.GetRows()

For i = 0 To UBound(data, 2)
    ProcessData data(0, i)
Next

The first approach continuously interacts with the Recordset while processing each record.

The second approach first transfers the selected records into an array and then processes the array.

The second approach can be convenient when the application needs to perform extensive processing on the retrieved data without repeatedly interacting with the Recordset object.

10. Advantages of GetRows

The major advantages of the GetRows method include:

Efficient retrieval: Multiple records can be retrieved in one method call.

Array-based processing: Once retrieved, the data can be processed as an array.

Selective field retrieval: Applications can request only the fields they need.

Controlled record retrieval: The number of records can be specified.

Simplified data manipulation: Array operations can sometimes be easier than repeated Recordset navigation.

Separation of retrieval and processing: Data can be retrieved first and processed afterward.

11. Limitations of GetRows

GetRows also has some limitations.

First, the returned data is stored in memory. Retrieving a very large number of records can therefore increase memory consumption.

Second, the array organization can initially be confusing because the first dimension represents fields and the second represents records.

Third, once data has been copied into the array, changes made to the original database are not automatically reflected in the already retrieved array.

Finally, GetRows is primarily intended for retrieving data. It does not replace the Recordset when an application needs advanced record navigation or direct manipulation of individual records.

12. Practical Example

Consider an employee Recordset:

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

rs.Open "SELECT EmployeeID, Name, Department FROM Employees", conn

data = rs.GetRows(20)

The application now has up to 20 records stored in data.

The information can then be processed:

For i = 0 To UBound(data, 2)

    employeeID = data(0, i)
    employeeName = data(1, i)
    department = data(2, i)

    Response.Write employeeID & " - "
    Response.Write employeeName & " - "
    Response.Write department

Next

In this example, the application does not repeatedly access the Recordset for every individual field. Instead, the required records are transferred into an array and processed from there.

13. Important Points to Remember

When studying the ADO GetRows method, remember these key points:

  1. GetRows belongs to the ADO Recordset object.

  2. It retrieves multiple records into an array.

  3. The returned array is two-dimensional.

  4. The first dimension represents fields.

  5. The second dimension represents records.

  6. The Rows parameter controls how many records are retrieved.

  7. The Fields parameter can be used to select particular fields.

  8. Retrieval normally starts at the Recordset's current position.

  9. Retrieved data is stored in memory.

  10. GetRows is particularly useful when multiple records need to be processed as an array.

Conclusion

The ADO GetRows method provides a convenient way to transfer multiple Recordset rows into a two-dimensional array. Its main advantage is that it allows an application to retrieve data in a single operation and subsequently process that data independently of the Recordset's normal row-by-row navigation. Understanding the field-first, record-second structure of the returned array is especially important. When used appropriately, GetRows can make ADO applications more efficient and can simplify situations where retrieved database data needs to be processed, formatted, or passed to other application components.