Option Explicit On 
Option Strict Off
Option Compare Text


'=============================================================================================================
'
' cODBC.vb
' --------
'
' Created By  : Kevin Wilson
'               http://www.TheVBZone.com   ( The VB Zone )
'               http://www.TheVBZone.net   ( The VB Zone .net )
'
' Last Update : April 20, 2005
' Created On  : April 19, 2005
'
' VB Versions : VB.NET 1.1 (VS.NET 2003)
'
' Requires    : The .NET Framework v1.1
'
' Description : This class module makes it easy to access your database via ODBC and manage the information
'               returned.
'
' See Also    : http://msdn.microsoft.com/vstudio/using/understand/data/default.aspx?pull=/library/en-us/dndotnet/html/usingadonet.asp
'               http://msdn.microsoft.com/vstudio/using/understand/data/default.aspx?pull=/library/en-us/dnbda/html/daag.asp
'               http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/adonetbest.asp
'               http://msdn.microsoft.com/library/en-us/cpref/html/frlrfSystemDataOdbc.asp
'
' Example Use :
'
'-------------------------------------------------------------------------------------------------------------
' "DataSet" object returned
'-------------------------------------------------------------------------------------------------------------
'
' Dim objDB As New cODBC("driver={SQL Server};server=127.0.0.1;Initial Catalog=pubs;Persist Security Info=False;UID=pubs;PWD=password;", 30, 120)
' Dim objDS As Data.DataSet
' Dim objDR As Data.DataRow
' Dim intRows As Integer
' Dim intErrNum As Integer
' Dim strErrSrc As String
' Dim strErrDesc As String
' If objDB.ExecuteSQL("SELECT * FROM [jobs]", CommandType.Text, objDS, False, intRows, intErrNum, strErrSrc, strErrDesc) = False Then
'    Debug.WriteLine("The following error occured:" & vbCrLf & vbCrLf & "Error Number = " & intErrNum & vbCrLf & "Error Source = " & strErrSrc & vbCrLf & "Error Description = " & strErrDesc)
' Else
'    For Each objDR In objDS.Tables(0).Rows
'       Debug.WriteLine(CStr(objDR("job_id") & "") & " / " & CStr(objDR("job_desc") & ""))
'    Next
'    objDR = Nothing
'    objDS = Nothing
' End If
' objDB = Nothing
'
'-------------------------------------------------------------------------------------------------------------
' "DataTable" object returned
'-------------------------------------------------------------------------------------------------------------
'
' Dim objDB As New cODBC("driver={SQL Server};server=127.0.0.1;Initial Catalog=pubs;Persist Security Info=False;UID=pubs;PWD=password;", 30, 120)
' Dim objDT As Data.DataTable
' Dim objDR As Data.DataRow
' Dim intRows As Integer
' Dim intErrNum As Integer
' Dim strErrSrc As String
' Dim strErrDesc As String
' If objDB.ExecuteSQL("SELECT * FROM [jobs]", CommandType.Text, objDT, False, intRows, intErrNum, strErrSrc, strErrDesc) = False Then
'    Debug.WriteLine("The following error occured:" & vbCrLf & vbCrLf & "Error Number = " & intErrNum & vbCrLf & "Error Source = " & strErrSrc & vbCrLf & "Error Description = " & strErrDesc)
' Else
'    For Each objDR In objDT.Rows
'       Debug.WriteLine(CStr(objDR("job_id") & "") & " / " & CStr(objDR("job_desc") & ""))
'    Next
'    objDR = Nothing
'    objDT = Nothing
' End If
' objDB = Nothing
'
'-------------------------------------------------------------------------------------------------------------
' "SqlDataReader" object returned
'-------------------------------------------------------------------------------------------------------------
'
' Dim objDB As New cODBC("driver={SQL Server};server=127.0.0.1;Initial Catalog=pubs;Persist Security Info=False;UID=pubs;PWD=password;", 30, 120)
' Dim objDR As Odbc.OdbcDataReader
' Dim intEffected As Integer
' Dim intErrNum As Integer
' Dim strErrSrc As String
' Dim strErrDesc As String
' If objDB.ExecuteSQL("SELECT * FROM [jobs]", CommandType.Text, objDR, False, intEffected, intErrNum, strErrSrc, strErrDesc) = False Then
'    Debug.WriteLine("The following error occured:" & vbCrLf & vbCrLf & "Error Number = " & intErrNum & vbCrLf & "Error Source = " & strErrSrc & vbCrLf & "Error Description = " & strErrDesc)
' Else
'    Do While objDR.Read = True
'       Debug.WriteLine(CStr(objDR.Item(1) & "") & " / " & CStr(objDR.Item(2) & ""))
'    Loop
'    objDR = Nothing
' End If
' objDB = Nothing
'
'-------------------------------------------------------------------------------------------------------------
' DELETE SQL statement executed using "SqlDataReader"
'-------------------------------------------------------------------------------------------------------------
'
' Dim objDB As New cODBC("driver={SQL Server};server=127.0.0.1;Initial Catalog=pubs;Persist Security Info=False;UID=pubs;PWD=password;", 30, 120)
' Dim objDR As Odbc.OdbcDataReader
' Dim intEffected As Integer
' Dim intErrNum As Integer
' Dim strErrSrc As String
' Dim strErrDesc As String
' If objDB.ExecuteSQL("DELETE FROM [jobs] WHERE [job_id] = 16", CommandType.Text, objDR, False, intEffected, intErrNum, strErrSrc, strErrDesc) = False Then
'    Debug.WriteLine("The following error occured:" & vbCrLf & vbCrLf & "Error Number = " & intErrNum & vbCrLf & "Error Source = " & strErrSrc & vbCrLf & "Error Description = " & strErrDesc)
' Else
'    Debug.WriteLine("Successfully deleted " & intEffected & " record(s)")
' End If
' objDB = Nothing
'
'-------------------------------------------------------------------------------------------------------------
' DataGrid control populated by a "DataSet" 
'-------------------------------------------------------------------------------------------------------------
'
' Dim objDB As cODBC
' Dim objDS As DataSet
' Dim intErrNum As Integer
' Dim strErrSrc As String
' Dim strErrDesc As String
' objDB = New cODBC("driver={SQL Server};server=127.0.0.1;Initial Catalog=pubs;Persist Security Info=False;UID=pubs;PWD=password;", 30, 120)
' If objDB.ExecuteSQL("SELECT * FROM [jobs]", CommandType.Text, objDS, False, , intErrNum, strErrSrc, strErrDesc) = False Then
'    MsgBox("The following error occured:" & vbCrLf & vbCrLf & "Error Number = " & intErrNum & vbCrLf & "Error Source = " & strErrSrc & vbCrLf & "Error Description = " & strErrDesc)
' Else
'    DataGrid1.DataSource = objDS
' End If
' objDB = Nothing
'
'=============================================================================================================
'
' LEGAL:
'
' You are free to use this code as long as you keep the above heading information intact and unchanged. Credit
' given where credit is due.  Also, it is not required, but it would be appreciated if you would mention
' somewhere in your compiled program that that your program makes use of code written and distributed by
' Kevin Wilson (www.TheVBZone.com).  Feel free to link to this code via your web site or articles.
'
' You may NOT take this code and pass it off as your own.  You may NOT distribute this code on your own server
' or web site.  You may NOT take code created by Kevin Wilson (www.TheVBZone.com) and use it to create products,
' utilities, or applications that directly compete with products, utilities, and applications created by Kevin
' Wilson, TheVBZone.com, or Wilson Media.  You may NOT take this code and sell it for profit without first
' obtaining the written consent of the author Kevin Wilson.
'
' These conditions are subject to change at the discretion of the owner Kevin Wilson at any time without
' warning or notice.  Copyright© by Kevin Wilson.  All rights reserved.
'
'=============================================================================================================


