Page hangs or displays connection pooling max size error

Question

AlexClifford 0 Newbie Poster

19 Years Ago

Hi, I have an ASP.NET page that contains quite a few looping SQL queries. After either loading this page (all.aspx) or doing a few table sorts the page freezes up and just loads for about 20 seconds. Sometimes the page just loads and doesn't show any results when I know there should be results and sometimes it displays 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.

I've read on the Microsoft website that the connection pools sizing issue is a bug in the Framework but I'm not sure if the freezing is related. The performance of the page just seems poor and the results aren't 100% reliable.

I've had this same code running fine on ASP, doing the same database queries and the results were fine! Please if you can help in anyway it would be really appreiciated, the entire page's code is shown below.

<%@ Import Namespace="System.Data" %>
<%@ Page Language="vb" AutoEventWireup="false" Codebehind="all.aspx.vb" Explicit="True" Inherits="Contractors.all" %>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html lang="en">
	<head>
		<title>Contractors and Consultants Induction Search Tool</title> 
		<!--
		Hobart Water, Alex Clifford
		Contractors and Consultants Induction Search Tool
		Contact aclifford@hobartwater.com.au for Support

		Filename: all.aspx
		Date:     8:53 PM 12/11/2004
		Version:  0.1
		Purpose:  
		-->
		<meta name="GENERATOR" content="Microsoft Visual Studio .NET 7.1">
		<meta name="CODE_LANGUAGE" content="Visual Basic .NET 7.1">
		<meta name="vs_defaultClientScript" content="VBScript">
		<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
		<meta http-equiv="content-type" content="text/html;charset=utf-8">
		<meta http-equiv="Content-Style-Type" content="text/css">
		<link rel="stylesheet" href="style.css" type="text/css">
	</head>
	<body>
		<table cellspacing="0" cellpadding="0" border="0">
			<tr>
				<td width="20" background="images/topleft_corner.gif" height="20"></td>
				<td background="images/top_border.gif" height="20"></td>
				<td width="20" background="images/topright_corner.gif" height="20"></td>
			</tr>
			<tr>
				<td width="20" background="images/left_border.gif"></td>
				<td valign="top" bgcolor="#f5f5f5">
					<span class="title">Contractors and Consultants Database Search</span><br>
					<br>
					<span class="links"></span><a href="indexname.aspx">Search by Name</a> 
					- <a href="indexcompany.aspx">Search by Company</a> - <a href="all.aspx">List All</a><br>
					<br>
					<%

						Dim conn_String As String
						Dim sSQL As String
						Dim nameSQL As String
						Dim RecordSort
						Dim intOrder
						Dim RecordFilter
						Dim DateIssuedOrder
						Dim SurnameOrder
						Dim FirstNameOrder
						Dim ContactNumberOrder
						Dim CompanyOrder

						Dim Dt As DataRow
						
						Dim MyConnection As System.Data.SqlClient.SqlConnection
						Dim MyCommand As System.Data.SqlClient.SqlCommand
						Dim MyReader As System.Data.SqlClient.SqlDataReader
						
						conn_String = "Provider=SQLOLEDB.1;Data Source=DBASESVR;Initial Catalog=Contractors;uid=CSC;pwd=CSC!"
						sSQL = "SELECT ID, VendorNo, DateIssued, Surname, FirstName, ContactNumber, Address FROM Contractors.dbo.Contacts"

						RecordSort = Request.QueryString("sort")
						RecordFilter = Request.QueryString("filter")
						
						If Len(Request.QueryString("order")) > 0 Then
							intOrder = Request.QueryString("order")
						Else
							intOrder = 0
						End If

						Select Case RecordSort
							Case 1
								If intOrder = 0 Then
									sSQL = sSQL & " ORDER BY Surname ASC"
									SurnameOrder = 1

								Else
									sSQL = sSQL & " ORDER BY Surname DESC"
									SurnameOrder = 0
								End If

							Case 2
								If intOrder = 0 Then
									sSQL = sSQL & " ORDER BY DateIssued ASC, Surname ASC"
									DateIssuedOrder = 1
								Else
									sSQL = sSQL & " ORDER BY DateIssued DESC, Surname ASC"
									DateIssuedOrder = 0
								End If

							Case 3
								If intOrder = 0 Then
									sSQL = sSQL & " ORDER BY FirstName ASC, Surname ASC"
									FirstNameOrder = 1
								Else
									sSQL = sSQL & " ORDER BY FirstName DESC, Surname ASC"
									FirstNameOrder = 0
								End If

							Case 4
								If intOrder = 0 Then
									sSQL = sSQL & " ORDER BY ContactNumber ASC, Surname ASC"
									ContactNumberOrder = 1
								Else
									sSQL = sSQL & " ORDER BY ContactNumber DESC, Surname ASC"
									ContactNumberOrder = 0
								End If

							Case 5
								If intOrder = 0 Then
									sSQL = sSQL & " ORDER BY VendorNo ASC, Surname ASC"
									CompanyOrder = 1
								Else
									sSQL = sSQL & " ORDER BY VendorNo DESC, Surname ASC"
									CompanyOrder = 0
								End If

							Case Else
								sSQL = sSQL & " ORDER BY Surname ASC"
						End Select
						
						Call ConnectToDB(sSQL, ds, conn_String)
						
						%>
					<table border="0" bordercolor="#000000" cellpadding="5" cellspacing="0" style="BORDER-COLLAPSE: collapse"
						width="700">
							<tr class="Heading1">
								<td class="TopRow" width="100" height="20">
									<b><a href="all.aspx?sort=2&order=<%=DateIssuedOrder%>" class="head">
											Date Issued</a></b>
								</td>
								<td class="TopRow" width="100" height="20">
									<b><a href="all.aspx?sort=1&order=<%=SurnameOrder%>" class="head">
											Surname</a></b>
								</td>
								<td class="TopRow" width="100" height="20">
									<b><a href="all.aspx?sort=3&order=<%=FirstNameOrder%>" class="head">
											First Name(s)</a></b>
								</td>
								<td class="TopRow" width="100" height="20">
									<b><a href="all.aspx?sort=4&order=<%=ContactNumberOrder%>" class="head">
											Contact #</a></b>
								</td>
								<td class="TopRow" width="300" height="20">
									<b><a href="all.aspx?sort=5&order=<%=CompanyOrder%>" class="head">
											Company</a></b>
								</td>
							</tr>
							<%

							dim i
							i = 0
							
							Try

							'Do While Not (RsDisplay.EOF OR RsDisplay.BOF)
							For Each Dt In ds.Tables(0).Rows

							nameSQL = "SELECT Name FROM [BMIS Live Database].dbo.[BMIS Live Database$Vendor] WHERE No_ = '" & Dt(1) & "'"
							MyConnection = New System.Data.SqlClient.SqlConnection("server=DBASESVR;uid=CSC;pwd=CSC!;database=BMIS Live Database")
							MyConnection.Open()
							MyCommand = New System.Data.SqlClient.SqlCommand(nameSQL, MyConnection)
							MyReader = MyCommand.ExecuteReader

							While MyReader.Read

							If (i MOD 2 = 0) Then
								Response.Write("<tr class=""Heading2"">" & vbCrLf)
								Response.Write("<td class=""OddColour"" width=""100"" height=""20"">" & vbCrLf)
								Response.Write(Dt(2) & vbCrLf)
								Response.Write("</td>" & vbCrLf)
								Response.Write("<td class=""OddColour"" width=""100"" height=""20"">" & vbCrLf)
								Response.Write("<a href=""info.aspx?ID=" & Dt(0) & """ class=""main"">" & vbCrLf)
								Response.Write(Dt(3) & vbCrLf)
								Response.Write(("</a>") & vbCrLf)
								Response.Write("</td>" & vbCrLf)
								Response.Write("<td class=""OddColour"" width=""100"" height=""20"">" & vbCrLf)
								Response.Write(Dt(4) & vbCrLf)
								Response.Write("</td>" & vbCrLf)
								Response.Write("<td class=""OddColour"" width=""100"" height=""20"">" & vbCrLf)
								Response.Write(Dt(5) & vbCrLf)
								Response.Write("</td>" & vbCrLf)
								Response.Write("<td class=""OddColour"" width=""300"" height=""20"">" & vbCrLf)
								Response.Write(MyReader.GetValue(0) & vbCrLf)
								Response.Write("</td>" & vbCrLf)
							Else
								Response.Write("<tr class=""Heading2"">" & vbCrLf)
								Response.Write("<td class=""EvenColour"" width=""100"" height=""20"">" & vbCrLf)
								Response.Write(Dt(2) & vbCrLf)
								Response.Write("</td>" & vbCrLf)
								Response.Write("<td class=""EvenColour"" width=""100"" height=""20"">" & vbCrLf)
								Response.Write("<a href=""info.aspx?ID=" & Dt(0) & """ class=""main"">" & vbCrLf)
								Response.Write(Dt(3) & vbCrLf)
								Response.Write(("</a>") & vbCrLf)
								Response.Write("</td>" & vbCrLf)
								Response.Write("<td class=""EvenColour"" width=""100"" height=""20"">" & vbCrLf)
								Response.Write(Dt(4) & vbCrLf)
								Response.Write("</td>" & vbCrLf)
								Response.Write("<td class=""EvenColour"" width=""100"" height=""20"">" & vbCrLf)
								Response.Write(Dt(5) & vbCrLf)
								Response.Write("</td>" & vbCrLf)
								Response.Write("<td class=""EvenColour"" width=""300"" height=""20"">" & vbCrLf)
								Response.Write(MyReader.GetValue(0) & vbCrLf)
								Response.Write("</td>" & vbCrLf)
							End If

							End While
							
							i = i + 1
							Next
							
							Catch ex as Exception
							
							Finally
							
							'MyReader.Close()
							MyConnection.Close()

							End try

							%>
			</tr>
		</table>
		</td>
		<td width="20" background="images/right_border.gif"></td>
		</tr>
		<tr>
			<td width="20" background="images/bottomleft_corner.gif" height="20"></td>
			<td background="images/bottom_border.gif" height="20"></td>
			<td width="20" background="images/bottomright_corner.gif" height="20"></td>
		</tr>
		</table>
	</body>
</html>

asp.net

2 Contributors
1 Reply
87 Views
1 Week Discussion Span
Latest Post 19 Years Ago Latest Post by michaelsowa

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

michaelsowa 0 Newbie Poster · Answer 1 · 2005-01-28T17:42:54+00:00

Make sure you that you close your datareaders and connections.

Where do you close the connection for the outer query? Also you should check any pages proceeding this page to make sure all the connections are getting closed properly. It may not be just this page it could be other pages compounding until you have no more connections available in the pool.