Image of Navigational Map linked to Home / Contents / Search From ASP to VB ActiveX DLL

by Rupert Walsh - GUI Computing
Image of Line Break

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 HTML
Here'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 & "'"
         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>"
         sReturn = sReturn & "</table>"
      End Select
      GetAddressData = sReturn
      Exit Function

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

Written by: Rupert Walsh
April '98

Image of Arrow linked to Previous Article Image of Arrow linked to Next Article
Image of Line Break