From ASP to VB ActiveX DLL
by Rupert Walsh - GUI Computing
You might be wondering, why move active server script to a DLL? Well, unlike the nice people here at AVDF, you may not want your code (and intellectual property) sitting on a public server somewhere, potentially available for all to see. In a commercial situation, you often won't sell your source code to a client.
However, if you're writing ASP script that will live on their server, they'll get it anyway. And, perhaps even worse, they'll be able to help you out by "tuning" it for you...
It's also a lot easier to debug in VB than it is in Visual Interdev. VB5 vs VID1? No contest.
Of course, there is a downside. With pure ASP, changing the site is as simple as changing the code. Using an ActiveX DLL, changing the site means stopping and restarting the WWW service, so this technique is not recommended for sites which must change often!
The process, once you get the hang of it, is pretty straightforward and it's a great way to encapsulate frequently used ASP code. For this example, I'm going to use a database search engine for contacts - this is code we use pretty regularly, so it was one of the first candidates to be moved.
Say you've got a table called 'People' with 'FirstName', 'Surname', 'telephone' etc. fields. The following code allows you to search by surname, but could easily be extended to search any field. I want the search results to either be presented as a list of matches, or as all the details of the exact match if found.
The basic approach is to create an ActiveX DLL with a class that can spit out HTML code directly. The pseudo-ASP framework is along the lines of:
Dim and Set the object 'Set local object as an instance of the class obj.Property = whatever 'Set some of its properties response.write obj.GetHTML() 'Call a function to generate the HTMLHere's the process for creating the ActiveX DLL:
Here's a cut down version of the ActiveX DLL code:
Option Explicit
Private msConnString As String 'Local copy of the Connection String
Private msResponsePageURL As String
Private msResponsePageQueryParam As String
Public Property Let ConnString(sConnString As String)
'ODBC Connection string
msConnString = sConnString
End Property
Public Property Get ConnString() As String
ConnString = msConnString
End Property
Public Property Let ResponsePageURL(sURL As String)
'URL of the calling page
msResponsePageURL = sURL
End Property
Public Property Get ResponsePageURL() As String
ResponsePageURL = msResponsePageURL
End Property
Public Property Let ResponsePageQueryParam(sParam As String)
'ID parameter name on calling page
msResponsePageQueryParam = sParam
End Property
Public Property Get ResponsePageQueryParam() As String
ResponsePageQueryParam = msResponsePageQueryParam
End Property
Public function GetSearchResultHTML(ByVal sSurname as string, Optional ByVal nID) as string
Dim conn1 As ADODB.Connection
Dim rs1 As ADODB.Recordset
Dim sSql As String
Dim sReturn As String ' Holds the HTML text that we'll return
dim nRecordCount as long
On Error GoTo error_handler
'Note that to use ADOR (a cut down ADODB without the connection object)
'you'll need to change the following code slightly.
Set rs1 = New ADODB.Recordset
Set conn1 = New ADODB.Connection
conn1.Open msConnString
If IsMissing(nId) Then
sSql = "SELECT * FROM people WHERE people.surname = '" & sSurname & "'"
Else
sSql = "SELECT * FROM people WHERE ID = " & nID
End If
'Open as Keyset so that recordcount is available
rs1.Open sSql, conn1, adOpenKeyset, adLockReadOnly
nRecordCount = rs1.RecordCount
Select Case mnRecordCount
Case 0 'No records found
sReturn = "Sorry, no matches were found."
Case 1 'We have a winner, write out all the details
sReturn = sReturn & "<table border=1>"
sReturn = sReturn & "<tr>"
sReturn = sReturn & "<td>First Name</td>"
sReturn = sReturn & "<td>" & rs1("FirstName") & "</td>"
sReturn = sReturn & "</tr>"
sReturn = sReturn & "<tr>"
sReturn = sReturn & "<td>Surname</td>"
sReturn = sReturn & "<td>" & rs1("Surname") & "</td>"
sReturn = sReturn & "</tr>"
'
'Other details would go in here
'
sReturn = sReturn & "</table>"
Case Else 'Write out the table with all matches
sReturn = "<table border=1>"
'The first row holds the titles
sReturn = sReturn & "<tr>"
sReturn = sReturn & "<td>ID</td>"
sReturn = sReturn & "<td>First Name</td>"
sReturn = sReturn & "<td>Surname</td>"
sReturn = sReturn & "</tr>"
'Add data rows to the table
Do While Not rs1.EOF
sReturn = sReturn & "<tr>"
' Use the class properties to find out where to link to
sReturn = sReturn & "<td><a href='" & _
msResponsePageURL & "?" & _
msResponsePageQueryParam & _
"=" & rs1("ID") & "'>" & _
rs1("ID") & "</a></td>"
sReturn = sReturn & "<td>" & rs1("FirstName") & "</td>"
sReturn = sReturn & "<td>" & rs1("Surname") & "</td>"
sReturn = sReturn & "</tr>"
rs1.MoveNext
Loop
sReturn = sReturn & "</table>"
End Select
GetAddressData = sReturn
Exit Function
error_handler:
GetAddressData = Err.Number & ": " & Err.Description
End function
Your component can then be called from ASP as follows:
Dim sSurname, nID
Dim objHTMLGenerator
Set objHTMLGenerator = server.createobject("Contacts.Search")
nID = Request("ID")
sSurname = Request("Surname")
objHTMLGenerator.ResponsePageURL = "search.ASP"
objHTMLGenerator.ResponsePageQueryParam = "ID"
objHTMLGenerator.ConnString = "DSN=Contacts"
response.write "<h2>Search Results</h2>"
If Trim(nID) = "" Then
response.write objHTMLGenerator.GetSearchResultHTML(sSurname)
Else
response.write objHTMLGenerator.GetSearchResultHTML("", nID)
End If
response.write "Records Found: " & objHTMLGenerator.RecordCount
The example I've provided here is fairly simple, but could easily be extended to include limiting result sets to, say 10 per page, and allowing navigation of the result sets AltaVista style. All you need to do is add the appropriate public properties and functions to your class, and away you go!
