How To Create a VB Component that Returns a Recordset in RDS (166277)

SUMMARY

There are two ways to pass a recordset back from your server to the client with Remote Data Service (RDS). One is to use RDS DataFactory, and the second way is to create a custom ActiveX DLL. This article describes both methods.

RDS.DataFactory

RDS contains a server-side business object (ActiveX DLL) called the RDSDataFactory (RDF) that sends SQL statements to a database management system (DBMS), and passes the results back across the Internet or an intranet. This is provided as a default ActiveX DLL that allows RDS to provide live data to your Web application with little programming.

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

Note You must change Username=<username> and PWD=<strong password> to the correct values before you run this code. Make sure that Username has the appropriate permissions to perform this operation on the database.

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

      <!-- RDS.DataSpace -->
      <OBJECT ID="RDS1" WIDTH=1 HEIGHT=1
        CLASSID="CLSID:BD96C556-65A3-11D0-983A-00C04FC29E36">
      </OBJECT>

      <SCRIPT LANGUAGE="VBScript">
      Option Explicit
      Sub Window_OnLoad()
        dim RDF1
        dim myRS
        set RDF1 = RDS1.CreateObject("RDSServer.DataFactory", _
         "http://<server name>")
        set myRS = RDF1.Query("DSN=pubs;Username=<username>;PWD=<strong password>;", _
          "select * from Authors")
      'pubs must be a system dsn
                ' or
      '  set myRS = RDF1.Query("provider=sqloledb;Username=<username>;PWD=<strong password>;" & _
           "Initial Catalog=Pubs;Data Source=<server name>", "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 contain methods that are not provided by the simple RDF ActiveX DLL. These methods do not have to be related to data access, they could just encompass a business rule (see the SumValues function).

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

MORE INFORMATION

In this example you are going to create a Visual Basic ActiveX DLL that is installed on the server and runs under RDS from Internet Explorer or a Visual Basic client application. In these steps assume that your Visual Basic development computer is also your Internet Information Server (IIS)/RDS server, so you do not have to cover the steps of Visual Basic application distribution. If you are not working from your IIS server, then you just need to correctly register and mark the .dll file safe for launching, as explained later in this document.

Creating the ActiveX DLL

Start a new project in Visual Basic (VB) and select "ActiveX DLL". Class1 is created by default.
From the Visual Basic Project menu, select Project1 Properties. Change the Project name to RDSTestObj and the Project Description to RDS Test Object. Click OK to close the Project Properties dialog box.
From the Visual Basic Project menu, click References. Find the Microsoft ActiveX Data Objects Library and select it.
Select Class1 in your Project Window and press F4 to view the Properties. Change the Instancing property of Class1 to "5 MultiUse".

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

      Public Function SumValues(lngVal1 As Integer, lngVal2 As Integer) _
         As Integer
        'This procedure is to test for minimum functionality.
         SumValues = lngVal1 + lngVal2
      End Function

     Public Function ExecuteSQL(strConnect As Variant, strSQL As Variant) _
          As Variant

        '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 = adUseClient
        '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

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

Creating the Visual Basic Test Client

You now test the ActiveX DLL project by creating another Visual Basic Standard EXE project as a client. This is done to test the functionality of your methods within Visual Basic where you have a good debugging environment.
In Visual Basic, from the File menu click New Project, and then choose Standard EXE project. Form1 is created by default.
From the File menu, click Add Project, click the Recent tab, and select RDSTestObj. You now have two projects listed in the Project window.
Place three Command buttons on Form1 named Command1, Command2, and Command3 respectively by default. Place one List box on Form1, named List1 by default.

Paste the following code into the General Declarations section of Form1:

