ExecuteReader requires an open and available Connection.

[Guest]

We are intermitantly receiving this error on our website.
ExecuteReader requires an open and available Connection. The connection's
current state is connecting. Following is the code from the Load event
of the aspx page. The last line is where the error occurs. Does anyone
have any
ideas what is causing this?

Private Sub Page_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim MyDA As New SqlDataAdapter
Dim MyDT As New DataTable

OpenNCConn()
MyDA = New SqlDataAdapter
MyDT = New DataTable
MyDA.SelectCommand = New SqlCommand("Select ELMAcctEmail from
TFT.dbo.TFTELMConfig NOLOCK where Acct_Nbr = '" & _
Session("Acct_Nbr") & "'", NC_Conn)
MyDA.Fill(MyDT)
If (MyDT.Rows.Count > 0) Then
Session("hasELM") = True
If Not IsBlank(MyDT.Rows(0).Item(0)) Then
Session("ELMAcctEmail") = Trim(MyDT.Rows(0).Item(0))
Else
Session("ELMAcctEmail") = ""
End If
Else
Session("hasELM") = False
End If
NC_Conn.Close()

OpenNCConn()
MyDA = Nothing
MyDT = Nothing
GC.Collect()
MyDA = New SqlDataAdapter
MyDT = New DataTable
MyDA.SelectCommand = New SqlCommand("Select
HoldCalls,DBMonthlyFee,CallStatusMenu,BusyNAMessage,PlayResMenu," & _

"PlayAcctResMenu,RecordAll,Allow_Markets,Reverse,BFIAfterHours,DBExtendedData,IdleAgentHold,SmartAgentRouting
from TFT.dbo.TFTAcctCfg NOLOCK where Acct_Nbr = '" & _
Session("Acct_Nbr") & "'", NC_Conn)
MyDA.SelectCommand.CommandTimeout = 0
MyDA.Fill(MyDT)

 

[Marina Levit [MVP]]

What is NC_Conn, where and how is it declared? You omitted the most
important part.

Also, I am not clear why you are calling GC.Collect. Leave well enough
alone, and let the GC run when it needs to.

 

[Guest]

It is under the app_code directory in a class. Here is the complete code:

Option Strict Off
Option Explicit On
Imports System.Data
Imports System.Data.SqlClient
Imports System.Runtime.InteropServices.Marshal
Namespace NarrowcastChart
Public Class clsNCIncludes
Public Shared HeaderBackColor As Color = Color.FromArgb(170, 170, 170)
Public Shared HeaderForeColor As Color = Color.White
Public Shared AltItemBackColor As Color = Color.White
Public Shared AltItemForeColor As Color = Color.FromArgb(0, 51, 102)
Public Shared ItemBackColor As Color = Color.FromArgb(204, 221, 237)
Public Shared ItemForeColor As Color = Color.FromArgb(0, 51, 102)
Public Shared Traffic_Conn As New SqlConnection
Public Shared NC_Conn As New SqlConnection
'
'Get BFI Server Name from Registry
'
Public Shared Function GetServer() As String
Dim theServer As String
theServer = GetSetting("WebDlls", "Config", "Server", "")
If (theServer = "") Then
SaveSetting("WebDlls", "Config", "Server", "GrayMatter")
theServer = GetSetting("WebDlls", "Config", "Server", "")
End If
GetServer = theServer
End Function
'
'Get Narrowcast Server Name from Registry
'
Public Shared Function GetNCServer() As String
Dim theServer As String
theServer = GetSetting("WebDlls", "Config", "NCServer", "")
If (theServer = "") Then
SaveSetting("WebDlls", "Config", "NCServer", "Lenny")
theServer = GetSetting("WebDlls", "Config", "NCServer", "")
End If
GetNCServer = theServer
End Function
Public Shared Sub OpenConn()
Dim ConnString As String
ConnString = "Integrated security=False;Pooling=False" & _
";Persist Security Info=False" & _
";User ID=sa" & _
";Initial Catalog=Traffic" & _
";Data Source=" & GetServer()
If Not IsNothing(Traffic_Conn) Then
If (Traffic_Conn.State = ConnectionState.Open) Then
Traffic_Conn.Close()
End If
End If
Traffic_Conn = New SqlConnection(ConnString)
Traffic_Conn.Open()
End Sub
Public Shared Sub OpenNCConn()
Dim ConnString As String
ConnString = "Integrated security=False;Pooling=False" & _
";Persist Security Info=False;" & _
"User ID=NCServices;" & _
"Password=narrowcast;" & _
"Connect Timeout=60;" & _
"Initial Catalog=TFT;" & _
"Data Source=" & GetNCServer()
If Not IsNothing(NC_Conn) Then
If (NC_Conn.State = ConnectionState.Open) Then
NC_Conn.Close()
End If
End If
NC_Conn = New SqlConnection(ConnString)
NC_Conn.Open()
End Sub
End Class
End Namespace

 

[Marina Levit [MVP]]

The problem is you are using a Shared SqlConnection. What this means is that
you have ALL your users sharing one connection. However, you are not
synchronizing access to the connection - and multiple users are trying to
use it at once - causing errors.

What you are doing is the the number one no-no when it comes to writing web
based applications that access the database. You do not under any
circumstances create one connection to be used by all users. If you do, you
need to synchronize access to it - but that is going to mean atrocious
performance for your app.

Make sure each user has their own connection. Open it when you need to get
data, get all your data, then close it.

 

[Guest]

Marina, is there a way to do this and leave the code in a class so we don't
have to write the same code over and over again?

 

[Marina Levit [MVP]]

What I described has nothing to do with not having the code in a class. In
fact, in .NET all code is in a class.

Not making something shared has nothing to do with how you architect your
application. Your application should still be architected such that either
via inheritence or composition, database access code is encapsulated in one
area.

But again, this has absolutely nothing to do with whether or not a variable
is declared as Shared, I'm not even sure what you are asking.

 

[Guest]

Thanks for your responses Marina. I will remove the sharred items
and see how that works.