Take the 2-minute tour ×

I am working on completely redeveloped website and sales system and have come up against this Max_connections issue surprisingly quickly.

I posted this question: Closing/Pooling MySQL ODBC connections Recently, but have since tried a few other things, still drawing a blank, but have more detail to offer...

I have a built a pretty complex sales process, and in creating an invoice I seem to be leaving 7 "processes" running each time. I have counted the number of times the data connection is used during the process of creating an invoice, and it is 7-9 depending on a few conditional values, so effectively the data connections are not closing at all.

To try to speed up coding, I have made a couple of functions which handle my database connectivity, so I will post these below.

Firstly, my connection string is:

"DRIVER={MySQL ODBC 3.51 Driver}; SERVER=mysql.dc-servers.com; DATABASE=jamieha_admin; UID=USERID; PASSWORD=pWD; OPTION=3;pooled=true;Max Pool Size=100" 

The functions which I am using to open and close and do stuff with the database are as follows:

Function connectionString(sql As String, closeConnection As String) As OdbcConnection
    Dim DBConnection As String = ConfigurationManager.ConnectionStrings("dbConnNew").ConnectionString
     'this is getting the connection string from web.config file.
    Dim oConnection As OdbcConnection = New OdbcConnection(DBConnection) 'call data connection
    connectionString = New OdbcConnection(DBConnection)
   If closeConnection <> "close" Then _
       connectionString.Open() ' open data connection
End Function

This function gives me a OdbcConnection Connection String Object, which I can then use with:

Function openDatabase(sql As String) As OdbcCommand
    openDatabase = New OdbcCommand(sql, connectionString(sql, ""))
End Function

This function creates a useable data object when called doing something like:

Dim stockLevel As OdbcCommand = openDatabase("SQL STATEMENT HERE")
Dim objDataReader As OdbcDataReader =      stockLevel.ExecuteReader(CommandBehavior.CloseConnection)
    '=== DO STUFF WITH objDataReader ==='

Having read up trying to ensure data connections were closing properly and so on I read that adding (CommandBehavior.CloseConnection) should ensure that the connection is closed when no longer used, but this doesn't seem to be happening, so I have created a separate "closeCOnnection" function, which looks like:

Function closeConn()
    If connectionString("", "", "close") IsNot Nothing AndAlso connectionString("", "close").State = ConnectionState.Open Then
        connectionString("", "close").Close()
        connectionString("", "close").Dispose()
    End If
End Function

This is called after every use of the openDatabase function and also within the functions I have created for insert/update and delete, which look like this:

Function insertData(InsertSql As String)
    Dim dataInsert = openDatabase(InsertSql, "new")
End Function

I am not sure whether making all these functions is making my life easier or harder, but I was trying to reduce the code in each file where data acceess is required, but I'm not convinced it has.

However, it has made it clear where and when I am opening and closing the database (or at least trying to)

The processes are not being closed though. If I run my sale process through 3 or 4 times in quick succession, with these 7 processes still being live and added to, I get the max_connections issue.

Not completely understanding how database connections work, I am afraid I am at a loss with this and hence having to ask you... again!!

Can anyone tell me:

a) Is my connection string correct, is there a better connection available for MySQL?

b) Using this method, creating a ODBCConnection Object, is it possible to close it within a function like this?

c) Why is (CommandBehavior.CloseConnection) not closing the connection (this problem arose before I tried closing the connection manually)

share|improve this question
Added the VB.NET tag –  Bueller Jan 9 '12 at 17:31
add comment

1 Answer

up vote 3 down vote accepted

Unfortunately the issues you are having come from your design and a mishandling of references to connections.

But don't worry. It's not difficult to fix. :-)

In VB.Net you always need to access data in the following pattern:

  1. Create a connection.
  2. Create a command which uses the connection (including adding any parameter values).
  3. Open the connection.
  4. Execute the command.
  5. Close the connection.

There are variations of this, such as looping over rows before closing the connection, but generally this is how it works. In order to ensure that the connection is closed, VB.Net provides Try/Finally blocks and Using statements. You need to use one of these to make sure the connections are closed.

I'll show you what I mean by rewriting your methods in the proper manner.

Firstly, wrap your connection-creation code into a function.

Function GetConnection() As OdbcConnection


    GetConnection = New OdbcConnection(DBConnection)

End Function

Secondly, write a function to create your command. (openDatabase is the wrong name, so I have changed it to CreateCommand).

Function CreateCommand(sql As String, connection As OdbcConnection) As OdbcCommand

    CreateCommand = New OdbcCommand(sql, connection)

End Function

Now when you wish to execute a query or a statement in the database, you can follow this pattern:

Dim connection As OdbcConnection = GetConnection()
Dim stockLevel As OdbcCommand = CreateCommand("SQL STATEMENT HERE", connection)
    Dim objDataReader As OdbcDataReader = stockLevel.ExecuteReader(CommandBehavior.CloseConnection)
End Try

Using the Try/Finally block means that the connection will always be closed correctly, even when an Exception causes the code to return before you expect it to.

An alternative shorthand is the Using statement (which effectively does exactly the same thing as the Dispose in a Finally block):

Dim connection As OdbcConnection = GetConnection()

Using connection
    Dim objDataReader As OdbcDataReader = stockLevel.ExecuteReader(CommandBehavior.CloseConnection)
End Using

And if you want to wrap your InsertData function in a command, you can do it like this:

Dim connection As OdbcConnection = GetConnection()
Dim stockLevel As OdbcCommand = CreateCommand(InsertSql, connection)

Using connection
    Dim result As Integer = stockLevel.ExecuteNonQuery()
End Using

I suspect that the first time you tried this, you were leaving your connections open without ever closing them. I also assume (from what you wrote) that you added the closeConn method to sort that out. Unfortunately, every time you call connectionString you are actually creating and opening a new connection, which you then call Close or Dispose on. The initial connection is never closed.

Hope that helps.

share|improve this answer
Whoops! Forgot to add the opening of the connection in my first edit. Updated now. –  Richard Jan 9 '12 at 21:38
Not by my computer now, on my phone, but that looks exactly what I was trying to achieve!! Thanks, I'll try it out later and report back! :) –  Jamie Hartnoll Jan 9 '12 at 22:27
OK, so far so good. Couple of questions: Firstly; When you need to connect to the database several times during the course of a script, would you open the connection at the top using connection and then close right at the end end using or would you open and close specifically each time it's required? Second, does end using ALWAYS close the connection? –  Jamie Hartnoll Jan 10 '12 at 10:19
I only usually answer for votes (hint ;-)), but as you asked so nicely... When you use a connection pool you are actually doing the work of creating connections up-front, and grabbing a connection from the pool is very lightweight. So the best thing to do is to open and close again as soon as you can. The Using statement always calls the Dispose method on the resources passed to it, even when an exception occurs. Calling Dispose on a connection automatically closes it. –  Richard Jan 10 '12 at 11:29
Brilliant. Voted up and accepted! Thanks so much, that was exactly the answer I was looking for! It's all working now and only one process can be seen at any time! :D –  Jamie Hartnoll Jan 10 '12 at 12:00
show 1 more comment

Your Answer


