ASP.NET RSS ASP.NET RSS

Page hangs or displays connection pooling max size error

Please support our ASP.NET advertiser: $4.95 a Month - ASP.NET Web Hosting – Click Here!
Reply

•
•
Join Date: Jan 2005
Posts: 8
Reputation: AlexClifford is an unknown quantity at this point 
Solved Threads: 0
AlexClifford AlexClifford is offline Offline
Newbie Poster

Page hangs or displays connection pooling max size error

 
0
  #1
Jan 20th, 2005
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:

ASP.NET Syntax (Toggle Plain Text) 
  1. Timeout expired. The timeout period elapsed prior to obtaining
  2. a connection from the pool. This may have occurred because all pooled
  3. 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.

ASP.NET Syntax (Toggle Plain Text) 
  1. <%@ Import Namespace="System.Data" %>
  2. <%@ Page Language="vb" AutoEventWireup="false" Codebehind="all.aspx.vb" Explicit="True" Inherits="Contractors.all" %>
  3. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
  4. <html lang="en">
  5. 	<head>
  6. 		<title>Contractors and Consultants Induction Search Tool</title> 
  7. 		<!--
  8. 		Hobart Water, Alex Clifford
  9. 		Contractors and Consultants Induction Search Tool
  10. 		Contact aclifford@hobartwater.com.au for Support
  11.  
  12. 		Filename: all.aspx
  13. 		Date:     8:53 PM 12/11/2004
  14. 		Version:  0.1
  15. 		Purpose:  
  16. 		-->
  17. 		<meta name="GENERATOR" content="Microsoft Visual Studio .NET 7.1">
  18. 		<meta name="CODE_LANGUAGE" content="Visual Basic .NET 7.1">
  19. 		<meta name="vs_defaultClientScript" content="VBScript">
  20. 		<meta name="vs_targetSchema" content="http://schemas.microsoft.com/intellisense/ie5">
  21. 		<meta http-equiv="content-type" content="text/html;charset=utf-8">
  22. 		<meta http-equiv="Content-Style-Type" content="text/css">
  23. 		<link rel="stylesheet" href="style.css" type="text/css">
  24. 	</head>
  25. 	<body>
  26. 		<table cellspacing="0" cellpadding="0" border="0">
  27. 			<tr>
  28. 				<td width="20" background="images/topleft_corner.gif" height="20"></td>
  29. 				<td background="images/top_border.gif" height="20"></td>
  30. 				<td width="20" background="images/topright_corner.gif" height="20"></td>
  31. 			</tr>
  32. 			<tr>
  33. 				<td width="20" background="images/left_border.gif"></td>
  34. 				<td valign="top" bgcolor="#f5f5f5">
  35. 					<span class="title">Contractors and Consultants Database Search</span><br>
  36. 					<br>
  37. 					<span class="links"></span><a href="indexname.aspx">Search by Name</a> 
  38. 					- <a href="indexcompany.aspx">Search by Company</a> - <a href="all.aspx">List All</a><br>
  39. 					<br>
  40. 					<%
  41.  
  42. 						Dim conn_String As String
  43. 						Dim sSQL As String
  44. 						Dim nameSQL As String
  45. 						Dim RecordSort
  46. 						Dim intOrder
  47. 						Dim RecordFilter
  48. 						Dim DateIssuedOrder
  49. 						Dim SurnameOrder
  50. 						Dim FirstNameOrder
  51. 						Dim ContactNumberOrder
  52. 						Dim CompanyOrder
  53.  
  54. 						Dim Dt As DataRow
  55.  
  56. 						Dim MyConnection As System.Data.SqlClient.SqlConnection
  57. 						Dim MyCommand As System.Data.SqlClient.SqlCommand
  58. 						Dim MyReader As System.Data.SqlClient.SqlDataReader
  59.  
  60. 						conn_String = "Provider=SQLOLEDB.1;Data Source=DBASESVR;Initial Catalog=Contractors;uid=CSC;pwd=CSC!"
  61. 						sSQL = "SELECT ID, VendorNo, DateIssued, Surname, FirstName, ContactNumber, Address FROM Contractors.dbo.Contacts"
  62.  
  63. 						RecordSort = Request.QueryString("sort")
  64. 						RecordFilter = Request.QueryString("filter")
  65.  
  66. 						If Len(Request.QueryString("order")) > 0 Then
  67. 							intOrder = Request.QueryString("order")
  68. 						Else
  69. 							intOrder = 0
  70. 						End If
  71.  
  72. 						Select Case RecordSort
  73. 							Case 1
  74. 								If intOrder = 0 Then
  75. 									sSQL = sSQL & " ORDER BY Surname ASC"
  76. 									SurnameOrder = 1
  77.  
  78. 								Else
  79. 									sSQL = sSQL & " ORDER BY Surname DESC"
  80. 									SurnameOrder = 0
  81. 								End If
  82.  
  83. 							Case 2
  84. 								If intOrder = 0 Then
  85. 									sSQL = sSQL & " ORDER BY DateIssued ASC, Surname ASC"
  86. 									DateIssuedOrder = 1
  87. 								Else
  88. 									sSQL = sSQL & " ORDER BY DateIssued DESC, Surname ASC"
  89. 									DateIssuedOrder = 0
  90. 								End If
  91.  
  92. 							Case 3
  93. 								If intOrder = 0 Then
  94. 									sSQL = sSQL & " ORDER BY FirstName ASC, Surname ASC"
  95. 									FirstNameOrder = 1
  96. 								Else
  97. 									sSQL = sSQL & " ORDER BY FirstName DESC, Surname ASC"
  98. 									FirstNameOrder = 0
  99. 								End If
  100.  
  101. 							Case 4
  102. 								If intOrder = 0 Then
  103. 									sSQL = sSQL & " ORDER BY ContactNumber ASC, Surname ASC"
  104. 									ContactNumberOrder = 1
  105. 								Else
  106. 									sSQL = sSQL & " ORDER BY ContactNumber DESC, Surname ASC"
  107. 									ContactNumberOrder = 0
  108. 								End If
  109.  
  110. 							Case 5
  111. 								If intOrder = 0 Then
  112. 									sSQL = sSQL & " ORDER BY VendorNo ASC, Surname ASC"
  113. 									CompanyOrder = 1
  114. 								Else
  115. 									sSQL = sSQL & " ORDER BY VendorNo DESC, Surname ASC"
  116. 									CompanyOrder = 0
  117. 								End If
  118.  
  119. 							Case Else
  120. 								sSQL = sSQL & " ORDER BY Surname ASC"
  121. 						End Select
  122.  
  123. 						Call ConnectToDB(sSQL, ds, conn_String)
  124.  
  125. 						%>
  126. 					<table border="0" bordercolor="#000000" cellpadding="5" cellspacing="0" style="BORDER-COLLAPSE: collapse"
  127. 						width="700">
  128. 							<tr class="Heading1">
  129. 								<td class="TopRow" width="100" height="20">
  130. 									<b><a href="all.aspx?sort=2&order=<%=DateIssuedOrder%>" class="head">
  131. 											Date Issued</a></b>
  132. 								</td>
  133. 								<td class="TopRow" width="100" height="20">
  134. 									<b><a href="all.aspx?sort=1&order=<%=SurnameOrder%>" class="head">
  135. 											Surname</a></b>
  136. 								</td>
  137. 								<td class="TopRow" width="100" height="20">
  138. 									<b><a href="all.aspx?sort=3&order=<%=FirstNameOrder%>" class="head">
  139. 											First Name(s)</a></b>
  140. 								</td>
  141. 								<td class="TopRow" width="100" height="20">
  142. 									<b><a href="all.aspx?sort=4&order=<%=ContactNumberOrder%>" class="head">
  143. 											Contact #</a></b>
  144. 								</td>
  145. 								<td class="TopRow" width="300" height="20">
  146. 									<b><a href="all.aspx?sort=5&order=<%=CompanyOrder%>" class="head">
  147. 											Company</a></b>
  148. 								</td>
  149. 							</tr>
  150. 							<%
  151.  
  152. 							dim i
  153. 							i = 0
  154.  
  155. 							Try
  156.  
  157. 							'Do While Not (RsDisplay.EOF OR RsDisplay.BOF)
  158. 							For Each Dt In ds.Tables(0).Rows
  159.  
  160. 							nameSQL = "SELECT Name FROM [BMIS Live Database].dbo.[BMIS Live Database$Vendor] WHERE No_ = '" & Dt(1) & "'"
  161. 							MyConnection = New System.Data.SqlClient.SqlConnection("server=DBASESVR;uid=CSC;pwd=CSC!;database=BMIS Live Database")
  162. 							MyConnection.Open()
  163. 							MyCommand = New System.Data.SqlClient.SqlCommand(nameSQL, MyConnection)
  164. 							MyReader = MyCommand.ExecuteReader
  165.  
  166. 							While MyReader.Read
  167.  
  168. 							If (i MOD 2 = 0) Then
  169. 								Response.Write("<tr class=""Heading2"">" & vbCrLf)
  170. 								Response.Write("<td class=""OddColour"" width=""100"" height=""20"">" & vbCrLf)
  171. 								Response.Write(Dt(2) & vbCrLf)
  172. 								Response.Write("</td>" & vbCrLf)
  173. 								Response.Write("<td class=""OddColour"" width=""100"" height=""20"">" & vbCrLf)
  174. 								Response.Write("<a href=""info.aspx?ID=" & Dt(0) & """ class=""main"">" & vbCrLf)
  175. 								Response.Write(Dt(3) & vbCrLf)
  176. 								Response.Write(("</a>") & vbCrLf)
  177. 								Response.Write("</td>" & vbCrLf)
  178. 								Response.Write("<td class=""OddColour"" width=""100"" height=""20"">" & vbCrLf)
  179. 								Response.Write(Dt(4) & vbCrLf)
  180. 								Response.Write("</td>" & vbCrLf)
  181. 								Response.Write("<td class=""OddColour"" width=""100"" height=""20"">" & vbCrLf)
  182. 								Response.Write(Dt(5) & vbCrLf)
  183. 								Response.Write("</td>" & vbCrLf)
  184. 								Response.Write("<td class=""OddColour"" width=""300"" height=""20"">" & vbCrLf)
  185. 								Response.Write(MyReader.GetValue(0) & vbCrLf)
  186. 								Response.Write("</td>" & vbCrLf)
  187. 							Else
  188. 								Response.Write("<tr class=""Heading2"">" & vbCrLf)
  189. 								Response.Write("<td class=""EvenColour"" width=""100"" height=""20"">" & vbCrLf)
  190. 								Response.Write(Dt(2) & vbCrLf)
  191. 								Response.Write("</td>" & vbCrLf)
  192. 								Response.Write("<td class=""EvenColour"" width=""100"" height=""20"">" & vbCrLf)
  193. 								Response.Write("<a href=""info.aspx?ID=" & Dt(0) & """ class=""main"">" & vbCrLf)
  194. 								Response.Write(Dt(3) & vbCrLf)
  195. 								Response.Write(("</a>") & vbCrLf)
  196. 								Response.Write("</td>" & vbCrLf)
  197. 								Response.Write("<td class=""EvenColour"" width=""100"" height=""20"">" & vbCrLf)
  198. 								Response.Write(Dt(4) & vbCrLf)
  199. 								Response.Write("</td>" & vbCrLf)
  200. 								Response.Write("<td class=""EvenColour"" width=""100"" height=""20"">" & vbCrLf)
  201. 								Response.Write(Dt(5) & vbCrLf)
  202. 								Response.Write("</td>" & vbCrLf)
  203. 								Response.Write("<td class=""EvenColour"" width=""300"" height=""20"">" & vbCrLf)
  204. 								Response.Write(MyReader.GetValue(0) & vbCrLf)
  205. 								Response.Write("</td>" & vbCrLf)
  206. 							End If
  207.  
  208. 							End While
  209.  
  210. 							i = i + 1
  211. 							Next
  212.  
  213. 							Catch ex as Exception
  214.  
  215. 							Finally
  216.  
  217. 							'MyReader.Close()
  218. 							MyConnection.Close()
  219.  
  220. 							End try
  221.  
  222. 							%>
  223. 			</tr>
  224. 		</table>
  225. 		</td>
  226. 		<td width="20" background="images/right_border.gif"></td>
  227. 		</tr>
  228. 		<tr>
  229. 			<td width="20" background="images/bottomleft_corner.gif" height="20"></td>
  230. 			<td background="images/bottom_border.gif" height="20"></td>
  231. 			<td width="20" background="images/bottomright_corner.gif" height="20"></td>
  232. 		</tr>
  233. 		</table>
  234. 	</body>
  235. </html>
Reply With Quote Quick reply to this message  
•
•
Join Date: Jan 2005
Posts: 3
Reputation: michaelsowa is an unknown quantity at this point 
Solved Threads: 0
michaelsowa michaelsowa is offline Offline
Newbie Poster

Re: Page hangs or displays connection pooling max size error

 
0
  #2
Jan 28th, 2005
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.
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread is more than three months old.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the ASP.NET Forum
Thread Tools Search this Thread



.net 2.0 3.5 activexcontrol advice ajax alltypeofvideos appliances asp asp.net bc30451 beginner bottomasp.net browser businesslogiclayer c# c#gridviewcolumn cac checkbox commonfunctions compatible confirmationcodegeneration content contenttype courier css dataaccesslayer database datagrid datagridview datagridviewcheckbox datalist development dgv dropdownlist dropdownmenu dynamically edit fileuploader fill flash flv formatdecimal forms formview gridview gudi homeedition iframe iis javascript jquery listbox menu microsoft mono mouse mssql multistepregistration nameisnotdeclared news objects opera panelmasterpagebuttoncontrols redirect registration relationaldatabases reportemail rotatepage schoolproject security serializesmo.table sessionvariables silverlight smartcard smoobjects software sql-server sqlserver2005 suse textbox tracking treeview unauthorized validatedate validation vb.net video videos virtualdirectory vista visual-studio visualstudio web webapplications webarchitecture webdevelopemnt webprogramming webservice youareanotmemberofthedebuggerusers
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC