|
|
Product Home Page | Developer Site | Version |
   
|
Retrieves multiple records of a recordset into an array.
array = recordset.GetRows( Rows, Start, Fields )
array
Two-dimensional Array containing records.
Rows
An optional Long expression indicating the number of records to retrieve. Default is adGetRowsRest (-1).
Start
An optional String or Variant that evaluates to the bookmark for the record from which the GetRows operation should begin. You can also use one of the following BookmarkEnum values:
Constant
|
Description
|
|---|---|
AdBookmarkCurrent
|
Start at the current record.
|
AdBookmarkFirst
|
Start at the first record.
|
AdBookmarkLast
|
Start at the last record.
|
Fields
An optional Variant representing a single field name or ordinal position or an array of field names or ordinal position numbers. ADO returns only the data in these fields.
Returns a two-dimensional array.
Use the GetRows method to copy records from a recordset into a two-dimensional array. The first subscript identifies the field and the second identifies the record number. The array variable is automatically dimensioned to the correct size when the GetRows method returns the data.
If you do not specify a value for the Rows argument, the GetRows method automatically retrieves all the records in the Recordset object. If you request more records than are available, GetRows returns only the number of available records.
If the Recordset object supports bookmarks, you can specify at which record the GetRows method should begin retrieving data by passing the value of that record's ADO Recordset Object Bookmark Property.
If you want to restrict the fields the GetRows call returns, you can pass either a single field name/number or an array of field names/numbers in the Fields argument.
After you call GetRows, the next unread record becomes the current record, or the ADO Recordset Object BOF, EOF Properties property is set to True if there are no more records.
This Visual Basic example uses the GetRows method to retrieve a specified number of rows from a recordset and to fill an array with the resulting data. The GetRows method will return fewer than the desired number of rows in two cases: either if EOF has been reached, or if GetRows tried to retrieve a record that was deleted by another user. The function returns False only if the second case occurs. The GetRowsOK function is required for this procedure to run.
Public Sub GetRowsX()
Dim rstEmployees As ADODB.Recordset
Dim strCnn As String
Dim strMessage As String
Dim intRows As Integer
Dim avarRecords As Variant
Dim intRecord As Integer
' Open recordset with names and hire dates from employee table.
strCnn = "driver={SQL Server};server=srv;" & _
"uid=sa;pwd=;database=pubs"
Set rstEmployees = New ADODB.Recordset
rstEmployees.Open "SELECT fName, lName, hire_date " & _
"FROM Employee ORDER BY lName", strCnn, , , adCmdText
Do While True
` Get user input for number of rows.
strMessage = "Enter number of rows to retrieve."
intRows = Val(InputBox(strMessage))
If intRows <= 0 Then Exit Do
` If GetRowsOK is successful, print the results,
` noting if the end of the file was reached.
If GetRowsOK(rstEmployees, intRows, _
avarRecords) Then
If intRows > UBound(avarRecords, 2) + 1 Then
Debug.Print "(Not enough records in " & _
"Recordset to retrieve " & intRows & _
" rows.)"
End If
Debug.Print UBound(avarRecords, 2) + 1 & _
" records found."
` Print the retrieved data.
For intRecord = 0 To UBound(avarRecords, 2)
Debug.Print " " & _
avarRecords(0, intRecord) & " " & _
avarRecords(1, intRecord) & ", " & _
avarRecords(2, intRecord)
Next intRecord
Else
` Assuming the GetRows error was due to data
` changes by another user, use Requery to
` refresh the Recordset and start over.
If MsgBox("GetRows failed--retry?", _
vbYesNo) = vbYes Then
rstEmployees.Requery
Else
Debug.Print "GetRows failed!"
Exit Do
End If
End If
` Because using GetRows leaves the current
` record pointer at the last record accessed,
` move the pointer back to the beginning of the
` Recordset before looping back for another search.
rstEmployees.MoveFirst
Loop
rstEmployees.Close
End Sub
Public Function GetRowsOK(rstTemp As ADODB.Recordset, _
intNumber As Integer, avarData As Variant) As Boolean
` Store results of GetRows method in array.
avarData = rstTemp.GetRows(intNumber)
` Return False only if fewer than the desired
` number of rows were returned, but not because the
` end of the Recordset was reached.
If intNumber > UBound(avarData, 2) + 1 And _
Not rstTemp.EOF Then
GetRowsOK = False
Else
GetRowsOK = True
End If
End Function
|
|
Copyright © 2003 Sun Microsystems, Inc. All rights reserved.