Timeout expired. the timeout period elapsed prior to obtaining a connection from the pool.

I have previously migrated 3 small websites that I use through our intranet into one directory with a shared web.config file because they all connect to the one database. So there are 3 folders under the website with the ASP.net files for each "mini-website" and 1 web.config file shared between them. They also each use a different dataset to connect to and update the database.
The site also has it's own app pool and doesn't use the default app pool on IIS.

Since moving them all to a shared website with 1 web.config file I have have been getting the following error after users complete around 10 inserts into the database (most of the screens are data entry screens);

"Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached."

This error is occuring at the line - Me.adapter.Fill(dataTable)
AWSHelpdeskAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

gnoonCommented:
The error describes itself enough. All connections in the pool are used concurrently and a new connection request is waiting but no one's available/released within time.

- make sure you release a connection after use, so it's returned to the pool
- try to increase maximum numbers of connections, but not much help since a connection is not returned to the pool
0
AWSHelpdeskAuthor Commented:
Ok, so i know how to do this when I am calling a qry and connection in code, but I am using a dataset and form view and not connecting programatically so how do i ensure that each connection is closed after an insert into the database?
0
gnoonCommented:
Using try/catch and close the connection in the final block.
    SqlConnection conn = null;
    try
    {
        conn = new SqlConnection(ConnectionString);
        conn.Open();
 
        string sql = SqlStatement;
 
        // prepare dataset
        SqlDataAdapter fetcher = new SqlDataAdapter(sql, conn);
        DataSet ds = new DataSet();
        fetcher.Fill(ds, "tblResult");
 
        // set the dataset as datasource of the grid
        DataGrid1.DataSource = ds;
        DataGrid1.DataBind();
    }
    catch(Exception exception)
    {
        // display error message here
    }
    finally
    {
        try{conn.Close();} catch {}
    }

Open in new window

0
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

gnoonCommented:
I'm just not sure how your custom connection pool work but if you are using the default connection pool of IIS, conn.Close() will release the connection back to the pool with connection overhead remained. So, open a new connection on the existing connection object will take less time.
0
AWSHelpdeskAuthor Commented:
But as I said I am not setting this up in the code. The frmView and gridViews are filled via an ObjectDataSource which it connects to via the formViews/gridviews DataSourceID.

The ObjectDataSource is connecting to a table adapter in the Dataset.

So how do I ensure that the connection is closed on insert?
0
gnoonCommented:
Close it somewhere you able to access the connection instance.

For example

<asp:ObjectDataSource ID="ObjectDataSource1" runat="server" TypeName="YourClass" ....></asp:ObjectDataSource>

You should manage to close connection within YourClass where the ObjectDataSource refer to.
0
AWSHelpdeskAuthor Commented:
I'm not really sure what you mean here??

My TypeName is = ListsDataSetTableAdapters.ListsListTableAdapter
0
gnoonCommented:
Can you show the <asp:ObjectDataSource> tag you defined?

I don't think the class ListsDataSetTableAdapters.ListsListTableAdapter is in .NET framework but I'm quite sure you can find database connection instance within it, or if it's a 3rd party library, it should provide you a mechanism to close/dispose database connection.
0
AWSHelpdeskAuthor Commented:
This is my ObjectDataSource code, it is just out of the box .Net stuff, i'm not using anything that didn't come with visual studio and .net 2.


<asp:ObjectDataSource ID="ListsDataSource" runat="server" DeleteMethod="Delete" InsertMethod="Insert" SelectMethod="GetDataByCampaignCode"
            TypeName="ListsDataSetTableAdapters.ListsListTableAdapter" UpdateMethod="Update" OldValuesParameterFormatString="original_{0}">
            <DeleteParameters>
                <asp:Parameter Name="listid" Type="Int32" />
            </DeleteParameters>
            <UpdateParameters>
                <asp:Parameter Name="listid" Type="Int32" />
                <asp:Parameter Name="campaigncode" Type="String" />
                <asp:Parameter Name="title" Type="String" />
                <asp:Parameter Name="firstname" Type="String" />
                <asp:Parameter Name="lastname" Type="String" />
                <asp:Parameter Name="emailaddress" Type="String" />
                <asp:Parameter Name="homephone" Type="String" />
                <asp:Parameter Name="businessphone" Type="String" />
                <asp:Parameter Name="mobile" Type="String" />
                <asp:Parameter Name="mailingaddress1" Type="String" />
                <asp:Parameter Name="suburb" Type="String" />
                <asp:Parameter Name="state" Type="String" />
                <asp:Parameter Name="postcode" Type="String" />
                <asp:Parameter Name="winetype" Type="String" />
                <asp:Parameter Name="red" Type="String" />
                <asp:Parameter Name="white" Type="String" />
                <asp:Parameter Name="price" Type="String" />
                <asp:Parameter Name="dob" Type="DateTime" />
                <asp:Parameter Name="contact" Type="Boolean" />
                <asp:Parameter Name="Winner" Type="Boolean" />
                <asp:Parameter Name="Entry" Type="String" />
                <asp:Parameter Name="entrycompleted" Type="Byte" />
                <asp:Parameter Name="processed" Type="Byte" />
            </UpdateParameters>
            <SelectParameters>
                <asp:ControlParameter ControlID="txtcampaigncode" Name="campaigncode" PropertyName="Text"
                    Type="String" />
            </SelectParameters>
            <InsertParameters>
                <asp:Parameter Name="campaigncode" Type="String" />
                <asp:Parameter Name="title" Type="String" />
                <asp:Parameter Name="firstname" Type="String" />
                <asp:Parameter Name="lastname" Type="String" />
                <asp:Parameter Name="emailaddress" Type="String" />
                <asp:Parameter Name="homephone" Type="String" />
                <asp:Parameter Name="businessphone" Type="String" />
                <asp:Parameter Name="mobile" Type="String" />
                <asp:Parameter Name="mailingaddress1" Type="String" />
                <asp:Parameter Name="suburb" Type="String" />
                <asp:Parameter Name="state" Type="String" />
                <asp:Parameter Name="postcode" Type="String" />
                <asp:Parameter Name="winetype" Type="String" />
                <asp:Parameter Name="red" Type="String" />
                <asp:Parameter Name="white" Type="String" />
                <asp:Parameter Name="price" Type="String" />
                <asp:Parameter Name="dob" Type="DateTime" />
                <asp:Parameter Name="contact" Type="Boolean" />
                <asp:Parameter Name="Entry" Type="String" />
                <asp:Parameter Name="entrycompleted" Type="Byte" />
            </InsertParameters>
        </asp:ObjectDataSource>

