Solved

ASP.NET page causes "Timeout expired" SQL error

Posted on 2004-10-17
4
265 Views
Last Modified: 2012-06-27
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"
            
                  LoadTimeoutPeriods()
                  GetTotalVotes()
                  LoadDataGrid()            
                  AddRefreshHeader()
            Else
                  ChangeRefreshHeader()
            End If
      End Sub
      
      Sub LoadTimeoutPeriods()            
            Dim lItem60 As New ListItem("1 Minute",60)
            timeoutPeriod.Items.Add(lItem60)
            
            Dim lItem120 As New ListItem("2 Minutes",120)
            timeoutPeriod.Items.Add(lItem120)
            
            Dim lItem180 As New ListItem("3 Minutes",180)
            timeoutPeriod.Items.Add(lItem180)
      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
            conn.Open()
                  Dim da As New SqlDataAdapter(cmd)
                  da.Fill(ds)
                  
                  dGrid.DataSource = ds
                  dGrid.DataBind()
            conn.Close()
      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
            conn.Open()
                  Dim rdr As SqlDataReader = cmd.ExecuteReader()
                  rdr.Read()
                  totalVotes = rdr(0)
            conn.Close()
            
            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)
            Else
                  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
            conn.Open()
                  Dim rdr As SqlDataReader = cmd.ExecuteReader()
                  rdr.Read()
                  Return rdr(0)
            conn.Close()
      End Function
      
      Sub AddRefreshHeader()
            'Response.AddHeader("refresh","60")            
      End Sub
      
      Sub ChangeRefreshHeader()
            'Dim tm As String = Request.Form("timeoutPeriod")
            'Response.AddHeader("refresh",tm)
      End Sub
      
</script>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
      <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">
      <script>
            keys = new Array();
            keys["f116"] = 'f5';
            
            saveCode=""
            function myFunc(code) {
              //alert('testing '+code)
            }
            
            document.onkeydown = function(){
             // Capture and remap F-key
              if(window.event && keys["f"+window.event.keyCode])  {
                  saveCode=window.event.keyCode;
                  window.event.keyCode = 505;
              }
              if(window.event && window.event.keyCode == 505) {
                  myFunc(saveCode)
                  return false;
                   }
            }
      </script>
</head>
<body>
      <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:DataGrid
                  runat="server"
                  ID="dGrid"
                  AllowPaging="false"
                  HeaderStyle-BackColor="#cccccc"
                  HeaderStyle-HorizontalAlign="2"
                  HeaderStyle-Font-Bold="true"
                  ItemStyle-HorizontalAlign="1"
                  AlternatingItemStyle-BackColor="#eeeeee"
                  BorderColor="#FFFFFF"
                  CellSpacing="3"
                  CellPadding="2"
                  CssClass="main_12"
                  AutoGenerateColumns="false">
                  <columns>
                        
                        <asp:TemplateColumn HeaderText="Candidate">
                              <ItemTemplate>
                                    <asp:Label ID="Candidate" runat="server"><%#(displayCandidate(DataBinder.Eval(Container.DataItem, "Candidate")))%></asp:Label>
                              </ItemTemplate>
                        </asp:TemplateColumn>
                        <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 %">
                              <ItemTemplate>
                                    <asp:Label ID="PartyPercentage" runat="server"><%#(calcPartyPercent(DataBinder.Eval(Container.DataItem, "Votes")))%></asp:Label>
                              </ItemTemplate>
                        </asp:TemplateColumn>
                        <asp:TemplateColumn HeaderText="Candidate Votes">
                              <ItemTemplate>
                                    <asp:Label ID="CandidateVotes" runat="server"><%#(getCandVotes(DataBinder.Eval(Container.DataItem, "Candidate"),DataBinder.Eval(Container.DataItem, "Party")))%></asp:Label>
                              </ItemTemplate>
                        </asp:TemplateColumn>

                  </columns>
            </asp:DataGrid>
            <asp:Label runat="server" ID="tsts"></asp:Label>
      </form>
</body>
</html>
0
Comment
Question by:pythaguras
4 Comments
 
LVL 6

Expert Comment

by:etmendz
Comment Utility
Maybe the problem is your connection string... Anything in it you may suspect?
0
 

Author Comment

by:pythaguras
Comment Utility
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.  
0
 

Accepted Solution

by:
modulo earned 0 total points
Comment Utility
PAQed with points refunded (500)

modulo
Community Support Moderator
0

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Suggested Solutions

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now