Avatar of JamesAnthony
JamesAnthony
Flag for Ireland asked on

Connection Pooling

Hi All
Have this code, works etc but I am trying to fill a table with sql attached, works for 541 records exactly every time then get error listed
I dont want to increase the max pool size as doing this will affect other servers that I have to connect to


this is statement in a loop, values change accordingly
 
Call executesql(insert into pricematrixWgtNew( DescNo, Sex, Conf, Fat, Code1, KgMin, KgMax, Penback,Unit) select 1,'A','O+','5-',886,0,239.9,0,'K'")

CODE

   Sub ExecuteSQL(ByVal SQLStateMent As String)


        gCancel = False
        Dim ExecuteConn As New SqlConnection(ConnStr)
        If ExecuteConn.State = 1 Then ExecuteConn.Close()
        ExecuteConn.Open()
        Dim SQLCommand1 As New SqlCommand
        Try
            SQLCommand1.CommandType = CommandType.Text
            SQLCommand1.CommandText = SQLStateMent
            SQLCommand1.Connection = ExecuteConn
            SQLCommand1.ExecuteNonQuery()
            If ExecuteConn.State = 1 Then ExecuteConn.Close() : ExecuteConn.Dispose()
        Catch exsql As SqlException
            'MyBox(exsql.Message)
            gCancel = True
            If ExecuteConn.State = 1 Then ExecuteConn.Close() : ExecuteConn.Dispose()
        End Try
    End Sub


ERROR

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.

Visual Basic.NET

Avatar of undefined
Last Comment
Nasir Razzaq

8/22/2022 - Mon
guvera

Hi,
 

  Please check the below links.

  http://forums.asp.net/t/465979.aspx/1
  http://social.msdn.microsoft.com/Forums/en-IN/adodotnetdataproviders/thread/c57c0432-c27b-45ab-81ca-b2df76c911ef

 For your information also read this article,

 This problem occurred most probably because of connection leak. Either the connection string do not close properly or consistently.

When you intend to close your database connection, you want to make sure that you are really closing it. The following code looks fine yet causes a connection leak:

     SqlConnection conn = new SqlConnection(myConnectionString);

      conn.Open();

      doSomething();

      conn.Close();    
           

If doSomething() throws an exception - conn will never get explicitly closed. Here is how this can be corrected:


     SqlConnection conn = new SqlConnection(myConnectionString);

      try

      {

            conn.Open();

            doSomething(conn);

      }

      finally

     {

            conn.Close();                

      }


When returning a connection from a class method - make sure you cache it locally and call its Close method. The following code will leak a connection:


     OleDbCommand cmd new OleDbCommand(myUpdateQuery, getConnection());

      intres = cmd.ExecuteNonQuery();

     getConnection().Close(); // The connection returned from the first call to getConnection() is not being closed.

Instead of closing your connection, this line creates a new one and tries to close it.

 
Here are some solutions that you can try to solve the problem:
1) Check your application to make sure all database connections are closed when it is not needed.  ASP.NET is supposed to have garbage collector to reclaim unused resource.  However, on a busy site, it is likely that the connection pool will run out of connections before garbage collection kicks in.


2) You can raise the connection pool size in the connection string.  For example, you can add "Max Pool Size=100" to your connection string to increase the pool size to 100.

Thanks
Guvera
ASKER CERTIFIED SOLUTION
Nasir Razzaq

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
JamesAnthony

ASKER
Short and sweet, got the job done
thanks
Nasir Razzaq

Glad to help :-)
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck