[Solved] IIS Error: Timeout expired. The timeout period elapsed prior to obtaining a connection from the pool

kevinf
 
on May 25, 2022 11:00 PM
2574 Views

I am getting

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

Using VB.net 2014 SQL Server over a network.

The program opens reads insert/updates multiple files see below part of the build section in program I open close and clear connections. I have attached the web.config file with max poolsize set to 300.

 Thanks

Dim sFullN As String
dim conn as New SqlConnection(connString)
 
Dim conCode As New SqlConnection(connString)
 
comm = New SqlCommand("Select Distinct Fd.Empid, c.*, S.FullN, R.ClassRec, S.WebChoices, S.YearCode, s.StudentNo from FutureDirectionsModel As FD Left Join CareerProfile As C On c.Empid = FD.Empid left Join SchoolMaster As S On c.Empid = s.Empid Left Join StudentRecommendations As R On c.Empid = r.Empid And c.ClassCode = r.ClassDesc where c.Empid is not null order by c.Empid, c.YearOrder, c.ClassCode", conn)
conn.Open()
Dim reader1 As SqlDataReader = comm.ExecuteReader
sCnt = 2
Dim sn As String = ""
Do While reader1.Read = True
    SqlCommand = New SqlCommand()
    With SqlCommand
        conCode = New SqlConnection(connString)
        If conCode.State = ConnectionState.Open Then conCode.Close()
        conCode.Open()
        .CommandText = "Select YearOrder From YearCode Where YearCode=@Code"
        .Parameters.Clear()
        sFullN = Trim(reader1.GetValue(14))
        If Not reader1.IsDBNull(4) Then
            .Parameters.AddWithValue("@Code", reader1.GetString(4))
            .Connection = conCode
            .CommandType = CommandType.Text
            yo = .ExecuteScalar()
            fnd = fnd + 1
            .CommandText = "INSERT INTO FutureDirectionsModel (Id, Empid, FName, ClassCode, GC1, GC2, Recommendation, Position, YearCode, ClassYearOrder, ClassLevel1, ClassLevel2, YearRate, SurveyYear, HColor, HFColor, MaxRate, Desc1, Desc2, Desc3, HColorName, HFColorName, StudentYear)  "
            .CommandText &= " VALUES(@Id, @Empid, @FName, @Class, @GC1, @GC2, @Recommendation, @Pos, @ClassYear, @ClassYearOrder, @ClassLevel1, @ClassLevel2, @YearRate, @SurveyYear, @HColor, @HFColor, @MaxRate, @Desc1, @Desc2, @Desc3, @HColorName, @HFColorName, @StudentYear) "
 
            If conCode.State = ConnectionState.Open Then conCode.Close()
            .Connection = conCode
            .CommandType = CommandType.Text
            conCode.Open()
            .Parameters.Clear()
            .Parameters.AddWithValue("@Id", fnd)
            .Parameters.AddWithValue("@Empid", reader1.GetInt32(2))
            .Parameters.AddWithValue("@FName", Trim(reader1.GetValue(14)))
 
            .Parameters.AddWithValue("@Class", Trim(reader1.GetValue(3)))
            If Not reader1.IsDBNull(12) Then
                .Parameters.AddWithValue("@GC1", reader1.GetValue(13))
            Else
                .Parameters.AddWithValue("@GC1", "")
            End If
            If Not reader1.IsDBNull(5) Then
                .Parameters.AddWithValue("@GC2", reader1.GetValue(5))
            Else
                .Parameters.AddWithValue("@GC2", "")
            End If
            .Parameters.AddWithValue("@Pos", sCnt)
            .Parameters.AddWithValue("@ClassYear", reader1.GetString(4))
            .Parameters.AddWithValue("@ClassYearOrder", yo)
            .Parameters.AddWithValue("@ClassLevel1", reader1.GetValue(10))
            .Parameters.AddWithValue("@ClassLevel2", reader1.GetValue(11))
            .Parameters.AddWithValue("@Desc1", "")
            .Parameters.AddWithValue("@Desc2", "")
            .Parameters.AddWithValue("@Desc3", "")
            .Parameters.AddWithValue("@HColorName", 0)
            .Parameters.AddWithValue("@HFColorName", 0)
            If Not reader1.IsDBNull(15) Then
                .Parameters.AddWithValue("@Recommendation", Trim(reader1.GetValue(15)))
            Else
                .Parameters.AddWithValue("@Recommendation", "")
            End If
            If Not reader1.IsDBNull(6) Then
                .Parameters.AddWithValue("@YearRate", Trim(reader1.GetValue(6)))
            Else
                .Parameters.AddWithValue("@YearRate", 0)
            End If
            .Parameters.AddWithValue("@SurveyYear", SurveyYear)
            .Parameters.AddWithValue("@HColor", 0)
            .Parameters.AddWithValue("@HFColor", 0)
            .Parameters.AddWithValue("@MaxRate", MaxRate)
            .Parameters.AddWithValue("@Logo", ImageCount)
            .Parameters.AddWithValue("@StudentYear", Trim(reader1.GetValue(17)))
            .ExecuteNonQuery()
            conCode.Close()
            conCode.Dispose()
            SqlCommand.Dispose()
        End If
    End With
