Re: ExecuteReader requires an open and available Connection.

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.

"Mike" <Mike@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:81105769-0A21-40B6-938B-D5D49B6ABA9F@xxxxxxxxxxxxxxxx
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?
--
Mike Hollibaugh


"Marina Levit [MVP]" wrote:

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.

"Mike" <Mike@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:AF29F07E-D5F8-442E-8082-09A0DB01C218@xxxxxxxxxxxxxxxx
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
--
Mike Hollibaugh


"Marina Levit [MVP]" wrote:

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.

"Mike" <Mike@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:7035CE14-4FD0-4F2B-9E33-965512EB886B@xxxxxxxxxxxxxxxx
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)

--
Mike Hollibaugh








.

Relevant Pages

  • Re: ADO connections question
    ... Function CreateADOObjects(ConnectionString as string) ... you'll see this error if the connection has not been ... I have a specific login form that calls the dbLogin function as shown ... Dim strCriteria As String ...
    (microsoft.public.access.adp.sqlserver)
  • Re: ADO connections question
    ... I have a module which creates the ADO connection object to validates the ... Function dbLogin(txtUser As String, txtPword As String, txtServer As String, ... I have a specific login form that calls the dbLogin function as shown below: ... Dim strCriteria As String ...
    (microsoft.public.access.adp.sqlserver)
  • DTS Transformation Data Task Errors
    ... Dim oIniFile As New IniFile ... Dim sServerName As String = oIniFile.GetString("Source Connection", ... ;ServerName* - String value representing the name or ip address of the ...
    (microsoft.public.sqlserver.dts)
  • Re: ExecuteReader requires an open and available Connection.
    ... you have ALL your users sharing one connection. ... Public Shared Function GetServerAs String ... Dim theServer As String ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: ExecuteReader requires an open and available Connection.
    ... Public Shared Function GetServerAs String ... Dim theServer As String ... Dim MyDT As New DataTable ...
    (microsoft.public.dotnet.framework.aspnet)