By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
448,496 Members | 1,266 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 448,496 IT Pros & Developers. It's quick & easy.

ExecuteReader requires an open and available Connection.

P: n/a
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,BusyNAMessag e,PlayResMenu," & _

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

--
Mike Hollibaugh
Sep 12 '06 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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" <Mi**@discussions.microsoft.comwrote in message
news:70**********************************@microsof t.com...
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,BusyNAMessag e,PlayResMenu," & _

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

--
Mike Hollibaugh

Sep 12 '06 #2

P: n/a
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" <Mi**@discussions.microsoft.comwrote in message
news:70**********************************@microsof t.com...
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,BusyNAMessag e,PlayResMenu," & _

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

--
Mike Hollibaugh


Sep 12 '06 #3

P: n/a
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" <Mi**@discussions.microsoft.comwrote in message
news:AF**********************************@microsof t.com...
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" <Mi**@discussions.microsoft.comwrote in message
news:70**********************************@microso ft.com...
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,BusyNAMessag e,PlayResMenu," & _

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

--
Mike Hollibaugh



Sep 12 '06 #4

P: n/a
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" <Mi**@discussions.microsoft.comwrote in message
news:AF**********************************@microsof t.com...
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" <Mi**@discussions.microsoft.comwrote in message
news:70**********************************@microsof t.com...
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,BusyNAMessag e,PlayResMenu," & _

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

--
Mike Hollibaugh


Sep 12 '06 #5

P: n/a
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" <Mi**@discussions.microsoft.comwrote in message
news:81**********************************@microsof t.com...
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" <Mi**@discussions.microsoft.comwrote in message
news:AF**********************************@microso ft.com...
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" <Mi**@discussions.microsoft.comwrote in message
news:70**********************************@microso ft.com...
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,BusyNAMessag e,PlayResMenu," &
_

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

--
Mike Hollibaugh



Sep 12 '06 #6

P: n/a
Thanks for your responses Marina. I will remove the sharred items
and see how that works.
--
Mike Hollibaugh
"Marina Levit [MVP]" wrote:
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" <Mi**@discussions.microsoft.comwrote in message
news:81**********************************@microsof t.com...
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" <Mi**@discussions.microsoft.comwrote in message
news:AF**********************************@microsof t.com...
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" <Mi**@discussions.microsoft.comwrote in message
news:70**********************************@microsof t.com...
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,BusyNAMessag e,PlayResMenu," &
_

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

--
Mike Hollibaugh



Sep 12 '06 #7

This discussion thread is closed

Replies have been disabled for this discussion.