Published: Wednesday, August 16, 2000
How To Pass a Recordset From a VB Component to an ASP Page
By Doug Dean
| Discuss this Article! |
|---|
| Got a question about this article? A comment to share? An idea to propose? If so,
you can participate in a discussion at
Doug Dean's "How To" Forum!
|
Introduction
This article serves as a quick "How To" example for sending a disconnected RecordSet from a VB server-side
component to an ASP page where record field values can be used. 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 http://www.dougdean.com.
The sample code simply exemplifies passing a disconnected RecordSet via a method. Recommended code procedures
like transactions, string buffering, use of GetRows, error handling, etc., have all been
eliminated for clarity.
Sending a RecordSet to an asp file is fairly straight forward, but it does involve a little know-how regarding
objects. 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 How2Project1.
THE VB COMPONENT - How2Project1.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 ADODB.Recordset
|
We want to return a RecordSet, so we declared the method as type ADODB.Recordset. It's best to
declare the parameters (strDbConnectionString and strSQL) as ByVal for
Internet connections.
The sql statement could be removed as a method parameter and hard coded in the VB component itself. I
frequently pass an AccountID as a parameter and concatenate it to the sql statement within the
method rather than send the entire sql statement.
|
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).
|
THE VB COMPONENT - How2Project1.vbp: 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
oRs.CursorLocation = adUseClient
Set oCmd.ActiveConnection = oConn
'~~~~~~~~~~~~~ Get RecordSet ~~~~~~~~~~~~~~~~~~~~
oRs.Open oCmd
|
Assigning The RecordSet
Since we're returning a RecordSet, we'll have to Set the RecordSet to the method name.
'~~~~~~~~~~~~~ Open the RecordSet ~~~~~~~~~~~~~~~
Set MethodName = oRs
Closing Up
Now we can close up and set our objects to Nothing.
'~~~~~~~~~~ Disconnect the RecordSet ~~~~~~~~~~~~
Set oRs.ActiveConnection = Nothing
'~~~~~~~~~~ Close the connection ~~~~~~~~~~~~~~~~
oConn.Close
'~~~~~~~~~~~~~ Set objects to nothing ~~~~~~~~~~~
Set oRs = Nothing
Set oCmd = Nothing
Set oConn = Nothing
|
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)
In Part 2 we'll take a look at creating the ASP page that grabs the
disconnected from the VB component we just created!
Read Part 2!
