ASP 101 - Active Server Pages 101 - Web03
The Place ASP Developers Go!



Windows Technology Windows Technology
15 Seconds
4GuysFromRolla.com
ASP 101
ASP Wire
VB Forums
VB Wire
WinDrivers.com
internet.commerce internet.commerce
Partners & Affiliates
ASP 101 is an
internet.com site
ASP 101 is an internet.com site
IT
Developer
Internet News
Small Business
Personal Technology

Search internet.com
Advertise
Corporate Info
Newsletters
Tech Jobs
E-mail Offers

ASP 101 News Flash ASP 101 News Flash



 Top ASP 101 Stories Top ASP 101 Stories
What is Adovbs.inc and Why Do I Need It?
An Overview of ASP.NET
Connections, Commands, And Procedures

QUICK TIP:
Make Sure Colors Exist
Show All Tips >>
ASP 101 RSS Feed ASP 101 Updates


Centralizing Database Access With Visual Basic ActiveX DLL's

Centralizing Database Access With Visual Basic ActiveX DLL's

by Carvin Wilson

Overview

By centralizing database code within ActiveX DLL's, you can simplify your development efforts, and decrease the learning curve for new developers. In this article, we will create a Visual Basic ActiveX DLL that controls database functionality for our Active Server Page (ASP) demo.

Why should I use COM?

Reason #1

By utilizing ActiveX DLL's, you can finally create that three-tiered application everyone talks about. By separating database functionality from ASP pages, you can minimize the changes needed within your ASP code.

Reason #2

If you are working on a large project, it might be a good idea to have the head guru construct and maintain a database class. By doing this, you have your best team member creating and maintaining your mission critical code. The database object will simplify database access for all developers. This will hopefully cut down on training, and time-consuming mistakes.

Tools used

The database access object was created in Visual Basic 6.0, utilizing Microsoft Data Access Components (MDAC) 2.6. You will notice that I am not calling setcomplete() or setabort(); I wanted to keep the code generic. Feel free to add your MTS or COM+ context code.

Class Information

The class contains some basic functions to get the creative juices flowing. The following information is given about each method.

  • fnSQL_RS(ByVal strSQL As String) - accepts a SQL statement and returns a record set.
  • fnSQL_XML(ByVal strSQL As String, ByVal strFilename As String) - accepts a SQL statement and filename, it saves the results as XML with the given filename. It returns TRUE if successful.
  • fnSP_RS(ByVal strSPName As String, ParamArray params() As Variant) - accepts the name of a stored procedure and array of parameters. It returns a record set.
  • fnSP(ByVal strSPName As String, ParamArray params() As Variant) - accepts the name of a stored procedure and array of parameters. It returns TRUE if the execution was successful.

The class also contains a property called ConnectionString, which is used to set connection information.

All the VB source code is included with this article; however, if you feel the need to do it from scratch, just create a new "ActiveX DLL" project, reference the required files, and start coding.

Visual Basic Code

Executing a SQL statement, returning a record set
Public Function fnSQL_RS(ByVal strSQL As String) As ADODB.Recordset
 On Error GoTo ErrorHandler
  Dim Conn As ADODB.Connection
  Dim rs As ADODB.Recordset
  'Create and set the connection information.
  Set Conn = CreateObject("ADODB.Connection")
  Conn.ConnectionString = mvarm_ConnectionString
  Conn.Open
  'Create and set the record set information
  Set rs = CreateObject("ADODB.Recordset")
  rs.ActiveConnection = Conn
  rs.CursorLocation = adUseClient
  rs.Source = strSQL
  rs.Open strSQL, , adOpenForwardOnly, adLockReadOnly
  Set fnSQL_RS = rs
  rs.ActiveConnection = Nothing
  Conn.Close
  Set Conn = Nothing
  Exit Function
ErrorHandler:
  Err.Raise Conn.Errors.Item(0).Number, _
    Conn.Errors.Item(0).Source, _
    Conn.Errors.Item(0).Description
  Set rs = Nothing
  Set Conn = Nothing
End Function

