HOWTO: Create a VB Component that Returns a Recordset in RDS
ID: Q166277
|
The information in this article applies to:
-
Remote Data Service for ADO versions 1.5, 2.0, 2.1 SP2
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):
<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;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
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:
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;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
- 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.
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.
REFERENCES
Remote Data Services version 1.5 Readme.txt file: \\[SERVER DIR]\Msadc\Docs\Readme.txt
Data Access Software Development Kit (SDK) version 2.0 DASDKReadme.txt
file: \\MSDASDK\doc\DASDKReadme.txt
ActiveX Data Objects version 2.0 ADOReadme.txt file: \\[SERVER
DIR]\ado\ADOreadme.txt
Remote Data Services version 1.5 Help: \\[SERVER
DIR]\Msadc\Docs\default.htm
Data Access Software Development Kit (SDK) Help: \\MSDASDK\doc\dasdk.chm
For additional information, please see the following articles in the
Microsoft Knowledge Base:
Q181092
FAQ: Remote Data Service (RDS) Frequently Asked Questions
For additional information, please see the following World Wide Web URL:
http://microsoft.com/data/rds/
For additional information, please see the following public newsgroup:
microsoft.public.ado.rds
© Microsoft Corporation 1999, All Rights Reserved. Contributions by Wendy Aldrich, Microsoft Corporation
Additional query words:
MDAC ADC
Keywords : kbADO kbRDS kbGrpVBDB kbGrpMDAC iisMisc
Version : WINDOWS:1.5,2.0,2.1 SP2
Platform : WINDOWS
Issue type : kbhowto
|