Imports System.Data
Imports System.Data.Odbc
Imports Microsoft
Imports Microsoft.VisualBasic

Public Class cODBC

   Private Enum ReturnType
      rt_DataReader = 0
      rt_DataTable = 1
      rt_DataSet = 2
   End Enum

#Region "Property Variable Declarations"

   Private WithEvents m_oDbConnection As OdbcConnection
   Private m_oTransaction As OdbcTransaction
   Private m_sConnString As String
   Private m_iTimeOutConn As Integer
   Private m_iTimeOutCmd As Integer

#End Region

#Region "Public Events"
   Public Event StateChange(ByVal sender As Object, ByVal e As System.Data.StateChangeEventArgs)
   Public Event InfoMessage(ByVal sender As Object, ByVal e As System.Data.Odbc.OdbcInfoMessageEventArgs)
#End Region

#Region "Private Events"

   Private Sub m_oDbConnection_StateChange(ByVal sender As Object, ByVal e As System.Data.StateChangeEventArgs) Handles m_oDbConnection.StateChange
      RaiseEvent StateChange(sender, e)
   End Sub

   Private Sub m_oDbConnection_InfoMessage1(ByVal sender As Object, ByVal e As System.Data.Odbc.OdbcInfoMessageEventArgs) Handles m_oDbConnection.InfoMessage
      RaiseEvent InfoMessage(sender, e)
   End Sub
   
