How to package ASP scripts by using Visual Basic COM components (299988)

SUMMARY

This step-by-step procedure demonstrates how to package common Active Server Pages (ASP) scripts for easy re-use in Visual Basic Component Object Model (COM) components. The value of this example is to take scripts that are used extensively and put them into a dynamic link library (DLL) for easy re-use. You can reduce the amount of code that is needed in the Web page to generate an HTML table from a database from 10 or 20 lines to three lines.

The example script in this article is the creation of an HTML table that is generated from a database query. This article uses the Northwind database that is included with Microsoft SQL Server for the data.

Create a Visual Basic DLL project

On the Start menu, point to Programs, point to Microsoft Visual Studio 6.0, and then click Microsoft Visual Basic to start Visual Basic.
In the New Project dialog box, click ActiveX DLL, and then click Open to create a new ActiveX DLL project.
In the Project Explorer, select the project, and then press the F4 key to display the properties for the project. Highlight the current name, and then rename the project ASPCOM.
In the Project Explorer, click the class, highlight the current name, and then rename the class cHTMLTable.
From the Project menu, click References. In the resultant dialog box, select the following check boxes (in addition to what is already checked), and then click OK.
- COM+ Services Type Library (for Windows 2000) or Microsoft Transaction Server Type Library (for Windows NT 4.0)
- Microsoft ActiveX Data Objects 2.X Library (use the latest version on your computer)
- Microsoft Active Server Pages Object Library

You should have a blank window for the class. Highlight the following code, right-click the code, and then click Copy. In the class code window of Visual Basic, click Paste as HTML on the Edit menu to paste the code.

Private miBorder As Integer
Private msConstring As String
Private msSQL As String

Public Property Let Border(inVal As Variant)
   miBorder = CInt(inVal)
End Property
Public Property Get Border() As Variant
    Border = miBorder
End Property
Public Property Let ConnectionString(inVal As Variant)
    msConstring = inVal
End Property
Public Property Get ConnectionString() As Variant
    ConnectionString = msConstring
End Property
Public Property Let sqlStatement(inVal As Variant)
    msSQL = inVal
End Property
Public Property Get sqlStatement() As Variant
    sqlStatement = msSQL
End Property
Public Sub Init_and_Make(Border, ConnectionString, sqlStatement)
    Me.Border = Border
    Me.ConnectionString = ConnectionString
    Me.sqlStatement = sqlStatement
    Me.MakeTable
End Sub
Public Sub MakeTable()
    Dim objctx As ObjectContext
    Dim Resp As ASPTypeLibrary.Response
    Dim adCon As ADODB.Connection
    Dim adRS As ADODB.Recordset
    Dim iCount As Integer
    
    Set objctx = GetObjectContext()
    Set Resp = objctx.Item("Response")
    
    Set adCon = New ADODB.Connection
    Set adRS = New ADODB.Recordset
    adCon.ConnectionString = msConstring
    adCon.Open
    Set adRS.ActiveConnection = adCon
    adRS.Open msSQL, , adOpenForwardOnly, adLockReadOnly
    If adRS.EOF And adRS.BOF Then
        Resp.Write "NO RECORDS RETURNED<BR>"
        Exit Sub
    End If
    
    With Resp
        .Write "<TABLE BORDER = " & miBorder & ">"
        .Write "<TR>"
        For iCount = 0 To adRS.Fields.Count - 1
            .Write "<TD>" & adRS.Fields(iCount).Name & "</TD>"
        Next
        .Write "</TR>"
        Do While Not adRS.EOF
            .Write "<TR>"
            For iCount = 0 To adRS.Fields.Count - 1
                .Write "<TD>" & adRS.Fields(iCount).Value & "</TD>"
            Next
            .Write "</TR>"
            adRS.MoveNext
        Loop
        .Write "</TABLE>"
    End With
    Set Resp = Nothing
    adRS.Close
    adCon.Close
    Set adRS = Nothing
    Set adCon = Nothing
    Set objctx = Nothing
End Sub

From the File menu, click Save Project.
From the File menu, click Make ASPCOM.DLL.

Create an ASP page to use the object

From the Windows Start menu, point to Programs, point to Microsoft Visual Studio, and then click Visual InterDev to start Visual InterDev 6.0.
In the New Project dialog box, type ASPCOM in the Name text box.
Ensure that New Web Project is selected, and click Open to proceed to the next step in the wizard.
On the next page, in the What server do you want to use? text box, type the name of a Microsoft Internet Information Server (IIS) server. Ensure that Master Mode is selected, and then click Next to continue.
Ensure that Create a new Web application is selected, and then click Finish to complete the wizard.
In the Project Explorer window, right-click the project (which should be similar to "IIS Server\ASPCOM"), point to Add, and then click Active Server Page.
In the Add Item dialog box, ensure that ASP Page is selected, and then click Open to add a new ASP page. You can change the name of the page if you want.
Place the following code between the <BODY> and </BODY> tags of the ASP page that you created earlier. Highlight the code, right-click the code, and then click Copy. In Visual Basic, click Paste as HTML on the Edit menu to paste the code.
```
<%
dim obj, constr
constr = "Provider=SQLOLEDB.1;Password=<password>;" & _
         "Persist Security Info=False;User ID=<username>;" & _
         "Initial Catalog=Northwind;Data Source=<servername>"

set obj = server.CreateObject("aspcom.chtmltable")
obj.Border= 2
obj.ConnectionString = constr
obj.SQLStatement = "select * from customers"
obj.MakeTable

set obj = nothing
%>
						
```
NOTE: Be sure to modify the connect string in the preceding code samples so that they contain the correct username, password, and servername parameters for your database.
Save the file. Right-click in a blank area of the Design window, and then click View in Browser from the View menu. Notice that the file is displayed in your browser.

Troubleshooting