Error while closing SQL Connection

Question

I have a problem with closing the SQLconnection in my application. My application is in VB.net. I have a reference in my application to a class with code to open and close the database connection and to execute all sql scripts.

The error occurs when i close my application. In the formClosing event of my main form I call a function that closes all the connections. But just before I close the connections I perform an SQLquery to delete a row from a table with the function below.

Public Function DeleteFunction(ByVal mySQLQuery As String, ByVal cmd As SqlCommand) As Boolean
    Try
        cmd.Connection = myConnection
        cmd.CommandText = mySQLQuery
        cmd.ExecuteNonQuery()
        Return True

    Catch ex As Exception
        WriteErrorMessage("DeleteFunction", ex, Logpath, "SQL Error: " & mySQLQuery)
        Return False
    End Try
End Function

In my application I check the result of the boolean. If it returns True, then i call the function to close the database connection.

The returned boolean is True and the requested row is deleted in my database. This means i can close my connection which I do with the function below.

Public Sub DatabaseConnClose()
    myCommand.CommandText = ""
    myConnection.Close()

    myCommand = Nothing
    myConnection = Nothing
End Sub

After executing this code I receive an error in my logfile from the DeleteFunction. It says: "Connection property has not been initialized."

It seems very strange to receive an error from a function that was completely executed, or am i wrong to think that?

Can anyone tell me why I receive this error and how I can solve the problem?

the formClosing event is probably executing more than one time — SysDragon, Apr 30 '14 at 09:38
Why don't use [Using Statement](http://msdn.microsoft.com/en-us/library/htd05whh.aspx). have a look at [this question](http://stackoverflow.com/questions/4717789/in-a-using-block-is-a-sqlconnection-closed-on-return-or-exception) for details — huMpty duMpty, Apr 30 '14 at 09:40
@huMptyduMpty If the function is executing more than one time, it's something that has to be fixed. Solving the close problem will only imply deleting more times — SysDragon, Apr 30 '14 at 09:45
Everything but the `myConnection.Close()` statement is just confusing noice — adrianm, Apr 30 '14 at 09:55
Do you mean that somthing appears in your logfile without your code writing to it? myConnection is probably `Nothing` and your method does not return True. — adrianm, Apr 30 '14 at 09:59
@SysDragon: Its not an answer to the question. Just a suggestion to use it properly !! — huMpty duMpty, Apr 30 '14 at 10:02

score 1 · Answer 1 · answered Apr 30 '14 at 10:04

I believe that you have two problem which have both been answered. Handling the connection and the formClosing event firing.

I see that "myConnection" and "myCommand" as not locally scoped. This objects should be not be held at class level (your form). You should open your connection in a using block and let the connection be disposed as soon as possible. (as @PaulG as stated) If you are using connection pooling then using a using block will allow for the connection to be returned to pool. If you dont that you could be in for a variety of problems.

Once you are handling your connections correctly, you might still have a problem as the formClosing event could fire more than once (as @SysDragon has already said.) I simple boolean flag should help you out.

score 1 · Accepted Answer · answered May 28 '14 at 09:22

Thanks for your help. I finally solved the problem. All my code was executed in the FormClosing event of my form. But now i've separated the code in two pieces. The piece of code I left in the FormClosing event is the call of the delete function. I moved the code that called the function to close the database connection to the FormClosed event.

So there was nothing wrong with my code, only the place was wrong.

score 0 · Answer 3 · answered Apr 30 '14 at 09:46

0

I'm sure that you have a very good reason to close the connection the way you are but why not try this instead?

Public Function DeleteFunction(ByVal mySQLQuery As String) As Boolean
    Try
        using conn as new sqlconnection("connstring"), comm as new sqlcommand("",conn)

            conn.open()

            comm.commandtext = mySQLQuery 

            comm.ExecuteNonQuery() 

        end using

        Return True

    Catch ex As Exception
        WriteErrorMessage("DeleteFunction", ex, Logpath, "SQL Error: " & mySQLQuery)
        Return False
    End Try
End Function

answered Apr 30 '14 at 09:46

PaulG

592
2
8

-1 As I said in the comments on the question: If the function is executing more than one time, it's something that has to be fixed. Solving the close problem will only imply deleting more times. This don't solve the problem, only improves the code. – SysDragon Apr 30 '14 at 10:04
@SysDragon Apologies, I posted this before you commented, no need for the mark down tbh :) – PaulG Apr 30 '14 at 10:06

Error while closing SQL Connection

3 Answers3