Loop
 
SqlConnection.ClearAllPools()
 
SqlCommand.Dispose()
comm.Dispose()
reader1.Close()
 
conn = New SqlConnection(connString)
conCode = New SqlConnection(connString)
comm = New SqlCommand("Select Distinct FD.Empid, e.*, s.FullN, s.YearCode from FutureDirectionsModel as FD Left Join SchoolMasterElective as e on e.Empid = FD.Empid left Join SchoolMaster as s on e.empid = s.empid", conn)
conn.Open()
reader1 = comm.ExecuteReader
sCnt = 3
sn = ""
Do While reader1.Read = True
    cnt = cnt + 1
    SqlCommand = New SqlCommand()
    With SqlCommand
        If conCode.State = ConnectionState.Open Then conCode.Close()
        conCode.Open()
        .CommandText = "Select YearOrder From YearCode Where YearCode=@Code"
        .Parameters.Clear()
        If Not reader1.IsDBNull(6) Then
            .Parameters.AddWithValue("@Code", reader1.GetString(6))
            .Connection = conCode
            .CommandType = CommandType.Text
            yo = .ExecuteScalar()
 
            fnd = fnd + 1
            .CommandText = "INSERT INTO FutureDirectionsModel (Id, Empid, FName, Electives1, Electives2, GE1, GE2, Position, Electives1Year, Electives2Year, ElectiveYearOrder, SurveyYear, HColor, HFColor, MaxRate, Desc1, Desc2, Desc3, HColorName, HFColorName, StudentYear)  "
            .CommandText &= " VALUES(@Id, @Empid, @FName, @Electives1, @Electives2,  @GE1, @GE2, @Pos, @Electives1Year, @Electives2Year, @ElectiveYearOrder, @SurveyYear, @HColor, @HFColor, @MaxRate, @Desc1, @Desc2, @Desc3, @HColorName, @HFColorName, @StudentYear) "
 
            If conCode.State = ConnectionState.Open Then conCode.Close()
            .Connection = conCode
            .CommandType = CommandType.Text
            conCode.Open()
            .Parameters.Clear()
            .Parameters.AddWithValue("@Id", fnd)
            .Parameters.AddWithValue("@Empid", reader1.GetInt32(2))
            .Parameters.AddWithValue("@FName", Trim(reader1.GetValue(8)))
            .Parameters.AddWithValue("@Electives1", Trim(reader1.GetValue(3)))
            .Parameters.AddWithValue("@Electives2", Trim(reader1.GetValue(3)))
            .Parameters.AddWithValue("@Desc1", "")
            .Parameters.AddWithValue("@Desc2", "")
            .Parameters.AddWithValue("@Desc3", "")
            .Parameters.AddWithValue("@HColorName", 0)
            .Parameters.AddWithValue("@HFColorName", 0)
            If Not reader1.IsDBNull(4) Then
                .Parameters.AddWithValue("@GE1", Trim(reader1.GetValue(4)))
            Else
                .Parameters.AddWithValue("@GE1", "")
            End If
 
            If Not reader1.IsDBNull(7) Then
                .Parameters.AddWithValue("@GE2 ", Trim(reader1.GetValue(7)))
            Else
                .Parameters.AddWithValue("@GE2 ", "")
            End If
 
            .Parameters.AddWithValue("@Pos", sCnt)
            .Parameters.AddWithValue("@Electives1Year", Trim(reader1.GetValue(6)))
            .Parameters.AddWithValue("@Electives2Year", Trim(reader1.GetValue(6)))
            .Parameters.AddWithValue("@ElectiveYearOrder", yo)
            .Parameters.AddWithValue("@SurveyYear", SurveyYear)
            .Parameters.AddWithValue("@HColor", 0)
            .Parameters.AddWithValue("@HFColor", 0)
            .Parameters.AddWithValue("@MaxRate", MaxRate)
            .Parameters.AddWithValue("@Logo", ImageCount)
            .Parameters.AddWithValue("@StudentYear", Trim(reader1.GetValue(9)))
            .ExecuteNonQuery()
            .Dispose()
        End If
    End With
Loop
 
SqlConnection.ClearAllPools()
conCode = New SqlConnection(connString)
conn = New SqlConnection(connString)

 

Download FREE API for Word, Excel and PDF in ASP.Net: Download
Mudassar
 
on May 28, 2022 03:01 AM

Hi,

I have check your code and there are few major issues.

1. You are opening connection and then running a while loop over SqlDataReader and that connection is closed after while loop ends and that line is missing in your shared code.

To solve this you must fetch data in a DataTable and then execute loop over a DataTable.

2. You are using too many IFs in code.

I recommend writing an SP and doing all this IF ELSE inside the SP.

3. You must not keep on creating connection variables once.

Iinstead you must create them inside a USING BLOCK, so that everything is cleared once you exit the Block.

Refer here:

ExecuteNonQuery: Insert Update and Delete examples in C# and VB.Net