[2005] Connection pool Maximum reached?

All,

I created a VB.NET web application project. Recently I got the dreaded above error message when a certain amount of web pages have loaded. On a form1_Load event, I do this:

vb Code:

Protected Sub form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles form1.Load
        '
        MyConnection = New SqlConnection
        MyConnection.ConnectionString = CONN_STRING
        MyConnection.Open()
        '
        Me.GridView1.Sort("Priority", SortDirection.Ascending)
        '
    End Sub

Question: Am I required to explicitly call the .Close? Inside which event would be the appropriate place?

Will this do the trick - or break anything?

vb Code:

Protected Sub form1_Unload(ByVal sender As Object, ByVal e As System.EventArgs) Handles form1.Unload
        MyConnection.Close()
        MyConnection = Nothing
    End Sub

Thanks!

Dave

Don't leave the connection open in the Load event handler. Open it, use it and close it. That's the way ADO.NET is designed to be used. Create a new connection each time you need one. Connection pooling keeps things efficient.

Well see now there's the rub. After the page is loaded, I have buttons that do things that require an open connection.

vb Code:

Protected Sub btnIncrease_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnIncrease.Click
        '
        Try
            '
            MyCommand = New SqlCommand("increasePriority", MyConnection)
            MyCommand.CommandType = CommandType.StoredProcedure
            MyCommand.Parameters.Add("@workOrderNumber", SqlDbType.VarChar).Value = DropDownList_WorkOrders.SelectedValue
            MyDataReader = MyCommand.ExecuteReader()
            '
        Catch ex As Exception
            MsgBox(ex.ToString)
        Finally
            MyDataReader.Close()
        End Try
        '
    End Sub

Do you suggest I open/close in the beg/end of each of these button's codeblock?

Thanks!

Quote:

---

Originally Posted by Dave Sell

Do you suggest I open/close in the beg/end of each of these button's codeblock?

---

It would appear so.

Quote:

---

Originally Posted by jmcilhinney

Open it, use it and close it. That's the way ADO.NET is designed to be used. Create a new connection each time you need one.

---

OK then thanks, I will do that if that is the standard .NET "good programming practice" in situations like this. I will keep this thread open a tad longer to ensure no new problems arise from it - or if it solves the prob.

Dave

You might want to look into what JM was saying about the efficiencies of connection pooling keeping things efficient. I may be reading too much into it, but you seem a bit reluctant to open and close connections. I think that was a valid position back in VB6, because there was a pretty good cost to some of that stuff, so leaving one connection open was often a good idea.....as long as your could afford to do so. Times have changed, though, and if you have concerns about this, checking into it a bit might ease your mind on it some.

As Shaggy says. With ADO.NET the connections you use are at a high level. There are also connection objects at a much lower level. When you create a connection, open it, use it and discard it, then open a second connection, you will likely be using the same underlying low-level connection. Creating the high-level object is very cheap, so doing so repeatedly is no problem. It's the low-level objects, which you never actually see, that are expensive to create, so they are created and discarded far less often.

Again, VBF failed to notify me that you guys responded to my posts (this is a long-time issue for me), sorry I just saw your replies.

Anyway, what I'm hearing is ADO.NET has under-the-hood connection pooling? That would be sweet. I have followed JM's advice from above. I still do not know if there is a problem on the server since I implemented this few weeks back. I will ask my customer how things are going this week.

Quote:

---

Originally Posted by jmcilhinney

Don't leave the connection open in the Load event handler. Open it, use it and close it. That's the way ADO.NET is designed to be used. Create a new connection each time you need one. Connection pooling keeps things efficient.

---

To sum it up: Open late, close early.