Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Page hangs or displays connection pooling max size error

Status
Not open for further replies.
ajclifford

ajclifford

Programmer
Jan 16, 2005
17
AU
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:

Code: 
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. Also if its of any help, Contractors and BMIS Live Database are both different database's in the same MS SQL Server.


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.

Code: 
<%@ 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" "[URL unfurl="true"]http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">[/URL]
<html lang="en">
	<head>
		<title>Contractors and Consultants Induction Search Tool</title>
		<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="[URL unfurl="true"]http://schemas.microsoft.com/intellisense/ie5">[/URL]
		<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=user;pwd=pass"
						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>
 
Sort by date Sort by votes
Well you're mixing asp and asp.net up a bit here but the connection pooling issue is usually due to not closing datareaders and such.

You have this in your code...

'MyReader.Close()

where the call to close the MyReader is commented out??

RG
 
Upvote 0 Downvote
Hi,

As sirjon points out, you are mixing up ASP and .NET a bit in you page. I notice that you are using a code-behind page as you have the following declaration:
Code: 
<%@ Page Language="vb" AutoEventWireup="false" Codebehind="all.aspx.vb" Explicit="True" Inherits="Contractors.all" %>
I would suggest that you place all database (and any other .NET code as well) in your code-behind as this will allow you to step through your code. You will then be able to see where the code is getting to and also where it is freezing.

You may also want to look into using something like a repeater control for displaying your data as opposed to using Response.Write.

Hope this helps.


----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty ( or read FAQ222-2244 on how to get better results.
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

AhmedAzizBarbary
  • Locked
  • Question
How to pass list of checkboxes not checked on page model razor page?
Replies
0
Views
768
AhmedAzizBarbary
AhmedAzizBarbary
AhmedAzizBarbary
  • Locked
  • Question
How to read data response body after success login razor page ?
Replies
0
Views
703
AhmedAzizBarbary
AhmedAzizBarbary
JohnB33
  • Locked
  • Question
int type causes error
Replies
0
Views
616
JohnB33
JohnB33
weberm
  • Locked
  • Question
How do I make a page with multiple AJAX custom controls?
Replies
2
Views
681
weberm
weberm
G12Consult
  • Locked
  • Question
Javascript from Content Pages
Replies
0
Views
612
G12Consult
G12Consult

Part and Inventory Search

Sponsor

Back
Top