Executing a SQL statement, storing an XML file
(Note: function declaration line wrapped for readability.)
Public Function fnSQL_XML(ByVal strSQL As String,
	ByVal strFilename As String) As Boolean
 On Error GoTo ErrorHandler
  Dim Conn As ADODB.Connection
  Dim rs As ADODB.Recordset
  'Create and set the connection information.
  Set Conn = CreateObject("ADODB.Connection")
  Conn.ConnectionString = mvarm_ConnectionString
  Conn.Open
  'Create and set the record set information
  Set rs = CreateObject("ADODB.Recordset")
  rs.ActiveConnection = Conn
  rs.CursorLocation = adUseClient
  rs.Source = strSQL
  rs.Open strSQL, , adOpenForwardOnly, adLockReadOnly
  rs.Save strFilename, adPersistXML
  'A little clean up work
  Conn.Close
  Set rs = Nothing
  Set Conn = Nothing
  fnSQL_XML = True
  Exit Function
ErrorHandler:
  Err.Raise Err.Number, Err.Source, Err.Description
  Set rs = Nothing
  Set Conn = Nothing
  fnSQL_XML = False
End Function

Executing a stored procedure, returning a record set
(Note: function declaration line wrapped for readability.)
Public Function fnSP_RS(ByVal strSPName As String,
	ParamArray params() As Variant) As ADODB.Recordset
 On Error GoTo ErrorHandler
  Dim cmd As ADODB.Command
  Dim Conn As ADODB.Connection
  Dim rs As ADODB.Recordset
  'Create and set the connection information.
  Set Conn = CreateObject("ADODB.Connection")
  Conn.ConnectionString = mvarm_ConnectionString
  Conn.Open
  'Create and set the command object
  Set cmd = CreateObject("ADODB.Command")
  cmd.ActiveConnection = Conn
  cmd.CommandText = strSPName
  cmd.CommandType = adCmdStoredProc
  'Create and set the Record set object
  Set rs = CreateObject("ADODB.Recordset")
  rs.ActiveConnection = Conn
  rs.CursorLocation = adUseClient
  'Call function to set params and execute
  prSetParams cmd, params
  rs.Open cmd, , adOpenForwardOnly, adLockReadOnly
  Set fnSP_RS = rs
  'Clean up work
  rs.ActiveConnection = Nothing
  Conn.Close
  Set Conn = Nothing
  Exit Function
ErrorHandler:
  Err.Raise Conn.Errors.Item(0).Number, _
    Conn.Errors.Item(0).Source, _
    Conn.Errors.Item(0).Description
  Set cmd = Nothing
  Set Conn = Nothing
End Function

Executing a stored procedure with no results
(Note: function declaration line wrapped for readability.)
Public Function fnSP(ByVal strSPName As String,
	ParamArray params() As Variant) As Boolean
 On Error GoTo ErrorHandler
  Dim cmd As ADODB.Command
  Dim Conn As ADODB.Connection
  'Create and set the connection information.
  Set Conn = CreateObject("ADODB.Connection")
  Conn.ConnectionString = mvarm_ConnectionString
  Conn.Open
  'Create and set the command object
  Set cmd = CreateObject("ADODB.Command")
  cmd.ActiveConnection = Conn
  cmd.CommandText = strSPName
  cmd.CommandType = adCmdStoredProc
  'Call function to set params and execute
  prSetParams cmd, params
  cmd.Execute , , ADODB.adExecuteNoRecords
  'Clean up
  Conn.Close
  Set cmd = Nothing
  Set Conn = Nothing
  fnSP = True
  Exit Function
ErrorHandler:
  Err.Raise Conn.Errors.Item(0).Number, _
    Conn.Errors.Item(0).Source, _
    Conn.Errors.Item(0).Description
  Set cmd = Nothing
  Set Conn = Nothing
  fnSP = False
End Function

ASP Code

The below samples use the PUBS database. This database is installed as part of the SQL7 and SQL2K installation. I will not bore you with another lecture on how to register a ActiveX DLL on your MTS or COM+ server; however, make sure that they are registered prior to running this code.

Executing a SQL statement, returning a recordset
<%
Dim obj
Dim rs
'Create an instance of the class
set obj = Server.CreateObject("DataAccess.clsDataAccess")
'Set the connection string
obj.ConnectionString = "Provider=SQLOLEDB.1;Data Source=;" _
    & "Initial Catalog=PUBS;User id=;Password=;"
