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
- Start a new project in Visual Basic and select "ActiveX DLL." Class1 is
created by default.
- 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.
- From the VB Project menu, click References. Find the Microsoft OLE DB
ActiveX Data Objects 1.0 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 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
- 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
- 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.
- From the File menu in VB click New Project, then Standard
EXE project. Form1 is created by default.
- 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.
- Place three Command buttons on Form1--Command1, Command2, and Command3
by default. Place one List box on Form1--List1 by default.
- 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
- 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.
- 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
- 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.
- 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.
- 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.
- 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