HOWTO: Creating a VB Component that Returns a Recordset in ADC

Last reviewed: September 30, 1997
Article ID: Q166277
The information in this article applies to:
  • Microsoft Internet Information Server, version 3.0

SUMMARY

There are two ways to pass a recordset back from your server to the client with the Advanced Data Connector (ADC). One is to use AdvancedDataFactory, another is to create a custom ActiveX DLL. The following information outlines both methods.

AdvancedDataFactory

The ADC contains a server-side business object (ActiveX DLL) called the AdvancedDataFactory (ADF) that sends SQL statements to a DBMS, and passes the results back across the Internet or an intranet. This is provided as a default ActiveX DLL that allows ADC to provide live data to your Web page with little programming.

Here is a simple example of using the ADF from Visual Basic, Scripting Edition (VBS). This same VBS code works also inside a Visual Basic (VB) Project if you substitute the <OBJECT> tag with the VB CreateObject method (see examples later):

   <HTML>
   <HEAD></HEAD>
   <BODY>

   <!-- AdvancedDataSpace -->
   <OBJECT ID="ADS1" WIDTH=1 HEIGHT=1
     CLASSID="CLSID:99586D40-DB60-11CF-9D87-00AA00B91181"
     CODEBASE="HTTP://<%=Request.ServerVariables("SERVER_NAME")%>
     /MSADC/msadc10.cab">
   </OBJECT>

   <SCRIPT LANGUAGE="VBScript">
   Option Explicit
   Sub Window_OnLoad()
     dim ADF1, myRS
     set ADF1 = ADS1.CreateObject("AdvancedDataFactory", _
       "http://www.myserver.com")
     set myRS = ADF1.Query("DSN=pubs;UID=sa;PWD=;", _
       "select * from Authors")
     MsgBox myRS.Fields("au_lname")
   End Sub
   </SCRIPT>
   </BODY>
   </HTML>

Custom ActiveX DLL

You can also create your own custom ActiveX DLLs that run on the server and could contain methods that are not provided by the simple ADF ActiveX DLL. These methods do not have to be related to data access--they could just encompass a business rule (see Function SumValues).

To demonstrate this you will create a custom ActiveX DLL in Visual Basic 5.0, although you can use any application capable of creating an ActiveX DLL.

MORE INFORMATION

In this example you are going to create a VB5 ActiveX DLL that will be installed on the server and run under ADC from an Internet Explorer (IE) or VB client application. In these steps we will assume that your VB development machine is also your IIS/ADC server, so we do not have to cover the steps of VB application distribution. If you are not working from your IIS server, then you will just need to correctly register and mark the .dll file safe for launching, as explained later in this document.

Task One - Create the ActiveX DLL

  1. Start a new project in Visual Basic and select "ActiveX DLL." Class1 is created by default.

  2. From the VB Project menu select Project1 Properties. Change the Project Name to ADCTestObj and the Project Description to ADC Test Object. Click OK to close the Project Properties dialog box.

  3. From the VB Project menu, click References. Find the Microsoft OLE DB ActiveX Data Objects 1.0 Library and select it.

  4. Select Class1 in your Project Window and press F4 to view the Properties. Change the Instancing property of Class1 to 5 - MultiUse.

  5. Paste the following code into the General Declarations section of Class1:

          Public Function SumValues(lngVal1 As Long, lngVal2 As Long) As Long
    
            'this procedure is to test for minimum functionality
            SumValues = lngVal1 + lngVal2
          End Function
    
          Public Function ExecuteSQL(strConnect As Variant, strSQL As Variant)_
            As Long
            'Executes an action query, returns RecordsAffected
            On Error GoTo ehExecuteSQL
            Dim cn As New ADODB.Connection
            cn.Open strConnect
            cn.BeginTrans                     'begin a transaction
            cn.Execute strSQL, ExecuteSQL     'recordsetAffected is returned
            cn.CommitTrans                    'no errors, commit
          Exit Function
          ehExecuteSQL:
            'if transaction is not committed, it will be rolled back
            ExecuteSQL = -2                   '-2 indicates error condition
          End Function
    
          Public Function ReturnRs(strConnect As Variant, strSQL As Variant) _
            As ADODB.Recordset
            'returns an ADODB recordset
            On Error GoTo ehGetRecordset
            Dim cn As New ADODB.Connection
            Dim rs As New ADODB.Recordset
            cn.Open strConnect
            'these are not listed in the typelib
            rs.CursorLocation = adUseClientBatch
            'using the Unspecified parameters, an ADO/R recordset is returned
            rs.Open strSQL, cn, _
              adOpenUnspecified, adLockUnspecified, adCmdUnspecified
            Set ReturnRs = rs
          Exit Function
          ehGetRecordset:
            Err.Raise Err.Number, Err.Source, Err.Description
          End Function
    
    

  6. Save the project, then from the File menu click Make Adctestobj.dll. You are now finished creating your ActiveX DLL project, but you still need to test it before deploying it under IIS/ADC.