#End Region

#Region "Constructors"

   Public Sub New()
      m_iTimeOutConn = 30
      m_iTimeOutCmd = 120
      m_sConnString = ""
   End Sub

   Public Sub New(ByVal strConnectionString As String, Optional ByVal intTimeOut_Conn As Integer = 30, Optional ByVal intTimeOut_Cmd As Integer = 120)
      m_iTimeOutConn = intTimeOut_Conn
      m_iTimeOutCmd = intTimeOut_Cmd
      m_sConnString = strConnectionString
      If Right(m_sConnString, 1) <> ";" Then m_sConnString = m_sConnString & ";"
   End Sub

#End Region

#Region "Deconstructors"

   Protected Overrides Sub Finalize()

      CleanUpConnection(m_oDbConnection)
      MyBase.Finalize()

   End Sub

#End Region

#Region "Public Properties"

   ' The following are several examples of possible OLEDB connection strings:
   ' MS SQL         = "Driver={SQL Server};Server=MyServer;Trusted_Connection=yes;Database=Northwind;"
   ' ORACLE         = "Driver={Microsoft ODBC for Oracle};Server=ORACLE8i7;Persist Security Info=False;Trusted_Connection=yes"
   ' MS Access      = "Driver={Microsoft Access Driver (*.mdb)};DBQ=c:\bin\nwind.mdb"
   ' MS Excel       = "Driver={Microsoft Excel Driver (*.xls)};DBQ=c:\bin\book1.xls"
   ' Text File      = "Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=c:\bin"
   ' DSN Connection = "DSN=dsnname"
   Public Property ConnectionString() As String
      Get
         Return m_sConnString
      End Get
      Set(ByVal Value As String)
         m_sConnString = Value
         If Right(m_sConnString, 1) <> ";" Then m_sConnString = m_sConnString & ";"
      End Set
   End Property

   ' Gets or sets the number of seconds to wait for a connection to complete before timing out
   Public Property TimeOut_Connect() As Integer
      Get
         Return m_iTimeOutConn
      End Get
      Set(ByVal Value As Integer)
         If Value >= 0 Then m_iTimeOutConn = Value
      End Set
   End Property

   ' Gets or sets the number of seconds to wait for a SQL query to execute before timing out
   Public Property TimeOut_Execute() As Integer
      Get
         Return m_iTimeOutCmd
      End Get
      Set(ByVal Value As Integer)
         If Value >= 0 Then m_iTimeOutCmd = Value
      End Set
   End Property

   ' Gets or sets the connection object that this class uses to communicate with the database
   Public Property ConnectionObject() As OdbcConnection
      Get
         Return m_oDbConnection
      End Get
      Set(ByVal Value As OdbcConnection)
         m_oDbConnection = Value
      End Set
   End Property

   ' Gets the current transaction object which was created via the "TransactionBegin" method
   Public ReadOnly Property TransactionObject() As OdbcTransaction
      Get
         Return m_oTransaction
      End Get
   End Property

#End Region