'Set execute and create our recordset
set rs = obj.fnSQL_RS("SELECT * FROM AUTHORS")
set obj = nothing
'Display something
While not rs.EOF
 Response.Write(Trim(rs("AU_LNAME")) + ", " + rs("AU_FNAME") + "<br>")
 rs.MoveNext
Wend
set rs = nothing
%>

Executing a SQL statement, creating an XML file
<%
Dim obj
Dim retVal
Dim strXMLFileName
'Create an instance of the class
set obj = Server.CreateObject("DataAccess.clsDataAccess")
'Set the connection string
obj.ConnectionString = "Provider=SQLOLEDB.1;Data Source=;" _
    & "Initial Catalog=PUBS;User id=;Password=;"
'Set execute and create our record set
strXMLFileName = Server.Mappath("demo.xml")
retval = obj.fnSQL_XML( _
    "SELECT AU_LNAME, AU_FNAME, PHONE FROM AUTHORS",strXMLFileName)
set obj = nothing
'Display something
If retval = True then 
 Response.Write("The XML File was created.")
Else
 Response.Write("Could not create the XML file.")
End If
%>

Executing a stored procedure, returning a record set
<%
Dim obj
Dim rs
'Create an instance of the class
set obj = Server.CreateObject("DataAccess.clsDataAccess")
'Set the connection string
obj.ConnectionString = "Provider=SQLOLEDB.1;Data Source=;" _
    & "Initial Catalog=PUBS;User id=;Password=;"
'Set execute and create our record set.
'This SP comes as part of the PUBS install
set rs = obj.fnSP_RS("byroyalty", _
    Array("@percentage", 3, adParamInput, 4, 40))
set obj = nothing
'Display something
Response.Write "It's not much, but you get the picture<br><br>"
While not rs.EOF
 Response.Write rs("AU_ID")+ "<br>"
 rs.MoveNext
Wend
set rs = nothing
%>

Executing a stored procedure with no results
Note: You must create the following SP in the PUBS database to run this sample.
create procedure spAuthorInsert(@au_id varchar(11),
 @au_lname varchar(40),@au_fname varchar(20),
 @phone varchar(20),@address varchar(40),
 @city varchar(20),@state char(2),
 @zip char(5),@contract smallint)
as begin
 insert into authors
  (au_id, au_lname, au_fname, phone, address,
   city, state, zip, contract)
 values
  (@au_id, @au_lname, @au_fname, @phone, @address, @city,
   @state, @zip, @contract)
end
<%
Dim obj
Dim retVal
Dim rs
'Create an instance of the class
set obj = Server.CreateObject("DataAccess.clsDataAccess")
'Set the connection string
obj.ConnectionString = "Provider=SQLOLEDB.1;Data Source=;" _
    & "Initial Catalog=PUBS;User id=;Password=;"
'Set execute and create our record set.
'This SP is in the PUBS database
retVal = obj.fnSP("spAuthorInsert", _ 
 Array("@au_id", 200, adParamInput, 11,"111-22-3333"), _  
 Array("@au_lname", 200, adParamInput, 40, "Wilson"), _ 
 Array("@au_fname", 200, adParamInput, 20, "Carvin"), _ 
 Array("@phone", 200, adParamInput, 20, "253-900-0001"), _  
 Array("@address", 200, adParamInput, 40, "1502 Main Street"), _  
 Array("@city", 200, adParamInput, 20, "Gig Harbor"), _ 
 Array("@state", 129, adParamInput, 2, "WA"), _ 
 Array("@zip", 129, adParamInput, 5, "98332"), _ 
 Array("@contract", 2, adParamInput, 4, 1))
set obj = nothing
'Display something
If retval = True Then
 Response.Write "The record was inserted"
Else
 Response.Write "Could not insert the record"
End If
%>

Summary

I hope I have spawned a few new ideas with all this source code =). If you have any questions, comments, or problems please do not hesitate to send me an e-mail.


Home |  News |  Samples |  Articles |  Lessons |  Resources |  Forum |  Links |  Search |  Feedback

Internet.com
The Network for Technology Professionals

Search:

About Internet.com

Legal Notices, Licensing, Permissions, Privacy Policy.
Advertise | Newsletters | E-mail Offers