Hi vbmo,
I am sure there are a couple of tutorials / faqs on the net describing how to create a
VB DLL using ADO, but I couldn't find one in my bookmarks. However, it's not too difficult. Using ADO in
VB is more or less the same as in ASP. The biggest thing to watch for is passing parameters in and out of procedures. The type (strongly typed versus a Variant) of the parameters is important.
You can read more about that here:
http://support.microsoft.com:80/supp...NoWebContent=1
OK, now for the "mini tutorial"
1. Start a new ActiveX DLL project and add a reference to the
Microsoft ActiveX Data Objects 2.5/2.6
2. Create a property (type string) to hold the connection string. Alternatively, add the connection string as a hard coded value, although this is not recommended.
3. Add a method (a Sub) like ExecSproc with the following code:
Code:
Public Sub ExecSproc(ByVal NameOfSproc As String)
'===============================================================
' NAME: ExecSproc
' DATE CREATED:
' CREATED BY:
' CREATED FOR:
' FUNCTION: Executes a sproc that does not return any records
' IN: NameOfSproc. The name of the sproc to execute
' OUT: Nothing
' RETURNS: Nothing
' VERSION: 1
' EXAMPLE:
'===============================================================
On Error GoTo errorHandler
Dim connConnection As ADODB.Connection
Dim cmdCommand As ADODB.Command
Set connConnection = New ADODB.Connection
Set cmdCommand = New ADODB.Command
With connConnection
.Open YourConnectionString
End With
With cmdCommand
' Set command properties
.ActiveConnection = connConnection
.CommandType = adCmdStoredProc
.CommandText = NameOfSproc
End With
' Now execute without using a return value
With cmdCommand
.Execute , , adExecuteNoRecords
.ActiveConnection = Nothing
End With
' And clean up
Set cmdCommand = Nothing
Set connConnection = Nothing
Exit Sub
errorHandler:
If connConnection.State = adStateOpen Then
connConnection.Close
End If
Set cmdCommand = Nothing
Set connConnection = Nothing
End Sub
4. Compile the DLL and place it inside a COM+ package. See
http://www.aspfree.com/authors/rober...ult.asp?aid=96 for more info on this.
5. Now you can use the DLL as follows in ASP:
Code:
Dim objDataAccess
Set objDataAccess = Server.CreateObject("MyProject.MyClassName")
objDataAccess.ExecSproc("sprocUpdatePrice")
Set objDataAccess = Nothing
As you can see, this is just a very short description of the situation. Of course you'll need more methods, properties etc to make this a successful Data Access component. Methods that you can implement are:
1. ExecSprocReturnRecordset - returns a strongly typed ADODB.Recordset
2. ExecSprocReturnArray - returns a Variant()
3. ExecSprocReturnInteger
4. ExecSQLReturnRecordset - accepts plain SQL instead of a sproc.
You'll also need a way to set up incoming parameters for the procedures.
All this code is based on the FMStocks sample site that Vertigo / Microsoft provided. If you go to
http://www.fmstocks.com and download the FMStock 2000 sample project, you'll find all the stuff you need.
It looks like it's time I write a tutorial on how to create a DAL using the FMStock sample as a basis ;)
Cheers,
Imar
Creating Activex DLL components
