How To Pass a Variant Array Populated with a RecordSet From a VB Component to an ASP Page, Part 2

In Part 1 we looked at a VB component that grabbed information from a database table, stuffing it into an array using the GetRows method of the Recordset object. In this part we'll look at the ASP page and how, exactly, it will retrieve this variant array and access its contents!

THE ASP FILE - How2Project3.asp

The Variable Array Structure
We'll be receiving a Variant array in our asp file that will be populated with the RecordSet field values from our method call. We'll assign the field values to a variable we'll name vRecordArray. But before we cover the asp code, some explanation of the array structure is in order. The vRecordArray values were originally obtained via the ADO GetRows Method in our VB code - thus the vRecordArray array structure will be a two-dimensional array. The array is zero based. The first subscript will identify the fields of the database and the second the record number.

vRecordArray(FIELD_INDEX, RECORD_INDEX)

Here FIELD_INDEX will index the fields returned. Since we'll be identifying three fields in our sql statement (ExampleFieldID, ExampleField1, and ExampleField2), this first subscript will be indexed from 0 to 2. With 0 being the index for the first field named ExampleFieldID, 1 the index for ExampleField1, and 2 the index for ExampleField2.

The returned two-dimensional array mimics the database rows and columns as identified in our SQL statement. So if we wanted the fifth record value of the ExampleField2 field, our first subscript would be 2 and our second would be 4.

LngVariable = vRecordArray(2,4)

LngVariable would contain "Text Field 2e" The two following tables show the database and array results of the following SQL statement:

Select ExampleFieldID, ExampleField1, ExampleField2 FROM ExampleTable

Database rows and columns determined by the SQL statement
ExampleFieldID	ExampleField1	ExampleField2
1	100	"Text Field 2a"
2	200	"Text Field 2b"
3	300	"Text Field 2c"
4	400	"Text Field 2d"
5	500	"Text Field 2e"
6	600	"Text Field 2f"

Array index structure of vRecordArray: FIELD_INDEX = 0 to 2 / RECORD_INDEX = 0 to 5
vRecordArray (0,0) = 1	vRecordArray (1,0) = 100	vRecordArray (2,0) = "Text Field 2a"
vRecordArray (0,1) = 2	vRecordArray (1,1) = 200	vRecordArray (2,1) = "Text Field 2b"
vRecordArray (0,2) = 3	vRecordArray (1,2) = 300	vRecordArray (2,2) = "Text Field 2c"
vRecordArray (0,3) = 4	vRecordArray (1,3) = 400	vRecordArray (2,3) = "Text Field 2d"
vRecordArray (0,4) = 5	vRecordArray (1,4) = 500	vRecordArray (2,4) = "Text Field 2e"
vRecordArray (0,5) = 6	vRecordArray (1,5) = 600	vRecordArray (2,5) = "Text Field 2f"

The Asp Code
Rather than using numerical values for our first subscript (FIELD_INDEX), we'll assign constants named after our database fields. This way we may avoid maintenance and debugging difficulties.

'-----~~~~~- Record Field Constants ~~~~~~~~~~~~~ Const EXAMPLE_FIELD_ID = 0 '<- ExampleFieldID Const EXAMPLE_FIELD_1 = 1 '<- ExampleField1 Const EXAMPLE_FIELD_2 = 2 '<- ExampleField2

Now vRecordArray(2,4) can be stated as vRecordArray(EXAMPLE_FIELD_2 ,4)

We'll first need to instantiate the VB object within the asp file with the following statement and then assign values to the two parameters used in the method.

'~~~~~~~~~~~~~~~~~~ Set Object ~~~~~~~~~~~~~~~~~ Set oClass = Server.CreateObject("How2Project3.ClassName") '~~~~~~~~~~~~ Set method patameters ~~~~~~~~~~~~~ strDbConnectionString = "strDSN" strSQL = "Select ExampleFieldID, ExampleField1, " & _ "ExampleField2 FROM ExampleTable"

Assigning the Variant array is easy.

'~~~~~~~~~~ Get RecordSet Variant Array ~~~~~~~~~ vRecordArray = oClass.MethodName(strDbConnectionString, strSQL)

We end by setting out object to Nothing.

'~~~~~~~~~~~~~ Set Object to Nothing ~~~~~~~~~~~~ Set oClass = Nothing

We now have a Variant array filled with the record values we indicated in our SQL statement. For this example we'll use them to fill a HTML TABLE. To see how we'll accomplish this, read Part 3!