#Region "Public Methods"

   ' Begins a transaction.  All queries executed after this, and before calling "TransactionCommit" or "TransactionRollback", will be wrapped in a transaction (all execute, or none do)
   Public Function TransactionBegin(Optional ByRef Return_ErrNum As Integer = 0, _
                                    Optional ByRef Return_ErrSrc As String = "", _
                                    Optional ByRef Return_ErrDesc As String = "") As Boolean
      On Error GoTo ErrorTrap

      ' Set default values
      Return_ErrNum = 0
      Return_ErrSrc = ""
      Return_ErrDesc = ""

      ' Make sure there's a valid connection
      If CheckConnection(Return_ErrNum, Return_ErrSrc, Return_ErrDesc) = False Then Return False

      ' If a transaction was already started, commit it before creating another
      If Not m_oTransaction Is Nothing Then
         m_oTransaction.Commit()
         m_oTransaction = Nothing
      End If

      ' Start the transaction
      m_oTransaction = m_oDbConnection.BeginTransaction

      ' Function executed successfully
      Return True

ErrorTrap:

      Return_ErrNum = Err.Number
      Return_ErrSrc = Err.Source
      If Return_ErrSrc = "" Then
         Return_ErrSrc = "TransactionBegin()"
      Else
         Return_ErrSrc = Return_ErrSrc & " [TransactionBegin()]"
      End If
      Return_ErrDesc = Err.Description
      Err.Clear()

   End Function

   ' Commits all SQL queries that have occured since calling the "TransactionBegin" method
   Public Function TransactionCommit(Optional ByRef Return_ErrNum As Integer = 0, _
                                     Optional ByRef Return_ErrSrc As String = "", _
                                     Optional ByRef Return_ErrDesc As String = "") As Boolean
      On Error GoTo ErrorTrap

      ' Set default values
      Return_ErrNum = 0
      Return_ErrSrc = ""
      Return_ErrDesc = ""

      ' Start the transaction
      If m_oTransaction Is Nothing Then
         Err.Raise(-1, "", "No transaction to commit")
      Else
         m_oTransaction.Commit()
         m_oTransaction = Nothing
      End If

      ' Function executed successfully
      Return True

ErrorTrap:

      Return_ErrNum = Err.Number
      Return_ErrSrc = Err.Source
      If Return_ErrSrc = "" Then
         Return_ErrSrc = "TransactionCommit()"
      Else
         Return_ErrSrc = Return_ErrSrc & " [TransactionCommit()]"
      End If
      Return_ErrDesc = Err.Description
      Err.Clear()

      m_oTransaction = Nothing

   End Function

   ' Rolls back all SQL queries that have occured since calling the "TransactionBegin" method
   Public Function TransactionRollback(Optional ByRef Return_ErrNum As Integer = 0, _
                                       Optional ByRef Return_ErrSrc As String = "", _
                                       Optional ByRef Return_ErrDesc As String = "") As Boolean
      On Error GoTo ErrorTrap

      ' Set default values
      Return_ErrNum = 0
      Return_ErrSrc = ""
      Return_ErrDesc = ""

      ' Start the transaction
      If m_oTransaction Is Nothing Then
         Err.Raise(-1, "", "No transaction to rollback")
      Else
         m_oTransaction.Rollback()
         m_oTransaction = Nothing
      End If

      ' Function executed successfully
      Return True

