...

View Full Version : Page hangs or displays connection pooling max size error



AlexClifford
01-20-2005, 09:54 PM
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>

glenngv
01-21-2005, 05:29 AM
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()

AlexClifford
01-21-2005, 06:27 AM
Ah ok, thanks for that. It doesn't freeze up on me now, no wonder with all those open connections! :)



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum