Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Jan 2005
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Page hangs or displays connection pooling max size error

    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.

    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" "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>

  • #2
    Supreme Master coder! glenngv's Avatar
    Join Date
    Jun 2002
    Location
    Philippines
    Posts
    11,068
    Thanks
    0
    Thanked 256 Times in 252 Posts
    You should open the db connection only once. So you need to take this out from the for-each loop

    MyConnection = New System.Data.SqlClient.SqlConnection("server=DBASESVR;uid=XXX;pwd=XXX;database=BMIS Live Database")
    MyConnection.Open()
    Glenn
    ____________________________________

    My Blog
    Tower of Hanoi Android app (FREE!)
    Tower of Hanoi Leaderboard
    Samegame Facebook App
    vBulletin Plugins
    ____________________________________

  • #3
    New to the CF scene
    Join Date
    Jan 2005
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ah ok, thanks for that. It doesn't freeze up on me now, no wonder with all those open connections!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •