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

By Doug Dean

Comments? Questions?
There's an on-line discussion forum on this article, so if you have any questions or comments, be sure to visit the forum and ask away!
[Visit the Forum]

- continued -

This article serves as a quick "How To" example for sending a Variant array containing the contents of a RecordSet from a VB server-side component to an asp file. The VB component will populate the Variant array via the ADO GetRows method. (For more information on using GetRows be sure to read: Using GetRows to Display "Skinny" Tables, Optimizing Database Display with GetRows, and Paging Records with GetRows.) The supporting database file, text, and code, can be downloaded here. If you are unfamiliar with writing a server-side VB dll, you can read some introductory articles at my site.

The sample code simply exemplifies passing a Variant array filled with a RecordSet via a method. Recommended code procedures like transactions, error handling, etc., have all been eliminated for clarity. This example will include a VB server-side dll (ActiveX dll) consisting of one class (ClassName) and one method (MethodName). The VB project is named How2Project3.

THE VB COMPONENT - How2Project3.vbp

The Function Definition
Two parameters will be passed to our component; a connection string (strDbConnectionString) and an sql statement (strSQL) - both of which are of type string.

Public Function MethodName(ByVal strDbConnectionString As String, _
                           ByVal strSQL As String) As Variant

We want to return a Variant array, so we declared the method as type Variant. Although the default type of a VB method is Variant, declaring it so clarifies the code. It's best to declare the parameters (strDbConnectionString and strSQL) as ByVal for Internet connections.

The connection string value will need to contain the Data Source Name (DSN) that should first be established with ODBC, although any valid database connection string will work here. ADO will also need to be referenced in the VB project (see an introductory VB component article on how to do this).

Connecting The Database
After instantiating the ADODB RecordSet, Command, and Connection objects, the connection string is used to open the database connection and then the appropriate values are assigned to the Command object before opening the RecordSet. There are numerous ways to obtain a RecordSet via ADO. This is one such way.

'~~~~~~~~~~~~~ Set ADO objects ~~~~~~~~~~~~~~~~~~
Dim oRs As New ADODB.Recordset
Dim oCmd As New ADODB.Command
Dim oConn As New ADODB.Connection

'~~~~~~ Open Database with method argument ~~~~~~
oConn.Open strDbConnectionString

'~~~~~~~~~~ Assign values to ADO objects ~~~~~~~~~
oCmd.CommandText = strSQL
oCmd.CommandType = adCmdText
Set oCmd.ActiveConnection = oConn

'~~~~~~~~~~~~~ Get RecordSet ~~~~~~~~~~~~~~~~~~~~
oRs.Open oCmd

Populating The Variant Array via GetRows
The GetRows Method returns a Variant whose value is a two-dimensional array. We'll assign the returned RecordSet field values directly to our methods' variable name. How to access each of the record field values stored within the Variant array will be covered in the asp file code section.

'~~~~~~~Assign RecordSet to Variant Array ~~~~~~~
MethodName = oRs.GetRows

Closing Up
We then close up and set our objects to Nothing.

'~~~~~Close RecordSet/Connection ~~~~~~~~~~~~~~~~

'~~~~~~~~~~~~~ Set objects to nothing ~~~~~~~~~~~
Set oRs = Nothing
Set oCmd = Nothing
Set oConn = Nothing

End Function

Now compile, or run, the VB component so the asp file can use it as an object (production servers will need to have the compiled dll registered on the system - again, see introductory articles on how this is accomplished).

Now that we've examined the VB component in depth, it's time to turn our attention to the ASP page. In Part 2 we'll look at this ASP page and how, exactly, it will obtain and access the contents of the variant array!

  • Read Part 2

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