[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

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

Posted on 2008-01-29
15
Medium Priority
?
945 Views
Last Modified: 2012-06-21
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)
0
Comment
Question by:AWSHelpdesk
  • 8
  • 7
15 Comments
 
LVL 16

Expert Comment

by:gnoon
ID: 20773520
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
 

Author Comment

by:AWSHelpdesk
ID: 20773762
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
 
LVL 16

Expert Comment

by:gnoon
ID: 20773945
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 16

Expert Comment

by:gnoon
ID: 20773961
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
 

Author Comment

by:AWSHelpdesk
ID: 20774396
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
 
LVL 16

Expert Comment

by:gnoon
ID: 20774734
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
 

Author Comment

by:AWSHelpdesk
ID: 20774773
I'm not really sure what you mean here??

My TypeName is = ListsDataSetTableAdapters.ListsListTableAdapter
0
 
LVL 16

Expert Comment

by:gnoon
ID: 20775095
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
 

Author Comment

by:AWSHelpdesk
ID: 20782073
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
 
LVL 16

Expert Comment

by:gnoon
ID: 20783201
Can you find the source of ListsDataSetTableAdapters.ListsListTableAdapter class?
0
 
LVL 16

Expert Comment

by:gnoon
ID: 20783223
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
 

Author Comment

by:AWSHelpdesk
ID: 20783495
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
 
LVL 16

Expert Comment

by:gnoon
ID: 20784644
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
 

Author Comment

by:AWSHelpdesk
ID: 20818947
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
 

Accepted Solution

by:
AWSHelpdesk earned 0 total points
ID: 20864096
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

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It was really hard time for me to get the understanding of Delegates in C#. I went through many websites and articles but I found them very clumsy. After going through those sites, I noted down the points in a easy way so here I am sharing that undeā€¦
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Stellar Phoenix SQL Database Repair software easily fixes the suspect mode issue of SQL Server database. It is a simple process to bring the database from suspect mode to normal mode. Check out the video and fix the SQL database suspect mode problem.
In this video I will demonstrate how to set up Nine, which I now consider the best alternative email app to Touchdown.
Suggested Courses

608 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question