VB.NET function return error and exit sub

Question

I have this connection function in my VB.NET project

Public Function OpenMysqlCon() As MySqlConnection
    Dim mMysqlconnection = New MySqlConnection()
    Try
        Dim strconDB As String = "server='192.168.100.2'; database='mydb';Port=3306; UID='epb'; password='hahaha'; pooling=true"
        mMysqlconnection = New MySqlConnection
        mMysqlconnection.ConnectionString = strconDB
        mMysqlconnection.Open()
        OpenMysqlCon = mMysqlconnection
    Catch exceptionThatICaught As System.Exception
        OpenMysqlCon = Nothing
    End Try
End Function

And i will call the function in my VB project something like

Private Sub frmTest_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Using Con=OpenMysqlCon()
        'mycode here
    End Using
End Sub

However when the connection is not available, it will throw an exception.

How can i avoid the exception by giving a msgbox something like connection not available at the moment, please try again later and then exit the sub that was using the function?

End Sub

it's OpenMysqlCon, i have corrected typo, sorry pal. – Joseph Goh Apr 07 '17 at 09:44 — Joseph Goh, Apr 07 '17 at 09:44

dbasnett · Answer 1 · 2017-04-07T11:18:55.550

It will be something like this.

Public Function OpenMysqlCon() As MySqlConnection
    Dim mMysqlconnection As MySqlConnection()
    Try
        Dim strconDB As String = "server='192.168.100.2'; database='mydb';Port=3306; UID='epb'; password='hahaha'; pooling=true"
        mMysqlconnection = New MySqlConnection
        mMysqlconnection.ConnectionString = strconDB
        mMysqlconnection.Open()
    Catch exceptionThatICaught As System.Exception
        mMysqlconnection = Nothing
    End Try
    Return mMysqlconnection
End Function

Private Sub frmTest_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Dim con As MySqlConnection = OpenMysqlCon
    If con Is Nothing Then
        MessageBox.Show("connection not available at the moment, please try again later")
        'Me.Close 'uncomment if the app should end
    End If
End Sub

edit - this is not tested

    Using con As MySqlConnection = OpenMysqlCon
        If con Is Nothing Then
            MessageBox.Show("connection not available at the moment, please try again later")
            'Me.Close 'uncomment if the app should end
        Else
            'your code
        End If
    End Using

i am trying to use `using` statement, so i don't have to bother to close the connection at the end of the code. — Joseph Goh, Apr 07 '17 at 09:50

score 0 · Answer 2 · edited May 23 '17 at 12:02

The best approach is to not hold the connection in a field of the form but to create and initalize it wherever you need it. The connection-pooling will ensure that no physical connection needs to be created.

So don't use a OpenMysqlCon method at all but code like this(GetAllUsers is just an example):

Public Function GetAllUsers() As List(Of User)
    Dim userList = New List(Of User)

    Try
        Using mMysqlconnection = New MySqlConnection("server='192.168.100.2'; database='mydb';Port=3306; UID='epb'; password='hahaha'; pooling=true")
            mMysqlconnection.Open()
            Using command = New MySqlCommand("SELECT * FROM USER ORDER By UserName", mMysqlconnection)
                Using rdr = command.ExecuteReader()
                    While rdr.Read()
                        Dim user = New User()
                        user.UserName = rdr.GetString(0)
                        userList.Add(user)
                    End While
                End Using
            End Using
        End Using
    Catch exceptionThatICaught As System.Exception
        MessageBox.Show("meaningful message here, logging would be useful too")
        Return Nothing
    End Try

    Return userList
End Function

Maybe helpful because related(you're also reusing the connection): ExecuteReader requires an open and available Connection. The connection's current state is Connecting

when `Return Nothing` it will be still the same, because i will still have the exception. — Joseph Goh, Apr 07 '17 at 09:51
@JosephGoh: Then return whatever you want. That's not the point. The point is that you should not hold the connection outside of the method where you use it. Of course you have to handle the case that the method returns `Nothing` wherever you will call this method. — Tim Schmelter, Apr 07 '17 at 09:55

VB.NET function return error and exit sub

2 Answers2