• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 280
  • Last Modified:

ASP.NET page causes "Timeout expired" SQL error

We're running SQL 2000 and I keep getting this error when I run the ASP.NET page:
"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. "

Here is my .Net code as well.  TIA!

<%@ Page Language="VB" ContentType="text/html" Debug="True" ResponseEncoding="iso-8859-1" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.OleDB" %>
<%@ Import Namespace="System.Data.Odbc" %>
<%@ Import Namespace="System.Data.SqlClient" %>

<script language="vb" runat="server">
      Dim WithEvents Timer As System.Timers.Timer
    Dim ds As New DataSet()
      Dim lastCandName As String
      Dim totalVotes As Integer
      Dim candVotes As Integer
      Dim DistNo As Integer
      Dim TownCD As String
      Dim OfficeDescription As String
      Sub Page_Load()
            If not(IsPostBack) Then
                  DistNo = 1
                  TownCd = "BE"
                  OfficeDescription = "President"
            End If
      End Sub
      Sub LoadTimeoutPeriods()            
            Dim lItem60 As New ListItem("1 Minute",60)
            Dim lItem120 As New ListItem("2 Minutes",120)
            Dim lItem180 As New ListItem("3 Minutes",180)
      End Sub
      Sub LoadDataGrid()
            Dim strConnection As String = (ConfigurationSettings.AppSettings("election_SQL"))
            Dim conn As New SqlConnection(strConnection)
            Dim strSQL As String = "SELECT ORSeq, CRSeq, TownCD, DistNo, SRow, SCol, Votes, OfficeDescription, Candidate, Party, Town " & _
                                             "FROM qryResultsByTownDistrict" & _
                                             " WHERE TownCD='"&(TownCd)&"'" & _
                                             " AND OfficeDescription='"&(OfficeDescription)&"'" & _
                                             " AND DistNo="&(DistNo)&";"
            Dim cmd As New SqlCommand(strSQL, conn)
            cmd.CommandTimeout = 15
                  Dim da As New SqlDataAdapter(cmd)
                  dGrid.DataSource = ds
      End Sub
      Sub GetTotalVotes()
            Dim strConnection As String = (ConfigurationSettings.AppSettings("election_SQL"))
            Dim conn As New SqlConnection(strConnection)
            Dim strSQL As String = "SELECT SumOfVotes, TownCd, OfficeDescription " & _
                                             "FROM qrySumOfVotes" & _
                                             " WHERE TownCD='"&(TownCd)&"'" & _
                                             " AND OfficeDescription='"&(OfficeDescription)&"';"
            Dim cmd As New SqlCommand(strSQL, conn)
            cmd.CommandTimeout = 15
                  Dim rdr As SqlDataReader = cmd.ExecuteReader()
                  totalVotes = rdr(0)
            tsts.text = totalVotes
      End Sub
      Function displayCandidate(candName As String) As String
            If (lastCandName = "") OR (lastCandName <> candName) Then
                  lastCandName = candName
                  Return candName
            End If
      End Function
      Function calcPartyPercent(votes As Integer)
            Dim VotePerc As Single
            If CInt(totalVotes) > 0 Then
                   VotePerc = (votes/totalVotes)
                  Return FormatPercent(VotePerc)
                  Return ""
            End If
      End Function
      Function getCandVotes(cand As String, party As String)
            Dim strConnection As String = (ConfigurationSettings.AppSettings("election_SQL"))
            Dim conn As New SqlConnection(strConnection)
            Dim strSQL As String = "SELECT SumOfVotes, TownCd, OfficeDescription, Candidate, Party " & _
                                             "FROM qrySumOfVotes WHERE TownCd='BE' AND OfficeDescription='President' " & _
                                             "AND Candidate='"&(cand)&"' AND Party='"&(party)&"';"
            Dim cmd As New SqlCommand(strSQL, conn)
            cmd.CommandTimeout = 15
                  Dim rdr As SqlDataReader = cmd.ExecuteReader()
                  Return rdr(0)
      End Function
      Sub AddRefreshHeader()
      End Sub
      Sub ChangeRefreshHeader()
            'Dim tm As String = Request.Form("timeoutPeriod")
      End Sub
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
      <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
      <title>Board of Elections : Results for 2004</title>
      <link href="/includes/css_main.css" rel="stylesheet" type="text/css">
            keys = new Array();
            keys["f116"] = 'f5';
            function myFunc(code) {
              //alert('testing '+code)
            document.onkeydown = function(){
             // Capture and remap F-key
              if(window.event && keys["f"+window.event.keyCode])  {
                  window.event.keyCode = 505;
              if(window.event && window.event.keyCode == 505) {
                  return false;
      <form runat="server">
            <asp:Label ID="pgRefreshTxt" runat="server" Text="Automatic Page Refresh:" CssClass="main_12"/>
            <asp:DropDownList ID="timeoutPeriod" runat="server" CssClass="main_12" AutoPostBack="true" />
                        <asp:TemplateColumn HeaderText="Candidate">
                                    <asp:Label ID="Candidate" runat="server"><%#(displayCandidate(DataBinder.Eval(Container.DataItem, "Candidate")))%></asp:Label>
                        <asp:BoundColumn DataField="Party" HeaderText="Party" ItemStyle-HorizontalAlign="2"></asp:BoundColumn>
                        <asp:BoundColumn DataField="Votes" HeaderText="Party Votes" ItemStyle-HorizontalAlign="2"></asp:BoundColumn>
                        <asp:TemplateColumn HeaderText="Party %">
                                    <asp:Label ID="PartyPercentage" runat="server"><%#(calcPartyPercent(DataBinder.Eval(Container.DataItem, "Votes")))%></asp:Label>
                        <asp:TemplateColumn HeaderText="Candidate Votes">
                                    <asp:Label ID="CandidateVotes" runat="server"><%#(getCandVotes(DataBinder.Eval(Container.DataItem, "Candidate"),DataBinder.Eval(Container.DataItem, "Party")))%></asp:Label>

            <asp:Label runat="server" ID="tsts"></asp:Label>
1 Solution
Maybe the problem is your connection string... Anything in it you may suspect?
pythagurasAuthor Commented:
I figured it out.  I believe it was when I was filling the datagrid, my connection was open.  Then in the datagrid columns I had a few columns that would also open connections before the original connection was closed.  I've since then changed my code around and it works perfectly.  
PAQed with points refunded (500)

Community Support Moderator
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now