Connection - reach max pools

[asafblasberg]

Hello, I'm getting an ASP.NET error which says "Maximum number of connections reached - please increase max pool size."

Here is my full code,
please help me out - I've issue close commands all over the place to make sure there are no connections being leaked. Can someone find something wrong in my code?
Thanks!!

AB

***************
DEFAULT.ASPX
***************

<%@ Page Language="vb" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">

Protected featuredimg as string
Protected featuredimg2 as string
Protected featuredimg3 as string
Protected featuredimg4 as string
Protected featuredimg5 as string
Protected featuredimg6 as string
Protected featuredbigimg as string
Protected promotionheader as string
Protected promotiondesc as string
Protected homepictureurl as string

Protected strConn as String

Protected websiteurl as string
Protected websiteurl2 as string
Protected websiteurl3 as string
Protected websiteurl4 as string
Protected websiteurl5 as string
Protected websiteurl6 as string

Sub SrchResult()

dim description
mainlayer.visible = "false"
searchlayer.visible = "true"
footerlayer.visible = "false"
insidetext.visible = "false"

dispdata.visible = "true"
strConn = "server=***;" _
& "database=*;user id=*;pwd=*"


Dim CommandText as String = "SELECT * from products WHERE active = -1 AND " _
& "SKU LIKE '%" & frmsearch.text & "%' OR DESCRIPTION LIKE '%" & frmsearch.text & "%'"

Dim myConnection As New SqlConnection(strConn)
Dim objDR as SQLDataReader
Dim myCommand As New SqlCommand(CommandText, myConnection)

MyConnection.Open()
objDR=myCommand.ExecuteReader()
SearchResults.DataSource = objDR
SearchResults.DataBind()

if objdr.HasRows then
dispdata.imageurl = "images/searchresults.gif"
searchresults.visible = "true"

else
dispdata.imageurl = "images/noresults.gif"
end if
objdr.close()
MyConnection.Close()
End Sub

Sub ButtonSearch_Click(sender as object, e as imageclickeventargs)
SrchResult()
End Sub

Sub Page_Load(sender As Object, e As EventArgs)

mainlayer.visible = "true"
searchlayer.visible = "false"
footerlayer.visible = "true"
strConn = "server=X" _
& "database=X;user id=X;pwd=X"

Dim CommandText As String = "select websitepicture, websiteurl from featuredwebsiteitems where id = 1"
Dim CommandText2 As String = "select websitepicture, websiteurl from featuredwebsiteitems where id = 2"
Dim CommandText3 As String = "select websitepicture, websiteurl from featuredwebsiteitems where id = 3"
Dim CommandText4 As String = "select websitepicture, websiteurl from featuredwebsiteitems where id = 4"
Dim CommandText5 As String = "select websitepicture, websiteurl from featuredwebsiteitems where id = 5"
Dim CommandText6 As String = "select websitepicture, websiteurl from featuredwebsiteitems where id = 6"
Dim CommandTextPromotion as String = "select promotionheader, promotiondesc from promotions where promoid = 1"
Dim CommandTextHome As String = "select homepictureurl, homepicture from featuredpic where id = 1"

Dim myConnection As New SqlConnection(strConn)
Dim objdr as SQLDataReader
Dim objdr2 as sqldatareader
dim objdr3 as sqldatareader
dim objdr4 as sqldatareader
dim objdr5 as sqldatareader
dim objdr6 as sqldatareader
dim objdrhome as sqldatareader
dim objdrpromo as sqldatareader

Dim myCommand As New SqlCommand(CommandText, myConnection)
Dim myCommand2 As New SqlCommand(CommandText2, myConnection)
Dim myCommand3 As New SqlCommand(CommandText3, myConnection)
Dim myCommand4 as New SqlCommand(CommandText4, myConnection)
Dim myCommand5 as New SqlCommand(CommandText5, myConnection)
Dim myCommand6 as New SqlCommand(CommandText6, myConnection)
Dim myCommandHome as New SqlCommand(CommandTextHome, myConnection)
Dim myCommandPromo as New SqlCommand(CommandTextpromotion, myConnection)



myConnection.open()
objDR=myCommand.ExecuteReader()
objdr.read()
featuredimg = objdr("websitepicture")
websiteurl = objdr("websiteurl")
objdr.close()

objDR2=myCommand2.ExecuteReader()
objdr2.read()
featuredimg2 = objdr2("websitepicture")
websiteurl2 = objdr2("websiteurl")
objdr2.close()

objdr3=mycommand3.executereader()
objdr3.read()
featuredimg3 = objdr3("websitepicture")
websiteurl3 = objdr3("websiteurl")
objdr3.close()

objdr4=mycommand4.executereader()
objdr4.read()
featuredimg4 = objdr4("websitepicture")
websiteurl4 = objdr4("websiteurl")
objdr4.close()

objdr5=mycommand5.executereader()
objdr5.read()
featuredimg5 = objdr5("websitepicture")
websiteurl5 = objdr5("websiteurl")
objdr5.close()

objdr6=mycommand6.executereader()
objdr6.read()
featuredimg6 = objdr6("websitepicture")
websiteurl6 = objdr6("websiteurl")
objdr6.close()