Task Two - Create the VB Test Client

  1. You will now test the ActiveX DLL project by creating another VB Standard EXE project as a client. This is done to test the functionality of your methods within VB where you have a good debugging environment.

  2. From the File menu in VB click New Project, then Standard EXE project. Form1 is created by default.

  3. From the File menu click Add Project, click the Recent tab, and select ADCTestObj. You will now have two projects listed in the Project window.

  4. Place three Command buttons on Form1--Command1, Command2, and Command3 by default. Place one List box on Form1--List1 by default.

  5. Paste the code into the General Declarations section of Form1:

          Dim rs  As Object               'ADO DB Recordset
          Dim ads As Object               'AdvancedDataSpace
          Dim bo As Object                'business object
    
          Private Sub Form_Load()
            'un-comment the next 1 line to test locally
            Set bo = CreateObject("ADCTestObj.Class1")    'for local component
    
            'un-comment the next 3 lines to test over HTTP
            'Set ads = CreateObject("AdvancedDataSpace")
            'for remote component
            'Set bo = ads.CreateObject("ADCTestObj.Class1", _
            '     " <http://[SERVER]> ")
    
          Private Sub Command1_Click()
            'minimum functionality test
            MsgBox bo.SumValues(2, 3)
          End Sub
    
          Private Sub Command2_Click()
            'Return a recordset
            'NOTE: change the Dsn, Uid, Pwd to match yours
            Set rs = bo.ReturnRs("dsn=pubs;uid=sa;pwd=", _
              "select * from authors")
            List1.Clear
            Debug.Print rs(0)
            While Not rs.EOF
              List1.AddItem rs("au_lname")
              rs.movenext
            Wend
          End Sub
    
          Private Sub Command3_Click()
            'Execute SQL within a transaction
            'NOTE: change the Dsn, Uid, Pwd to match yours
            Dim strSQL As Variant, lngRetVal As Long
            strSQL = "Update authors set au_lname = au_lname + 'x' " & _
              "Where au_id Like '172-32-1176'"
            lngRetVal = bo.ExecuteSQL("dsn=pubs;uid=sa;pwd=", strSQL)
           MsgBox "RecordsAffected: " & CStr(lngRetVal) & " (-2 is an error)"
          End Sub
    
    

  6. Run the project. Press Command1 to print 5 to the Debug window. Press Command2 to add the au_lname column to the ListBox1 control. Press Command3 to update the au_lname column.

  7. If you encounter any errors, you can step through your code to correct them. Since you are not running this through ADC yet, you only have to worry about debugging VB and ADO code. This is an important point because if you deploy your business object under ADC before you perform a functionality test, it will be much harder to find problems later.

Task Three - Test under ADC over HTTP

  1. After you have tested your business object to run successfully you can deploy it under ADC. Re-compile Adctestobj.dll with the latest changes and make sure it is correctly registered on your IIS/ADC server. If you are not developing on your server, you will need to manually copy over the DLL and use Regsvr32.exe to register it.

  2. Make sure your createable object has launch rights on your server. This can be done by taking the following lines and saving them in a file with a .reg extension and double-clicking on the file to merge the information into the registry. This can also be done manually with Regedit.exe.

          REGEDIT4
          ;this entry should be on one line
          [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W3SVC
    
            \Parameters\ADCLaunch\ADCTestObj.Class1]
    
    
If you want to later deploy this business object using DCOM instead of HTTP, you will need to mark it safe for scripting and initialization on each client. See the ADC Readme.txt file for more details.

  1. The last thing you need to do before testing it under ADC is make a few modifications in your VB client application. Comment out the following line:

          Set b = CreateObject("ADCTestObj.Class1")
    

    Now un-comment the next three lines so they will be executed:

          'Set ads = CreateObject("AdvancedDataSpace")
          'Set b = ads.CreateObject("ADCTestObj.Class1", _
    
          '    "http://[SERVER]")
    
    
You do this so your VB internet client application does not look for the ActiveX DLL locally, but rather on the IIS specified.

  1. Repeat Task 2, step 6, to test under IIS/ADC.

The following are some suggestions made earlier:

Always place one simple method in your VB server component to test for minimum functionality before attempting to pass recordsets back.

Build a simple VB client app to test your VB server component before deploying it and testing with IE. If you use VB5's multiple project feature you can actually step the code from the client right into the method in your ActiveX DLL.

It is easier to develop your VB application on your test server. If you develop it elsewhere, you will need to copy and register the DLL on the test server after each compile.

The data source name (DSN) passed to your ActiveX DLL needs to be a registered System DSN on your server. If it does not exist or is setup improperly, your component will fail. It is a good idea to test the DSN on the server with another ODBC application, such as MSQuery, to make sure the DSN is setup properly.

Don't forget to mark the component safe for launching on the server with a .reg file containing the following text (use your actual progid).

   REGEDIT4
   ;this entry should be on one line
   [HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\W3SVC
     \Parameters\ADCLaunch\ADCTestObj.Class1]

If you are still having problems after following all of these steps, please review the QueryBuilder application that is available for download in the msadcqry.exe file. This file can be found at the following address http://microsoft.com/adc/files.htm#top.

It uses a VB ActiveX DLL on the server that is called from an .asp page, and the code is included in \QueryBuilder\VBSource for you to review. The setup for QueryBuilder automatically registers the ActiveX DLL and marks if safe for launching.

REFERENCES

  • Web site for ADC: http://microsoft.com/adc
  • ADC public newsgroup: microsoft.public.adc
  • ADC Readme.txt file: \\[SERVER DIR]\Msadc\Doc\Readme.txt
  • ADC Help: \\[SERVER DIR]\Msadc\Doc\Madc10.hlp

(c) Microsoft Corporation 1997, All Rights Reserved. Contributions by Jon Fowler, Microsoft Corporation
Keywords          : iisMisc
Version           : 3.0
Platform          : NT WINDOWS
Issue type        : kbhowto


================================================================================


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: September 30, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.