Problem With Closing MySQL Connections

I am having a real problem with an ASP application I am building. The main page is forced to connect to 14 different tables in 2 different databases (it's checking multiple news sources for updates). Once the site moved to production, during peaks times, we are getting "Too Many Connections" errors. I believe I've properly closed all the connections.

I run "show processlist" from the MySQL command line prompt and I discover that all the connections are in sleep mode. So, they don't seem to be closed correctly, though closing a connection isn't rocket science and I can't for the life of me see what could possibly be wrong.

I cannot post my actual code here due to security concerns by my company, but here is the basic structure.

-default.asp consists of three includes. {header.asp / body.asp / footer.asp}

-I open my connections in the header.asp file like so:

Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open("Driver={MySQL ODBC 3.51 Driver};SERVER="xxx";DATABASE="xxx";uid="xxx";pwd="xxx";)
Set objConn = objConn.Execute(SELECT * FROM tablexxx)

Set objConn2 = Server.CreateObject("ADODB.Connection")
objConn2.Open("Driver={MySQL ODBC 3.51 Driver};SERVER="xxx";DATABASE="xxx";uid="xxx";pwd="xxx";)
Set objConn2 = objConn2.Execute(SELECT * FROM tablexxx)

Set objConn3 = Server.CreateObject("ADODB.Connection")
objConn3.Open("Driver={MySQL ODBC 3.51 Driver};SERVER="xxx";DATABASE="xxx";uid="xxx";pwd="xxx";)
Set objConn3 = objConn3.Execute(SELECT * FROM tablexxx)

etc....



I then output my body, and then the footer. It is within the footer that I close my connections, like so:



objConn.Close
Set objConn = Nothing

objConn2.Close
Set objConn2 = Nothing

objConn3.Close
Set objConn3 = Nothing



Any ideas what I could be doing wrong? Any help or advice would be greatly appreciated.

Are you making sure to trap errors and close connections when they occur?
Because if the code never hits the close statements, the connections might stay alive.
I had this problem with one of my java apps.

I'm sure someone will correct me if I'm wrong but I'm confused as to why you're doing this:
Set objConn = objConn.Execute(SELECT * FROM tablexxx)
Its quite possible (even proabably ) that you know more about this than me but is it not more usual to have a connection object and a recordset object and then set recordset = connection.execute(SQL)
'do stuff with recordset
recordset.close
connection.close


...also why do you need 3 connections to get data from 2 databases?

Are you making sure to trap errors and close connections when they occur?
Because if the code never hits the close statements, the connections might stay alive.
I had this problem with one of my java apps.

I'm not sure what you mean by this. Since the close statements are held in the footer, and the footer is included in every page, wouldn't it guarentee that the connections are closed with every page load? An honest question, because I am obviously missing something somewhere.

NancyJ, I have a typo there, I am connecting to 14 different databases to retrieve news from each independant source here on our local intranet.

Since the close statements are held in the footer, and the footer is included in every page, wouldn't it guarentee that the connections are closed with every page load?

Honestly, with ASP, I am not positive. I don't like it for anything important, since error trapping is such a pain. (who invented on error resume next? I'd like to smack him or her!)
I play with it and use it for small things.
And I am in the process of converting an existing ASP app to ASP.NET (which is MUCH better)

With JSP, nope. In my JSP app, if an error occurs that halts execution ("exceptions"), unless you use try/catch/finally and set connection close in the finally so it is always executed, the connection is not closed by the JSP app. The database was set to timeout connections after a certain amount of time had elapsed with no activity, but if a lot of users were on, that didn't always help.
We ended up using a combo of error trapping as well as using connection pooling and a custom class that kept track of open connections.

How high is your peak traffic? It takes 2-4 minutes for a connection port to become available again after being closed (when running on windows)
If you're exceeding the number of connections allowed (check what the limit on the dbs are) in a 5 minutes period then it may be that there is nothing wrong with your code... though I still dont understand why you're using your connection object as a recordset

@Spire2000,
The problem is that you have too many connections opened for too long of a time. You really only need one and should close it after you get the data from each database. This is the way that I would do it.
<%
Dim aryNews1, aryNews2, etc.
Dim objConn, objRS, iRow, iCol
Set objConn = Server.CreateObject("ADODB.Connection")
Set objRS = Server.CreateObject("ADODB.Recordset")
'First database
objConn.Open("Driver={MySQL ODBC 3.51 Driver};SERVER="xxx";DATABASE="xxx";uid="xxx";pwd="xxx";)
Set objRS = objConn.Execute("SELECT * FROM tablexxx")
If NOT objRS.EOF AND NOT objRS.BOF Then
aryNews1 = objRS.GetRows
objRS.Close
objConn.Close
End If
'Second database
objConn.Open("Driver={MySQL ODBC 3.51 Driver};SERVER="xxx";DATABASE="xxx";uid="xxx";pwd="xxx";)
Set objRS = objConn.Execute("SELECT * FROM tablexxx")
If NOT objRS.EOF AND NOT objRS.BOF Then
aryNews2 = objRS.GetRows
objRS.Close
objConn.Close
End If
...
Set objRS = Nothing
Set objConn = Nothing

'To print out
If IsArray(aryNews1) Then
For iRow = 0 To Ubound(aryNews1, 2)
For iCol = 0 To Ubound(aryNews1, 1)
Response.Write "..."
Next
Next
End If
...
%>