objdrhome=mycommandhome.executereader()
objdrhome.read()
featuredbigimg = objdrhome("homepicture")
homepictureurl = objdrhome("homepictureurl")
objdrhome.close()

objdrpromo=mycommandpromo.executereader()
objdrpromo.read()
promotionheader = objdrpromo("promotionheader")
promotiondesc = objdrpromo("promotiondesc")
objdrpromo.close()

myconnection.close()


page.databind()
End Sub

</script>
<html>
<head>
<link rel="stylesheet" type="text/css" href="">
</head>

<form runat="server" id="features">
<div runat="server" style = "position: absolute; width: 754px; height: 33px; z-index: 1; left: 7px; top: 565px" id="footerlayer">
<!--#include file = "foot.html" -->

</div>


<div style="position: absolute; width: 754px; height: 48px; z-index: 1; left: 0px; top: 0px" id="header">

<!--#include file = "head.aspx" -->

</div>

<div style="position: absolute; width: 744px; height: 98px; z-index: 1; left:10px; top:82px" id="searchlayer" runat="server">
<asp:image cssclass = "gbbodybold" ID="dispdata" runat="server"></asp:image><br>
<aspataList id="searchresults" orizontalAlign="Center" repeatcolumns="3" cssclass = "gbhead" halign="center" ItemStyle-VerticalAlign="Top" itemstyle-horizontalalign= "Center" ItemStyle-Width = "300" width="100%" repeatlayout="table" runat="server">

<ItemStyle CssClass="dash"></ItemStyle>
<itemtemplate>
<p align="center">
<a href="viewproduct.aspx?pid=<%# DataBinder.Eval(Container.DataItem, ("idProduct"))%>&mcat=<%# DataBinder.Eval(Container.DataItem, ("mastercategory"))%>&id=<%# DataBinder.Eval(Container.DataItem, ("idcategory"))%>">
<asp:Image id="Image1" runat="server" src='<%# DataBinder.Eval(Container.DataItem, ("smallimageurl"))%>'>
</asp:Image></a><br>
<%# DataBinder.Eval(Container.DataItem, ("sku"))%><br>

<b><%# DataBinder.Eval(Container.DataItem, ("description"))%></b><br>

List Price: <%# DataBinder.Eval(Container.DataItem, ("listprice"), "{0:c}")%><br>
<b><font color="#CC0000">Sale Price: <%# DataBinder.Eval(Container.DataItem, ("price"), "{0:c}")%><br></font>
<b><font size="3"></font>
</itemtemplate>

</aspataList>
</div>
<div style="position: absolute; width: 744px; height: 98px; z-index: 1; left:10px; top:82px" runat="server" id="mainlayer">

<table border="0" width="100%" cellspacing="0" cellpadding="0">
<tr>
<td width="312" valign="top">
<a href="<%# homepictureurl %>"><img border=0 src="<%# featuredbigimg %>"></a><img src="images/welcomeastor.gif" width="340" height="142">






</td>
<td><img border=0 src="images/spacerhome.gif" width="1" height="479"></td>
<td width="2" valign="top">
</td>
<td width="330" valign="top">
<a href="<%# websiteurl %>"><img border=0 src="<%# featuredimg %>"></a><br>
<img border=0 src="images/spacerhorz.gif" width="205" height="1"><br>
<a href="<%# websiteurl3 %>"><img border=0 src="<%# featuredimg3 %>"></a><br>
<img border=0 src="images/spacerhorz.gif" width="205" height="1"><br>
<a href="<%# websiteurl5 %>"><img border=0 src="<%# featuredimg5 %>"></td>
<td valign="top" width="0"><img border=0 src="images/spacer.gif" width="1" height="159"></td>

<td valign="top"><a href="<%# websiteurl2 %>"><img border=0 src="<%# featuredimg2 %>"></a><br>
<img border=0 src="

http://astorclub.com/images/spacerhorz.gif"

width="205" height="1"><br>
<a href="<%# websiteurl4 %>"><img border=0 src="<%# featuredimg4 %>"></a><br>
<img border=0 src="

http://astorclub.com/images/spacerhorz.gif"

width="205" height="1"><br>
<a href="<%# websiteurl6 %>"><img border=0 src="<%# featuredimg6 %>"></td>
</tr>
</table>
</div>
<div border="0" style="position: absolute; width: 338px; height: 22px; z-index: 2; left:24px; top:448px" runat="server" id="insidetext">
<p align="center" class="gbbig"><%# promotionheader %></p>
<p align="left" class="gbbodynormal"><%# promotiondesc %></p>
</div>

</form>
</body>

</html>\

********
THANKS GUYS!!*

 

[ecannizzo]

I think your problem is all your open commands. You only need one datareader and one command since you close the datareader each time.

example:

Dim myCommand As New SqlCommand()
Dim objdr as SQLDataReader

mycommand.CommandText = CommandText
objDR=myCommand.ExecuteReader()
    objdr.read()
    featuredimg = objdr("websitepicture")
    websiteurl = objdr("websiteurl")
    objdr.close()

mycommand.CommandText = CommandText2
objDR=myCommand.ExecuteReader()
    objdr.read()
    featuredimg = objdr("websitepicture")
    websiteurl = objdr("websiteurl")
    objdr.close()

Erica