Open in new window

0
gnoonCommented:
Can you find the source of ListsDataSetTableAdapters.ListsListTableAdapter class?
0
gnoonCommented:
I'm sorry to ask you many questions. It's because I don't use .NET 2.0 currently but just trying to help.
0
AWSHelpdeskAuthor Commented:
I have no idea where to find this because I didn't write the classes myself, just added the FormView to the page and created an ObjectDataSource connecting it to my Dataset TableAdapter which is using stored procedures for Select/Insert/Update/Delete.

So I have no idea where I would find the classes created for this?
0
gnoonCommented:
http://microsoft.apress.com/index.php?id=50

At the end of link above is what I mean, 3-tier architect. You will see there that

FormView is using a instance of ObjectDataSource named ObjectDataSource1.
ObjectDataSource1 refers to EmployeeTbl class (TypeName="EmployeeTbl").
Within EmployeeTbl, there are methods used by ObjectDataSource1 (GetEmployees, InsertEmployee, UpdateEmployee, DeleteEmployee).
Each method has to close database connection at the final (although it's not good coding).

My point is the ListsDataSetTableAdapters.ListsListTableAdapter class should does the same thing as EmployeeTbl class.
0
AWSHelpdeskAuthor Commented:
Ok so i searched the code and found this. Obviously this was created automatically in Visual Studio when I set up the table adapters, so I would have thought that it would be closing the connection itself, and it looks to be down the bottom??


<Global.System.Diagnostics.DebuggerNonUserCodeAttribute(),  _
         Global.System.ComponentModel.Design.HelpKeywordAttribute("vs.data.TableAdapter"),  _
         Global.System.ComponentModel.DataObjectMethodAttribute(Global.System.ComponentModel.DataObjectMethodType.Insert, true)>  _
        Public Overloads Overridable Function Insert( _
                    ByVal campaigncode As String,  _
                    ByVal title As String,  _
                    ByVal firstname As String,  _
                    ByVal lastname As String,  _
                    ByVal emailaddress As String,  _
                    ByVal homephone As String,  _
                    ByVal businessphone As String,  _
                    ByVal mobile As String,  _
                    ByVal mailingaddress1 As String,  _
                    ByVal suburb As String,  _
                    ByVal state As String,  _
                    ByVal postcode As String,  _
                    ByVal winetype As String,  _
                    ByVal red As String,  _
                    ByVal white As String,  _
                    ByVal price As String,  _
                    ByVal dob As Global.System.Nullable(Of Date),  _
                    ByVal contact As Global.System.Nullable(Of Boolean),  _
                    ByVal Entry As String,  _
                    ByVal entrycompleted As Global.System.Nullable(Of Boolean)) As Integer
            If (campaigncode Is Nothing) Then
                Me.Adapter.InsertCommand.Parameters(1).Value = Global.System.DBNull.Value
            Else
                Me.Adapter.InsertCommand.Parameters(1).Value = CType(campaigncode,String)
            End If
            If (title Is Nothing) Then
                Me.Adapter.InsertCommand.Parameters(2).Value = Global.System.DBNull.Value
            Else
                Me.Adapter.InsertCommand.Parameters(2).Value = CType(title,String)
            End If
            If (firstname Is Nothing) Then
                Me.Adapter.InsertCommand.Parameters(3).Value = Global.System.DBNull.Value
            Else
                Me.Adapter.InsertCommand.Parameters(3).Value = CType(firstname,String)
            End If
            If (lastname Is Nothing) Then
                Me.Adapter.InsertCommand.Parameters(4).Value = Global.System.DBNull.Value
            Else
                Me.Adapter.InsertCommand.Parameters(4).Value = CType(lastname,String)
            End If
            If (emailaddress Is Nothing) Then
                Me.Adapter.InsertCommand.Parameters(5).Value = Global.System.DBNull.Value
            Else
                Me.Adapter.InsertCommand.Parameters(5).Value = CType(emailaddress,String)
            End If
            If (homephone Is Nothing) Then
                Me.Adapter.InsertCommand.Parameters(6).Value = Global.System.DBNull.Value
            Else
                Me.Adapter.InsertCommand.Parameters(6).Value = CType(homephone,String)
            End If
            If (businessphone Is Nothing) Then
                Me.Adapter.InsertCommand.Parameters(7).Value = Global.System.DBNull.Value
            Else
                Me.Adapter.InsertCommand.Parameters(7).Value = CType(businessphone,String)
            End If
            If (mobile Is Nothing) Then
                Me.Adapter.InsertCommand.Parameters(8).Value = Global.System.DBNull.Value
            Else
                Me.Adapter.InsertCommand.Parameters(8).Value = CType(mobile,String)
            End If
            If (mailingaddress1 Is Nothing) Then
                Me.Adapter.InsertCommand.Parameters(9).Value = Global.System.DBNull.Value
            Else
                Me.Adapter.InsertCommand.Parameters(9).Value = CType(mailingaddress1,String)
            End If
            If (suburb Is Nothing) Then
                Me.Adapter.InsertCommand.Parameters(10).Value = Global.System.DBNull.Value
            Else
                Me.Adapter.InsertCommand.Parameters(10).Value = CType(suburb,String)
            End If
            If (state Is Nothing) Then
                Me.Adapter.InsertCommand.Parameters(11).Value = Global.System.DBNull.Value
            Else
                Me.Adapter.InsertCommand.Parameters(11).Value = CType(state,String)
            End If
            If (postcode Is Nothing) Then
                Me.Adapter.InsertCommand.Parameters(12).Value = Global.System.DBNull.Value
            Else
                Me.Adapter.InsertCommand.Parameters(12).Value = CType(postcode,String)
            End If
            If (winetype Is Nothing) Then
                Me.Adapter.InsertCommand.Parameters(13).Value = Global.System.DBNull.Value
            Else
                Me.Adapter.InsertCommand.Parameters(13).Value = CType(winetype,String)
            End If
            If (red Is Nothing) Then
                Me.Adapter.InsertCommand.Parameters(14).Value = Global.System.DBNull.Value
            Else
                Me.Adapter.InsertCommand.Parameters(14).Value = CType(red,String)
            End If
            If (white Is Nothing) Then
                Me.Adapter.InsertCommand.Parameters(15).Value = Global.System.DBNull.Value
            Else
                Me.Adapter.InsertCommand.Parameters(15).Value = CType(white,String)
            End If
            If (price Is Nothing) Then
                Me.Adapter.InsertCommand.Parameters(16).Value = Global.System.DBNull.Value
            Else
                Me.Adapter.InsertCommand.Parameters(16).Value = CType(price,String)
            End If
            If (dob.HasValue = true) Then
                Me.Adapter.InsertCommand.Parameters(17).Value = CType(dob.Value,Date)
            Else
                Me.Adapter.InsertCommand.Parameters(17).Value = Global.System.DBNull.Value
            End If
            If (contact.HasValue = true) Then
                Me.Adapter.InsertCommand.Parameters(18).Value = CType(contact.Value,Boolean)
            Else
                Me.Adapter.InsertCommand.Parameters(18).Value = Global.System.DBNull.Value
            End If
            If (Entry Is Nothing) Then
                Me.Adapter.InsertCommand.Parameters(19).Value = Global.System.DBNull.Value
            Else
                Me.Adapter.InsertCommand.Parameters(19).Value = CType(Entry,String)
            End If
            If (entrycompleted.HasValue = true) Then
                Me.Adapter.InsertCommand.Parameters(20).Value = CType(entrycompleted.Value,Boolean)
            Else
                Me.Adapter.InsertCommand.Parameters(20).Value = Global.System.DBNull.Value
            End If
            Dim previousConnectionState As Global.System.Data.ConnectionState = Me.Adapter.InsertCommand.Connection.State
            If ((Me.Adapter.InsertCommand.Connection.State And Global.System.Data.ConnectionState.Open)  _
                        <> Global.System.Data.ConnectionState.Open) Then
                Me.Adapter.InsertCommand.Connection.Open
            End If
            Try 
                Dim returnValue As Integer = Me.Adapter.InsertCommand.ExecuteNonQuery
                Return returnValue
            Finally
                If (previousConnectionState = Global.System.Data.ConnectionState.Closed) Then
                    Me.Adapter.InsertCommand.Connection.Close
                End If
            End Try
        End Function

Open in new window

0
AWSHelpdeskAuthor Commented:
I found my solution, the textboxes where using a webservice to populate an autocompleteextender which was not closing the connection.
Thanks to everyone who tried to help :)
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.