When you think ASP, think...
Recent Articles
All Articles
ASP.NET Articles
Related Web Technologies
User Tips!
Coding Tips

Sample Chapters
JavaScript Tutorials
MSDN Communities Hub
Official Docs
Stump the SQL Guru!
XML Info
Author an Article
Print this page.
Published: Wednesday, August 23, 2000

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

By Doug Dean

  • Read Part 1

  • 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!

    - continued -

    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.


    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
    1100"Text Field 2a"
    2200"Text Field 2b"
    3300"Text Field 2c"
    4400"Text Field 2d"
    5500"Text Field 2e"
    6600"Text Field 2f"

    Array index structure of vRecordArray: FIELD_INDEX = 0 to 2 / RECORD_INDEX = 0 to 5
    vRecordArray (0,0) = 1vRecordArray (1,0) = 100vRecordArray (2,0) = "Text Field 2a"
    vRecordArray (0,1) = 2vRecordArray (1,1) = 200vRecordArray (2,1) = "Text Field 2b"
    vRecordArray (0,2) = 3 vRecordArray (1,2) = 300 vRecordArray (2,2) = "Text Field 2c"
    vRecordArray (0,3) = 4vRecordArray (1,3) = 400 vRecordArray (2,3) = "Text Field 2d"
    vRecordArray (0,4) = 5vRecordArray (1,4) = 500vRecordArray (2,4) = "Text Field 2e"
    vRecordArray (0,5) = 6vRecordArray (1,5) = 600vRecordArray (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!

  • Read Part 3

  • ASP.NET [1.x] [2.0] | ASPFAQs.com | Advertise | Feedback | Author an Article