RSS
| << 13.3.6- The Fields Collection | Chapter13 | 13.4.0- Summary >> |
Arrays of Rows
We'll look at one more method of the Recordset object in this chapter. The GetRows method returns a multi-dimensional array, which contains the data of whatever fields you specify, from a specified number of records in the recordset. Its syntax is:
varArray = objRecordset.GetRows (Rows, Start, Fields)
All three parameters are optional. In its simplest form, you can use the GetRows method in a line like this:
varMyArray = objRS.GetRows
In this case, GetRows will adopt its default behavior – which is to returns an array containing all field values, for each record between the current record and the last record inclusive. When all of the rows are returned, then the cursor is set to point to the end of the recordset and EOF is set. Let's look at the three parameters:
- The Rows parameter allows us to restrict the number of records that will be returned in the array. You can specify an integer here, or you can use the constant adGetRowsRest to return all the rows including and subsequent to the current record. (Default is adGetRowsRest.)
- The Start parameter is a bookmark (or one of the adBookmark constants shown earlier), which allows us to specify which record to start on. (Default is adBookmarkCurrent.)
- The Fields parameter represents the fields whose values are to be placed in the array. Fields can be a single field name, or a single ordinal value (i.e. an integer reflecting the position of the field within the Fields collection). Alternatively, it can be an array of field names or an array of ordinal values. (Default is 'all fields'.)
The array that GetRows returns is sized automatically to fit the number of fields and records returned by the method.
Try It Out – The GetRows Method
OK, let's give it a try. We'll create a recordset of data, and then use it to generate an array of data (via the GetRows method). Then, just to prove that it works, we'll close the recordset and display the data using the array.
1. We're going to use the Movies database and the strConnect connection string again so you'll need to ensure they're set up as for previous examples in this chapter.
2. Start up your editor, and enter the following code for the file GetRows.asp:
<%
Option Explicit
Dim strConnect
%>
<!-- #include file="DataStore.asp" -->
<!-- METADATA TYPE="typelib"
FILE="C:\Program Files\Common Files\System\ado\msado15.dll" -->
<HTML>
<HEAD>
<TITLE>ADO GetRows Method</TITLE>
</HEAD>
<BODY>
<%
Dim objRS ' recordset object
Dim avarFields ' array of fields
Dim intRow ' current row
Dim intCol ' current column
Dim intLastRow ' ordinal of last row in the array
Dim intLastCol ' ordinal of last column in the array
' create and open the recordset object
Set objRS = Server.CreateObject ("ADODB.Recordset")
objRS.Open "Movies", strConnect, adOpenKeyset, adLockReadOnly, adCmdTable
' get the data
avarFields = objRS.GetRows
' now close the recordset and clean up
objRS.Close
Set objRS = Nothing
' now use the array to display thre data
intLastCol = UBound(avarFields, 1)
intLastRow = UBound(avarFields, 2)
For intRow = 0 To intLastRow ' loop through the array
For intCol = 0 To intLastCol
Response.Write avarFields(intCol, intRow) & " "
Next
Response.Write "<BR>"
Next
%>
</BODY>
</HTML>
3. Save the GetRows.asp file in the \inetpub\wwwroot\BegASPFiles folder.
|
4. View the page from your browser:
|
|
As you can see this just displays every field in every row of the Movies table.
How It Works
We'll skip past the (now familiar) header code, and the variable declarations. The recordset is created much as before:
Set objRS = Server.CreateObject ("ADODB.Recordset")
objRS.Open "Movies", strConnect, adOpenKeyset, adLockReadOnly, adCmdTable
Now we call the GetRows method. We're not passing in any arguments, so this will extract all records into our arrray:
avarFields = objRS.GetRows
Now we've captured the data in the array, we don't need the recordset anymore, so we can get rid of it:
objRS.Close
Set objRS = Nothing
Now we'll display the data, directly from the array. We use the VBScript function, UBound() to find out how many elements there are in each dimension of the array (the first dimension of the array is columns, the second is rows):
intLastCol = UBound(avarFields, 1)
intLastRow = UBound(avarFields, 2)
And then we can iterate through both dimensions in a nested loop, printing out the values:
For intRow = 0 To intLastRow ' loop through the array
For intCol = 0 To intLastCol
Response.Write avarFields(intCol, intRow) & " "
Next
Response.Write "<BR>"
Next
You might be wondering why we didn't use the GetRows method in RecToTable() to create the HTML table. There are two reasons for this:
- First, RecToTable() generated a table header with the names of the fields; the GetRows method can't help us with that, because the array that it generates doesn't contain the field names (it only contains the field values for each record)
- Second, the array takes up memory – since we already have the fields and values in the recordset, what's the point of copying them all to an array and then copying them out to the table? In the RecToTable example, we just created the table straight from the recordset
When are the best occasions to use the GetRows method? Well, it's probably not a good idea to use it for large recordsets, but for small ones, or even small sets of data from a large recordset, it's fine. You may find that you need to use this method when you end up with a recordset cursor that is not scrollable. For example, if you are using ADO and Oracle and execute a parameterized query from the Command object, the recordset cursor provided is read-only and totally non-scrollable. How, then, do you navigate backwards and forwards through the records? The answer is that you extract the rows into an array using GetRows and navigate through the array instead. Another occasion when you may want to use this method is with custom functions or routines that accept an array as a parameter, but couldn't cope with a recordset.
| << 13.3.6- The Fields Collection | Chapter13 | 13.4.0- Summary >> |