Note You must change Username=<username> and PWD=<strong password> to the correct values before you run this code. Be sure that Username has the appropriate permissions to perform this operation on the database.

      Dim rs  As Object               'ADO DB Recordset
      Dim rds As Object               'RemoteDataSpace
      Dim bo As Object                'Business object

      Private Sub Form_Load()
        'Un-comment the next line to test locally.
        Set bo = CreateObject("RDSTestObj.Class1")    'For local component.

        'Un-comment the next 3 lines to test over HTTP.
        'Set rds = CreateObject("RDS.DataSpace")
        'Set bo = rds.CreateObject("RDSTestObj.Class1", _
        '     " <http://[SERVER]> ")
      End Sub

      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;Username=<username>;PWD=<strong password>;", _
          "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;Username=<username>;PWD=<strong password>;", strSQL)
       MsgBox "RecordsAffected: " & CStr(lngRetVal) & " (-2 is an error)"
      End Sub

Run the project. Press Command1 to display the sum of 2+3 to a message box. Press Command2 to add the au_lname column to the ListBox1 control. Press Command3 to update the au_lname column.
If you encounter any errors, you can step through your code to correct them. Since you are not running this through RDS yet, you only have to worry about debugging Visual Basic and ActiveX Data Objects code. This is an important point because if you deploy your business object under RDS before you perform a functionality test, it will be much harder to find problems later.

Test under RDS over HTTP

After you have tested your business object to run successfully you can deploy it under RDS. Remove the ActiveX DLL project (RDSTestObj) from the project group. Then use Regsvr32.exe to register the dll. Check to make sure the Rdstestobj business object correctly registered on your IIS/RDS server. To see if the component correctly registered look in the computers registry by running "regedit" from the Run menu. The component is under the HKEY_CLASSES_ROOT key in alphabetical order. If you are not developing on your server, you need to manually copy over the DLL and use Regsvr32.exe to register it.
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 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\RDSTestObj.Class1]
					
```
The last thing you need to do before testing it under RDS is make a few modifications in your Visual Basic client application. Comment out the following line:
```
Set bo = CreateObject("RDSTestObj.Class1")
					
```
Now un-comment the next three lines so they execute:
```
'Set rds = CreateObject("RDS.DataSpace")
'Set bo = rds.CreateObject("RDSTestObj.Class1", _
'    "http://[SERVER]")
					
```
This is done so the Visual Basic Internet client application does not look for the ActiveX DLL locally, but rather on the IIS specified server.
Change the [SERVER] code to reflect the correct IIS server.

Note for IIS server under Windows 2000: The security settings for IIS may need to be changed because the default settings are very restrictive. In order to enable RDS, the server administrator must follow these steps:

Click Start, point to Programs, then point to Administrative Tools. Click Internet Services Manager.
Expand the <computer name> in the left pane.
Expand the Default Web Site.
Right-click on the MSADC virtual directory and select Properties.
On the Directory Security tab, under IP address and domain name restrictions, click Edit. The IP Address and Domain Name Restrictions dialog box will appear.
In order to enable RDS-based applications and pages on this server, do either of the following:
- If you want all clients to access RDS-based pages and applications, then select Granted Access.
- If you want to grant access only to selected clients, then click Add to enter their IP addresses or domain names.

For more information on setting IP address and domain name restrictions, see the IIS Documentation.

The following are some suggestions made earlier:

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

Build a simple Visual Basic client application to test your Visual Basic server component before deploying it and testing with Internet Explorer. If you use Visual Basic'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 Visual Basic 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 fails. 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.

Do not 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\RDSTestObj.Class1]

If you want to later deploy this business object using DCOM instead of HTTP there are some additional steps that need to be done. These steps differ between RDS version 1.5 and RDS 2.x.

For use with RDS 1.5:

You need to mark it safe for scripting and initialization on each client. Please see the RDS 1.5 Documentation, Remote Data Service Developer's Guide/Developing Remote Data Service Applications/Getting a Recordset to the Client/Getting a Recordset with a Custom Business Object/Required Custom Business Object Registry Entry file for more details.

For use with RDS 2.x:

You no longer need to mark components safe for scripting and initialization on each client. But, you still need to register the components on the client computers. RDS 2.x also handles DCOM streaming differently then 1.5 and if you are using 1.1 or 1.5 client components. Follow the steps in the RDS documentation by searching for "Client-side Registry Entries for Business Objects with DCOM" to add registry keys to support RDS 1.1 or RDS 1.5 client components.