944,113 Members | Top Members by Rank

Web Development > ASP.NET > Page hangs or displays connection pooling max size error
Ad:
  • ASP.NET Discussion Thread
  • Unsolved
  • Views: 8280
  • ASP.NET RSS
Permalink
Jan 20th, 2005
0

Page hangs or displays connection pooling max size error

Expand Post »
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>
Reputation Points: 10
Solved Threads: 0
Newbie Poster
AlexClifford is offline Offline
8 posts
since Jan 2005
Permalink
Jan 28th, 2005
0

Re: Page hangs or displays connection pooling max size error

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.
Reputation Points: 10
Solved Threads: 0
Newbie Poster
michaelsowa is offline Offline
3 posts
since Jan 2005

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.
Message:
Previous Thread in ASP.NET Forum Timeline: Need help with completion of Cryptography program
Next Thread in ASP.NET Forum Timeline: Radiobuttonlist control - help!





About Us | Contact Us | Advertise | Acceptable Use Policy
Forum Index | Build Custom RSS Feed


Follow us on Twitter


© 2011 DaniWeb® LLC