ErrorTrap:

      Return_ErrNum = Err.Number
      Return_ErrSrc = Err.Source
      If Return_ErrSrc = "" Then
         Return_ErrSrc = "TransactionRollback()"
      Else
         Return_ErrSrc = Return_ErrSrc & " [TransactionRollback()]"
      End If
      Return_ErrDesc = Err.Description
      Err.Clear()

      m_oTransaction = Nothing

   End Function

   ' (see documentation for "ExecuteSQL_" method)
   Public Overloads Function ExecuteSQL(ByVal sSQL As String, _
                                        ByVal CommandType As CommandType, _
                                        ByRef Return_DataSet As DataSet, _
                                        Optional ByRef Return_RowsEffected As Integer = -1, _
                                        Optional ByRef Return_RowsReturned As Integer = -1, _
                                        Optional ByRef Return_ErrNum As Integer = 0, _
                                        Optional ByRef Return_ErrSrc As String = "", _
                                        Optional ByRef Return_ErrDesc As String = "") As Boolean

      Return ExecuteSQL_(sSQL, CommandType, ReturnType.rt_DataSet, Return_DataSet, Return_RowsEffected, Return_RowsReturned, Return_ErrNum, Return_ErrSrc, Return_ErrDesc)

   End Function

   ' (see documentation for "ExecuteSQL_" method)
   Public Overloads Function ExecuteSQL(ByVal sSQL As String, _
                                        ByVal CommandType As CommandType, _
                                        ByRef Return_DataTable As DataTable, _
                                        Optional ByRef Return_RowsEffected As Integer = -1, _
                                        Optional ByRef Return_RowsReturned As Integer = -1, _
                                        Optional ByRef Return_ErrNum As Integer = 0, _
                                        Optional ByRef Return_ErrSrc As String = "", _
                                        Optional ByRef Return_ErrDesc As String = "") As Boolean

      Return ExecuteSQL_(sSQL, CommandType, ReturnType.rt_DataTable, Return_DataTable, Return_RowsEffected, Return_RowsReturned, Return_ErrNum, Return_ErrSrc, Return_ErrDesc)

   End Function

   ' (see documentation for "ExecuteSQL_" method)
   Public Overloads Function ExecuteSQL(ByVal sSQL As String, _
                                        ByVal CommandType As CommandType, _
                                        ByRef Return_DataReader As OdbcDataReader, _
                                        Optional ByRef Return_RowsEffected As Integer = -1, _
                                        Optional ByRef Return_RowsReturned As Integer = -1, _
                                        Optional ByRef Return_ErrNum As Integer = 0, _
                                        Optional ByRef Return_ErrSrc As String = "", _
                                        Optional ByRef Return_ErrDesc As String = "") As Boolean

      Return ExecuteSQL_(sSQL, CommandType, ReturnType.rt_DataReader, Return_DataReader, Return_RowsEffected, Return_RowsReturned, Return_ErrNum, Return_ErrSrc, Return_ErrDesc)

   End Function

   ' Executes the specified SQL Query and returns back an "SqlDataReader" object along with how many records were effected
   Public Overloads Function GetSingleValue(ByVal sSQL As String, _
                                            ByVal CommandType As CommandType, _
                                            ByRef Return_Value As Object, _
                                            Optional ByRef Return_ErrNum As Integer = 0, _
                                            Optional ByRef Return_ErrSrc As String = "", _
                                            Optional ByRef Return_ErrDesc As String = "") As Boolean
      On Error GoTo ErrorTrap

      Dim oDbCommand As OdbcCommand

      ' Set default values
      Return_Value = Nothing
      Return_ErrNum = 0
      Return_ErrSrc = ""
      Return_ErrDesc = ""

      ' Validate parameters
      If sSQL.Trim = "" Then Err.Raise(-1, "", "No SQL statement specified to execute")

      ' Make sure there's a valid connection
      If CheckConnection(Return_ErrNum, Return_ErrSrc, Return_ErrDesc) = False Then Return False

      ' Create the command object to use
      oDbCommand = m_oDbConnection.CreateCommand
      With oDbCommand
         .CommandText = sSQL
         .CommandTimeout = m_iTimeOutCmd
         .CommandType = CommandType
         If Not m_oTransaction Is Nothing Then .Transaction = m_oTransaction
      End With

      ' Execute the SQL statement
      Return_Value = oDbCommand.ExecuteScalar

      ' Clean up
      If Not oDbCommand Is Nothing Then
         oDbCommand.Dispose()
         oDbCommand = Nothing
      End If

      ' Function executed successfully
      Return True

