Request Timeout in Visual Studio

Question

0.00/5 (No votes)

See more:

Hi!

I keep getting this 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.

Note:
Already tried to adjust connection time. The results was the windows application form is hanging. How do I expand the pool size that is said in the error? The maximum value of a loop that will save in a database is 250. :|

Sample:

VB

For x As Integer = 0 To (250 - 1)
InsertToDB(pnNumber, dateOfPay, Math.Round(principal_payments, 2), Math.Round(interest_payments, 2), Math.Round(monthly_payment, 2), Math.Round(balance, 2))
Next

VB

Private Sub InsertToDB(ByVal pnNumber As String, ByVal dateOfPayment As String, ByVal principalAmount As Double, ByVal interestAmount As Double,
                           ByVal monthlyPayment As Double, ByVal balance As Double)

        'MessageBox.Show(pnNumber + ", " +
        '                Convert.ToString(dateOfPayment) + ", " +
        '                Convert.ToString(principalAmount) + ", " +
        '                Convert.ToString(interestAmount) + ", " +
        '                Convert.ToString(monthlyPayment) + ", " +
        '                Convert.ToString(balance) + ", " +
        '                DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss") + ", " +
        '                userid)
        Try
            Dim conn As SqlConnection
            Dim comm As SqlCommand
            Dim reader As SqlDataReader
            Dim connectionString As String = ConfigurationManager.ConnectionStrings("msSqlConnectionString1").ConnectionString
            conn = New SqlConnection(connectionString)
            comm = New SqlCommand()

            comm.Connection = conn

            comm.CommandType = CommandType.StoredProcedure

            comm.CommandText = "sp_SetPNAmort"

            comm.Parameters.Add("@PNNumber", Data.SqlDbType.NVarChar)
            comm.Parameters.Add("@periodDate", Data.SqlDbType.NVarChar)
            comm.Parameters.Add("@principalAmount", Data.SqlDbType.Float)
            comm.Parameters.Add("@interestAmount", Data.SqlDbType.Float)
            comm.Parameters.Add("@payment", Data.SqlDbType.Float)
            comm.Parameters.Add("@osBalance", Data.SqlDbType.Float)
            comm.Parameters.Add("@dateGenerated", Data.SqlDbType.NVarChar)
            comm.Parameters.Add("@uploadedBy", Data.SqlDbType.NVarChar)

            comm.Parameters("@PNNumber").Value = pnNumber
            comm.Parameters("@periodDate").Value = dateOfPayment
            comm.Parameters("@principalAmount").Value = principalAmount
            comm.Parameters("@interestAmount").Value = interestAmount
            comm.Parameters("@payment").Value = monthlyPayment
            comm.Parameters("@osBalance").Value = balance
            comm.Parameters("@dateGenerated").Value = DateTime.Now.ToString("yyyy/MM/dd HH:mm:ss")
            comm.Parameters("@uploadedBy").Value = userid

            conn.Open()
            reader = comm.ExecuteReader()
            reader.Close()
        Catch ex As Exception
            MessageBox.Show(ex.Message, "Alert!!!")
        End Try
        

    End Sub

Posted 14-Jul-14 16:49pm

mitchiee1226

Updated 14-Jul-14 19:21pm

v2

Add a Solution

Comments

ArunRajendra 14-Jul-14 23:05pm

Are you sure that connection pool is a problem. I guess the problem is with connction not being closed.

mitchiee1226 15-Jul-14 1:21am

Updated the question for you. :)

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Biswa Pujarini Mohapatra · Accepted Answer · 2014-07-14T17:14:00

it can be the

1. Connection not closed
2. Deadlock somewhere
3. Database optimal query plan / stats

To check Deadlock Connect to your database with Sql Server Management Studio. In the left pane right-click on the server node and select Activity Monitor. Take a look at the running processes. Normally most will be idle or running. When the problem occurs you can identify any blocked process by the process state. If you right-click on the process and select details it'll show you the last query executed by the process.

SQL

The second issue will cause the database to use a sub-optimal query plan. It can be resolved by clearing the statistics:

exec sp_updatestats

Sarvesh Kumar Gupta · Accepted Answer · 2014-07-14T19:27:00

Solution 2

Hi,

Please make Some changes in Connection String

1. define min pool size and Max pool size

2. define Connection Time Out Time

Both settings will be in the connection String. Please change, check and confirm.

Posted 14-Jul-14 19:27pm

Sarvesh Kumar Gupta

ArunRajendra · Accepted Answer · 2014-07-14T19:36:00

Solution 3

Try putting close after reader si closed. You also do some code reorganization so that connection will be closed in finally block.

VB

conn.Close()

http://msdn.microsoft.com/en-us/library/fk6t46tz.aspx[^]

Posted 14-Jul-14 19:36pm

ArunRajendra