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)