ErrorTrap:

      Return_ErrNum = Err.Number
      Return_ErrSrc = Err.Source
      If Return_ErrSrc = "" Then
         Return_ErrSrc = "GetSingleValue()"
      Else
         Return_ErrSrc = Return_ErrSrc & " [GetSingleValue()]"
      End If
      Return_ErrDesc = Err.Description
      Return_Value = Nothing
      Err.Clear()

      If Not oDbCommand Is Nothing Then
         oDbCommand.Dispose()
         oDbCommand = Nothing
      End If

   End Function

   ' Executes the specified SQL Query and returns how many rows were effected.  This function is best for INSERT, UPDATE, and DELETE statements (or calling Stored Procs that have no return).
   ' NOTE: If you are executing a SELECT and want to get the results of the query back, use "ExecuteSQL" instead.
   Public Overloads Function ExecuteSqlQuick(ByVal sSQL As String, _
                                             ByVal CommandType As CommandType, _
                                             Optional ByRef Return_RowsEffected As Integer = -1, _
                                             Optional ByRef Return_ErrNum As Integer = 0, _
                                             Optional ByRef Return_ErrSrc As String = "", _
                                             Optional ByRef Return_ErrDesc As String = "") As Boolean
      On Error GoTo ErrorTrap

      Dim oDbCommand As OdbcCommand

      ' Set default values
      Return_RowsEffected = -1
      Return_ErrNum = 0
      Return_ErrSrc = ""
      Return_ErrDesc = ""

      ' Validate parameters
      If sSQL.Trim = "" Then Err.Raise(-1, "", "No SQL statement specified to execute")

      ' Make sure there's a valid connection
      If CheckConnection(Return_ErrNum, Return_ErrSrc, Return_ErrDesc) = False Then Return False

      ' Create the command object to use
      oDbCommand = m_oDbConnection.CreateCommand
      With oDbCommand
         .CommandText = sSQL
         .CommandTimeout = m_iTimeOutCmd
         .CommandType = CommandType
         If Not m_oTransaction Is Nothing Then .Transaction = m_oTransaction
      End With

      ' Execute the SQL statement
      Return_RowsEffected = oDbCommand.ExecuteNonQuery()

      ' Clean up
      If Not oDbCommand Is Nothing Then
         oDbCommand.Dispose()
         oDbCommand = Nothing
      End If

      ' Function executed successfully
      Return True

ErrorTrap:

      Return_ErrNum = Err.Number
      Return_ErrSrc = Err.Source
      If Return_ErrSrc = "" Then
         Return_ErrSrc = "GetSingleValue()"
      Else
         Return_ErrSrc = Return_ErrSrc & " [GetSingleValue()]"
      End If
      Return_ErrDesc = Err.Description
      Return_RowsEffected = -1
      Err.Clear()

      If Not oDbCommand Is Nothing Then
         oDbCommand.Dispose()
         oDbCommand = Nothing
      End If

   End Function

   ' Makes sure that there's an active connection established.  The "ExecuteSQL" method automatically establishes 
   ' a connection to the database if one was not previously established.
   Public Function Connect(Optional ByRef Return_ErrNum As Integer = 0, _
                           Optional ByRef Return_ErrSrc As String = "", _
                           Optional ByRef Return_ErrDesc As String = "") As Boolean

      ' Make sure there's a valid connection
      Return CheckConnection(Return_ErrNum, Return_ErrSrc, Return_ErrDesc)

   End Function

   ' Makes sure that the SQL statement passed in is properly qualified (Single quotes are escaped)
   Public Function CleanSQL(ByVal sSQL As String) As String

      CleanSQL = Replace(sSQL, "'", "''")

   End Function

   ' Returns TRUE if the specified "DataSet" object is valid.  Optionallly cleans up the object if 
   ' it is invalid or contains no data.
   Public Overloads Function IsDataObjectValid(ByRef oDataset As DataSet, _
                                               Optional ByVal bDeleteIfInvalid As Boolean = False) As Boolean

      If Not oDataset Is Nothing Then
         If oDataset.Tables.Count > 0 Then
            If oDataset.Tables.Item(0).Rows.Count > 0 Then
               Return True
            End If
         End If
      End If

      If bDeleteIfInvalid = True Then
         oDataset = Nothing
      End If

   End Function

   ' Returns TRUE if the specified "DataSet" object is valid.  Optionallly cleans up the object if 
   ' it is invalid or contains no data.
   Public Overloads Function IsDataObjectValid(ByRef oDatatable As DataTable, _
                                               Optional ByVal bDeleteIfInvalid As Boolean = False) As Boolean

      If Not oDatatable Is Nothing Then
         If oDatatable.Rows.Count > 0 Then
            Return True
         End If
      End If

      If bDeleteIfInvalid = True Then
         oDatatable = Nothing
      End If

   End Function

   ' Returns TRUE if the specified "OdbcDataReader" object is valid and contains data.  Optionallly
   ' cleans up the object if it is invalid or contains no data.
   Public Overloads Function IsDataObjectValid(ByRef oDataReader As OdbcDataReader, _
                                               Optional ByVal bDeleteIfInvalid As Boolean = False) As Boolean

      If Not oDataReader Is Nothing Then
         If oDataReader.IsClosed = False Then
            If oDataReader.HasRows = True Then
               Return True
            End If
         End If
      End If

      If bDeleteIfInvalid = True Then
         Call CleanUpDataObject(oDataReader)
      End If

   End Function

   ' Cleans up the specified SqlConnection object
   Public Sub CleanUpConnection(ByRef oConnection As OdbcConnection)
      On Error Resume Next

      If Not oConnection Is Nothing Then
         oConnection.Close()
         oConnection.Dispose()
         oConnection = Nothing
      End If
      Err.Clear()

   End Sub

   ' Cleans up the specified OdbcDataReader object
   Public Overloads Sub CleanUpDataObject(ByRef oDatareader As OdbcDataReader)
      On Error Resume Next

      If Not oDatareader Is Nothing Then
         If oDatareader.IsClosed = False Then oDatareader.Close()
         oDatareader = Nothing
      End If
      Err.Clear()

   End Sub

   ' Cleans up the specified DataTable object
   Public Overloads Sub CleanUpDataObject(ByRef oDataTable As DataTable)
      On Error Resume Next

      If Not oDataTable Is Nothing Then
         oDataTable.Clear()
         oDataTable.Dispose()
         oDataTable = Nothing
      End If
      Err.Clear()

   End Sub

   ' Cleans up the specified DataTable object
   Public Overloads Sub CleanUpDataObject(ByRef oDataSet As DataSet)
      On Error Resume Next

      If Not oDataSet Is Nothing Then
         oDataSet.Dispose()
         oDataSet = Nothing
      End If
      Err.Clear()

   End Sub

