asked on

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)

gnoon

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

AWSHelpdesk

ASKER

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?

gnoon

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

gnoon

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.

AWSHelpdesk

ASKER

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?

gnoon

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.

AWSHelpdesk

ASKER

I'm not really sure what you mean here??

My TypeName is = ListsDataSetTableAdapters.ListsListTableAdapter

gnoon

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.

AWSHelpdesk

ASKER

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

gnoon

Can you find the source of ListsDataSetTableAdapters.ListsListTableAdapter class?

gnoon

I'm sorry to ask you many questions. It's because I don't use .NET 2.0 currently but just trying to help.

AWSHelpdesk

ASKER

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?

gnoon

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.

AWSHelpdesk

ASKER

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

ASKER CERTIFIED SOLUTION

AWSHelpdesk

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial