locked
What is the best way of handling various exceptions? RRS feed

  • Question

  • I have a vb.net application that connects to an access database.

    There are various things that could cause an exception when opening a connection, such as the network being down, The oledb provider not being registered and the actual database file not being found.
    I'd like to take different actions depending on the error thrown.
    What is the best way of doing this?

    A couple of examples below. First one by uninstalling AccessDatabaseEngine, the other by disconnecting the network.

    System.InvalidOperationException: The 'Microsoft.ACE.OLEDB.12.0' provider is not registered on the local machine.

    System.Data.OleDb.OleDbException (0x80004005): Your network access was interrupted.

     Public Function fOpenCon(strCaller As String) As Boolean
            fOpenCon = False
            Dim dbLoadSource As String
            dbLoadSource = "|DataDirectory|\CheckCon.accdb"
            Dim pSwRD As String
            pSwRD = ""
            con.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & dbLoadSource & "';Jet OLEDB:Database Password='" & pSwRD & "';Mode=Share Deny None"
            Try
                con.Open()
                fOpenCon = True
            Catch ex As Exception
                MsgBox(strCaller & vbCrLf & ex.ToString)
                fOpenCon = False
            End Try
        End Function

    Cheers Andy


    Wednesday, January 16, 2019 12:02 PM

All replies

  • Hello,

    Check out the following I have on GitHub, if there are question let me know. It handles exceptions and encrypts and decrypts connections with passwords.

    https://github.com/karenpayneoregon/SecureConnectionStringsVisualBasic

    And 

    https://github.com/karenpayneoregon/WorkingWithAccessDatabases

    Here the basics

    A base exception class which your connection class inherits then the data class inherits this class. What this does is in the data class provides consistent logic to handle exceptions by setting if an exception occurs along with the last exception.

    Public Class BaseExceptionProperties
    
        Protected mHasException As Boolean
        ''' <summary>
        ''' Indicate the last operation thrown an exception or not
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property HasException() As Boolean
            Get
                Return mHasException
            End Get
        End Property
        Protected mLastException As Exception
        ''' <summary>
        ''' Provides access to the last exception thrown
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property LastException() As Exception
            Get
                Return mLastException
            End Get
        End Property
        ''' <summary>
        ''' If you don't need the entire exception as in LastException this 
        ''' provides just the text of the exception
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property LastExceptionMessage As String
            Get
                Return mLastException.Message
            End Get
        End Property
        ''' <summary>
        ''' Indicate for return of a function if there was an exception thrown or not.
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property IsSuccessFul As Boolean
            Get
                Return Not mHasException
            End Get
        End Property
    End Class

    Here is the connection class (note the Implements IConnection which allows a developer to jump to working with say SQL-Server and ensures that that class for connections has a ConnectionString property).

    Public Interface IConnection
        ReadOnly Property ConnectionString() As String
    End Interface

    Connection class

    Imports System.Data.OleDb
    
    Public MustInherit Class AccessConnection
        Inherits BaseExceptionProperties
        Implements IConnection
    
        Private ReadOnly _builder As New OleDbConnectionStringBuilder With
            {
                .Provider = "Microsoft.ACE.OLEDB.12.0"
            }
        ''' <summary>
        ''' Database name and path
        ''' </summary>
        Protected DefaultCatalog As String = ""
        Public ReadOnly Property ConnectionString As String Implements IConnection.ConnectionString
            Get
    
                If String.IsNullOrWhiteSpace(DefaultCatalog) Then
                    Throw New Exception("Database name and path not provided.")
                End If
    
                _builder.DataSource = DefaultCatalog
    
                Return _builder.ConnectionString
    
            End Get
        End Property
        Public Property ConnectionStringWithPassword() As String
    End Class

    A simple example of a data class implementing the above classes

    Imports System.Data.OleDb
    Imports System.IO
    Imports KarensBaseClasses
    
    Public Class DatabaseOperations
        Inherits AccessConnection
    
        ''' <summary>
        ''' Default our connection to a database in the executable folder when not using a password
        ''' </summary>
        ''' <remarks>
        ''' Not used in the code sample but this is how to do a connection not encrypted.
        ''' </remarks>
        Public Sub New()
            DefaultCatalog = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Database1.accdb")
        End Sub
        ''' <summary>
        ''' Test the connection before attempting to interact with the ms-access database.
        ''' </summary>
        ''' <returns></returns>
        Public Function TestConnection() As Boolean
            mHasException = False
    
            Using cn As New OleDbConnection(ConnectionStringWithPassword)
                Try
                    cn.Open()
                    Return True
                Catch ex As Exception
                    mHasException = True
                    mLastException = ex
                End Try
            End Using
    
            Return IsSuccessFul
    
        End Function
    
        ''' <summary>
        ''' Read customers from database into a DataTable
        ''' </summary>
        ''' <returns>Populated DataTable of Customers</returns>
        ''' <remarks>
        ''' XML Literals allow a developer to write clean SQL with no string concatenation.
        ''' </remarks>
        Public Function LoadCustomers() As DataTable
    
            Using cn As New OleDbConnection(ConnectionStringWithPassword)
                Using cmd As New OleDbCommand With {.Connection = cn}
                    cmd.CommandText = <SQL>
                            SELECT 
                                Process,
                                Identifier, 
                                CompanyName, 
                                ContactName, 
                                ContactTitle
                            FROM Customer 
                            ORDER BY CompanyName;
                        </SQL>.Value
    
                    Dim dt As New DataTable With {.TableName = "Customer"}
    
                    Try
                        cn.Open()
                        dt.Load(cmd.ExecuteReader)
                        dt.Columns("Identifier").ColumnMapping = MappingType.Hidden
                        dt.Columns("Process").ColumnMapping = MappingType.Hidden
    
                    Catch ex As Exception
                        mHasException = True
                        mLastException = ex
                    End Try
    
                    Return dt
    
                End Using
            End Using
        End Function
    End Class
    
    

    Here is app.config in the project

    <?xml version="1.0" encoding="utf-8"?>
    <configuration>
        <configSections>
        </configSections>
        <connectionStrings>
            <add name="SimpleLoad_OleDb_MS_Access2007_1.My.MySettings.CustomersConnection"
                connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=Database2.accdb;Jet OLEDB:Database Password=karen" />
        </connectionStrings>
        <startup>
            <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1"/>
        </startup>
    </configuration>

    After encrypted

    <?xml version="1.0" encoding="utf-8"?>
    <configuration>
        <configSections>
        </configSections>
        <connectionStrings configProtectionProvider="DataProtectionConfigurationProvider">
            <EncryptedData>
                <CipherData>
                    <CipherValue>AQAAANCMnd8BFdERjHoAwE/Cl+sBAAAAICYgSlAvJUGmh7ph3+NpwAQAAAACAAAAAAAQZgAAAAEAACAAAAApuH7FMX+oX/9H6xxvcc2pdeCUvMuSftA21qqdr7aVrwAAAAAOgAAAAAIAACAAAABVF2YOak/vwDSfNeD+vf9IaLoAEJg9siybAlCIP9KtfvABAABLNxqdXpiW5Q5jjNGnc3v1ryqC7lQQ+/1iw72PR2mZcjMjXs22k/k79iW3HK+owneJpDpksJdQvGxvcVJPElKhMAD0ntaR0OH8/Wstxqv/do6FjLmZ2BsWXNqAccmLcZKcwc2oOIuBV0wDznSrulX5eajXkX0uwxVm/QFIft5r6VjqA9PqC4l62gUT4XJRgwg9qDlNs3AnfdzCl8N9A4n+M8PISauWofgiqgTmmr6+wO03oNP6gxIqJvixCaAK4rOc70uZNfP/IEcyAY9CpbuOd64UYWWVP0mpS0x1W0U4+zmzy/haunKE9HWsnyiuJUrYPszxY0Sw2JCyZxDKnjm0Ld35IS7xLT252wjyXe+3b/SuE72bhRk21Eq5z5HVWQ4r8xmSQPksiVhaxdsFew+hvKAePKFvOOdLwHZlz2UlHiuere3GiuLCai8W4czDeMrCgzSpL9V6JyiGTeKNilHocRB5EmPL/75RArKmGlQ2cDpJx+qssMkYc1Ts3TK+RYg2v6n/CDzgA36Ka4svrF1NTgFqgHzTFpow7LyNLBMEEuUnED9N8Rk8Mc9gYJIIsjL6vXTaVjWvfkf674cwPwFsJUSoe+pX2+CFPMBtQTlkKr/rX+xS6mhRvO+uHa9yvkEuPh0fZH0EsuGp/0dwlp/YQAAAAIqJgGUaOyyGQ1ChwoZNpi5YgCyr4a2+YLdcP+UAZRabDHGRbLiXz7Ayu9HlJGJ6ozH9blJrmugkE8CMUCbpUgo=</CipherValue>
                </CipherData>
            </EncryptedData>
        </connectionStrings>
        <startup>
            <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.6.1"/>
        </startup>
    </configuration>

    In the form declare the data class.

    Private operations As New ConnectionProtection(Application.ExecutablePath)

    Some sample operations, test a connection, read data via a DataTable and also check to see if there were errors via ops.IsSuccessful which is code back in the base exception class.

    Private Sub MainForm_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        If Not operations.IsProtected() Then
            operations.EncryptFile()
        End If
    
        operations.DecryptFile()
        ops.ConnectionStringWithPassword = My.Settings.CustomersConnection
        operations.EncryptFile()
    
        If ops.TestConnection() Then
            bsCustomers.DataSource = ops.LoadCustomers()
    
            If ops.IsSuccessFul Then
                contactTitles = ops.LoadContactTitles()
                BindingNavigator1.BindingSource = bsCustomers
    
                DataGridView1.AllowUserToAddRows = False
                DataGridView1.DataSource = bsCustomers
    
    
                DataGridView1.Columns("CompanyName").HeaderText = "Company"
                DataGridView1.Columns("ContactName").HeaderText = "Contact"
    
                DataGridView1.ExpandColumns()
    
                bsCustomers.Sort = "CompanyName"
            End If
        End If
    End Sub

    Handling unhandled exceptions: These can be handled for the unexpected via MyApplication class where I show how to write to a log file and suggested other options.

    https://code.msdn.microsoft.com/Dealing-with-unhandled-9b933818?redir=0


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites




    Wednesday, January 16, 2019 12:31 PM
  • Wow, that's more than I expected!

    Thanks Karen.
    I'll have to take a bit of time studying that.

    Wednesday, January 16, 2019 4:27 PM
  • Wow, that's more than I expected!

    Thanks Karen.
    I'll have to take a bit of time studying that.

    I realize there is a lot to go through here, many developers don't want to take time to go this route but the true benefits pay off via

    • Repeatable pattern.
    • Base classes can reside in a class project and referenced in many projects.

    Have questions let me know. If at that time you are happy with these classes consider marking the reply that helped as answered.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, January 16, 2019 4:46 PM
  • Thought I would check in to see how this is coming along.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, January 17, 2019 12:07 AM
  • Hi Karen,

    Thanks for your time.
    To be honest I'm out of my depth with this. I just write apps for my own business and out of interest.
    Do you have a sample project on GitHub?
    I think the best way to understand this would be if I could download a working example and study it's operation.
    The other problem I have is time.. I just come to work an hour early to do a bit of programing before I have to start doing my real job. So I apologize for being slow to respond.
    Actually I'm not even sure how to use GitHub, but I'm sure I can figure that out.

    Thanks again

    Andy

    Thursday, January 17, 2019 8:12 AM
  • Hi Karen,

    Thanks for your time.
    To be honest I'm out of my depth with this. I just write apps for my own business and out of interest.
    Do you have a sample project on GitHub?
    I think the best way to understand this would be if I could download a working example and study it's operation.
    The other problem I have is time.. I just come to work an hour early to do a bit of programing before I have to start doing my real job. So I apologize for being slow to respond.
    Actually I'm not even sure how to use GitHub, but I'm sure I can figure that out.

    Thanks again

    Andy

    I don't have any simple code samples and the majority use SQL-Server (like 99.99 percent) and are pretty much all working with a database.

    Here is a super simple example.

    Public Class BaseExceptionProperties
    
        Protected mHasException As Boolean
        ''' <summary>
        ''' Indicate the last operation thrown an exception or not
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property HasException() As Boolean
            Get
                Return mHasException
            End Get
        End Property
        Protected mLastException As Exception
        ''' <summary>
        ''' Provides access to the last exception thrown
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property LastException() As Exception
            Get
                Return mLastException
            End Get
        End Property
        ''' <summary>
        ''' If you don't need the entire exception as in LastException this 
        ''' provides just the text of the exception
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property LastExceptionMessage As String
            Get
                Return mLastException.Message
            End Get
        End Property
        ''' <summary>
        ''' Indicate for return of a function if there was an exception thrown or not.
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property IsSuccessFul As Boolean
            Get
                Return Not mHasException
            End Get
        End Property
    End Class

    Class to work with

    Public Class BadCode
        Inherits BaseExceptionProperties
        ''' <summary>
        ''' Always throws an exception
        ''' </summary>
        ''' <param name="firstName"></param>
        ''' <returns></returns>
        Public Function Example1(firstName As String) As String
            Dim result As String = ""
            Try
                result = firstName.ToLower()
                Throw New Exception("Me writing bad code")
            Catch ex As Exception
                mHasException = True
                mLastException = ex
            End Try
            Return result
        End Function
        ''' <summary>
        ''' Good code, works fine
        ''' </summary>
        ''' <param name="firstName"></param>
        ''' <returns></returns>
        Public Function Example2(firstName As String) As String
            Dim result As String = ""
            Try
                result = firstName.ToLower()
            Catch ex As Exception
                mHasException = True
                mLastException = ex
            End Try
            Return result
        End Function
    
    
    End Class
    

    Form code, one catches an exception while the other works as expected.

    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim test As New BadCode
            Dim result = test.Example1("Karen")
            If test.IsSuccessFul Then
                MessageBox.Show(result)
            Else
                MessageBox.Show(test.LastExceptionMessage)
            End If
        End Sub
    
        Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
            Dim test As New BadCode
            Dim result = test.Example2("Karen")
            If test.IsSuccessFul Then
                MessageBox.Show(result)
            Else
                MessageBox.Show(test.LastExceptionMessage)
            End If
    
        End Sub
    End Class
    


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Thursday, January 17, 2019 11:23 AM