#End Region

#Region "Private Helper Functions"

   '==========================================================================================================
   ' ExecuteSQL
   '
   ' Executes the specified SQL Query and returns back an "SqlDataReader" object along with how many records
   ' were effected
   '
   ' Parameter:                      Definition:
   ' --------------------------------------------------
   ' sSQL                            The SQL query string, or stored procedure name to execute
   ' CommandType                     Specifies what the "sSQL" query is
   ' DataReturnType                  Optional. Specifies what data object should be returned (DataReader, DataTable, or DataSet)
   ' Return_DataObject               Optional. If something is returned from the query, this returns is in 
   '                                 the form specified by the "DataReturnType" parameter
   ' Return_RowsEffected             Optional. If the "DataReturnType" parameter is set to "rt_DataReader",
   '                                 this parameter returns how many records where effected.  Otherwise, it
   '                                 returns -1.
   ' Return_RowsReturned             Optional. If the "DataReturnType" parameter is set to "rt_DataReader",
   '                                 this parameter returns -1.  Otherwise, it returns how many records where
   '                                 returned in the DataTable or DataSet object.
   ' Return_ErrNum                   Optional. If an error occurs, this returns the number of the error.
   ' Return_ErrSrc                   Optional. If an error occurs, this returns the source of the error.
   ' Return_ErrDesc                  Optional. If an error occurs, this returns the description of the error.
   '
   ' Return:
   ' -------
   ' TRUE = The function executed without errors, FALSE = an error occured
   '==========================================================================================================
   Private Function ExecuteSQL_(ByVal sSQL As String, _
                                ByVal CommandType As CommandType, _
                                Optional ByVal DataReturnType As ReturnType = ReturnType.rt_DataReader, _
                                Optional ByRef Return_DataObject As Object = Nothing, _
                                Optional ByRef Return_RowsEffected As Integer = -1, _
                                Optional ByRef Return_RowsReturned As Integer = -1, _
                                Optional ByRef Return_ErrNum As Integer = 0, _
                                Optional ByRef Return_ErrSrc As String = "", _
                                Optional ByRef Return_ErrDesc As String = "") As Boolean
      On Error GoTo ErrorTrap

      Dim oDataAdapter As OdbcDataAdapter
      Dim oDbCommand As OdbcCommand

      ' Set default values
      Return_DataObject = Nothing
      Return_RowsEffected = -1
      Return_RowsReturned = -1
      Return_ErrNum = 0
      Return_ErrSrc = ""
      Return_ErrDesc = ""

      ' Validate parameters
      If sSQL.Trim = "" Then Err.Raise(-1, "", "No SQL statement specified to execute")

      ' Make sure there's a valid connection
      If CheckConnection(Return_ErrNum, Return_ErrSrc, Return_ErrDesc) = False Then Return False

      ' Create the command object to use
      oDbCommand = m_oDbConnection.CreateCommand
      With oDbCommand
         .CommandText = sSQL
         .CommandTimeout = m_iTimeOutCmd
         .CommandType = CommandType
         If Not m_oTransaction Is Nothing Then .Transaction = m_oTransaction
      End With

      Select Case DataReturnType

         Case ReturnType.rt_DataTable
            Return_DataObject = New DataTable
            oDataAdapter = New OdbcDataAdapter
            oDataAdapter.SelectCommand = oDbCommand
            oDataAdapter.Fill(Return_DataObject)
            If Not Return_DataObject Is Nothing Then Return_RowsReturned = Return_DataObject.Rows.Count

         Case ReturnType.rt_DataSet
            Return_DataObject = New DataSet
            oDataAdapter = New OdbcDataAdapter
            oDataAdapter.SelectCommand = oDbCommand
            oDataAdapter.Fill(Return_DataObject)
            If Not Return_DataObject Is Nothing AndAlso Return_DataObject.Tables.Count > 0 Then Return_RowsReturned = Return_DataObject.Tables(0).Rows.Count

         Case Else ' ReturnType.rt_DataReader 
            Return_DataObject = oDbCommand.ExecuteReader
            Return_RowsEffected = Return_DataObject.RecordsAffected
      End Select

      ' Clean up
      If Not oDataAdapter Is Nothing Then
         oDataAdapter.SelectCommand = Nothing
         oDataAdapter.Dispose()
         oDataAdapter = Nothing
      End If
      If Not oDbCommand Is Nothing Then
         oDbCommand.Dispose()
         oDbCommand = Nothing
      End If

      ' Function executed successfully
      Return True

