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>