ErrorTrap:

      Return_ErrNum = Err.Number
      Return_ErrSrc = Err.Source
      If Return_ErrSrc = "" Then
         Return_ErrSrc = "ExecuteSQL()"
      Else
         Return_ErrSrc = Return_ErrSrc & " [ExecuteSQL()]"
      End If
      Return_ErrDesc = Err.Description
      Return_RowsEffected = -1
      Return_RowsReturned = -1
      Return_DataObject = Nothing
      Err.Clear()

      If Not oDataAdapter Is Nothing Then
         oDataAdapter.SelectCommand = Nothing
         oDataAdapter.Dispose()
         oDataAdapter = Nothing
      End If
      If Not oDbCommand Is Nothing Then
         oDbCommand.Dispose()
         oDbCommand = Nothing
      End If

   End Function

   ' Checks to make sure a connection was previously established.  If not, it creates a new one.
   Private Function CheckConnection(Optional ByRef Return_ErrNum As Integer = 0, _
                                    Optional ByRef Return_ErrSrc As String = "", _
                                    Optional ByRef Return_ErrDesc As String = "") As Boolean
      On Error GoTo ErrorTrap

      ' Set default values
      Return_ErrNum = 0
      Return_ErrSrc = ""
      Return_ErrDesc = ""

      ' Validate parameters
      If Not m_oDbConnection Is Nothing Then
         Return True
      ElseIf m_sConnString = "" Then
         Err.Raise(-1, "", "No connection string specified")
      End If

      ' Create the Connection objects to use
      m_oDbConnection = New OdbcConnection
      m_oDbConnection.ConnectionTimeout = m_iTimeOutConn
      m_oDbConnection.ConnectionString = m_sConnString
      m_oDbConnection.Open()

      ' Function executed successfully
      Return True

ErrorTrap:

      Return_ErrNum = Err.Number
      If Return_ErrSrc = "" Then
         Return_ErrSrc = "CheckConnection()"
      Else
         Return_ErrSrc = Return_ErrSrc & " [CheckConnection()]"
      End If
      Return_ErrDesc = Err.Description
      Err.Clear()

   End Function

#End Region

End Class