I'm starting to worry a bit now. We're getting the above error when two
users hit the same database/page on an ASP.NET application using ADO.NET,
talking to a SQL 7 server. The error is perfectly repeatable :-( But this
should help!
The error is occurring inside ExecuteReader which uses a DataReader
internally.
Here are some things that I'm pretty sure it's *NOT*:
It's not because our data readers are not being closed - they are. If they
weren't being closed, then the same error would occur in single user
operation. It doesn't - it's only when two users (threads) are running at
once.
It's not connection pooling as we've turned that off using Pooling=False in
the SqlConnection connection string. I've verified it really is off by
watching connections open & close manually in SQL Enterprise manager.
The two threads are not (AFAIK) sharing the same connection object. The
SqlConnection object is not in a shared or static variable - it's stored as
a private variable within one of our classes (called MSSQL) in a variable
called m_Connection. This m_Connection variable is created each time we open
a connection using m_Connection = New SqlConnection(ConnString), opened,
used (ExecuteScalar) closed and then destroyed using m_Connection = nothing.
The instance of MSSQL is stored in the session cache but I've verified that
the two users/threads are indeed using their own instance of MSSQL and
therefore their own instance of m_Connection. The two instances of
m_Connection will have the same connection string (SQL login used, not
Windows authentication).
There are no exceptions/error conditions occuring elsewhere apart from this
final error.
I don't think it's the connection/data reader closing - I've triple checked
all open/close pairs and the close is always within a Try...Catch with the
connection closed in the Finally section.
The error never occurs in our own use of data readers, only from within
ExecuteScalar. Hmm, I guess I could write my own version of ExecuteScaler...
So, pretty flummoxed at the moment. I willing to try any suggestions!
Cheers, Rob.Does it happen in EVERY circumstance? It just sounds like your connected
access is being hit twice simultaneously.
"Rob Nicholson" <informed@.community.nospam> wrote in message
news:uz%23T7dGwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> I'm starting to worry a bit now. We're getting the above error when two
> users hit the same database/page on an ASP.NET application using ADO.NET,
> talking to a SQL 7 server. The error is perfectly repeatable :-( But this
> should help!
> The error is occurring inside ExecuteReader which uses a DataReader
> internally.
> Here are some things that I'm pretty sure it's *NOT*:
> It's not because our data readers are not being closed - they are. If they
> weren't being closed, then the same error would occur in single user
> operation. It doesn't - it's only when two users (threads) are running at
> once.
> It's not connection pooling as we've turned that off using Pooling=False
> in
> the SqlConnection connection string. I've verified it really is off by
> watching connections open & close manually in SQL Enterprise manager.
> The two threads are not (AFAIK) sharing the same connection object. The
> SqlConnection object is not in a shared or static variable - it's stored
> as
> a private variable within one of our classes (called MSSQL) in a variable
> called m_Connection. This m_Connection variable is created each time we
> open
> a connection using m_Connection = New SqlConnection(ConnString), opened,
> used (ExecuteScalar) closed and then destroyed using m_Connection =
> nothing.
> The instance of MSSQL is stored in the session cache but I've verified
> that
> the two users/threads are indeed using their own instance of MSSQL and
> therefore their own instance of m_Connection. The two instances of
> m_Connection will have the same connection string (SQL login used, not
> Windows authentication).
> There are no exceptions/error conditions occuring elsewhere apart from
> this
> final error.
> I don't think it's the connection/data reader closing - I've triple
> checked
> all open/close pairs and the close is always within a Try...Catch with the
> connection closed in the Finally section.
> The error never occurs in our own use of data readers, only from within
> ExecuteScalar. Hmm, I guess I could write my own version of
> ExecuteScaler...
> So, pretty flummoxed at the moment. I willing to try any suggestions!
> Cheers, Rob.
>
Are any of the objects involved being stored in Application variables?
Sincerely,
S. Justin Gengo, MCP
Web Developer / Programmer
www.aboutfortunate.com
"Out of chaos comes order."
Nietzsche
"Rob Nicholson" <informed@.community.nospam> wrote in message
news:uz%23T7dGwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> I'm starting to worry a bit now. We're getting the above error when two
> users hit the same database/page on an ASP.NET application using ADO.NET,
> talking to a SQL 7 server. The error is perfectly repeatable :-( But this
> should help!
> The error is occurring inside ExecuteReader which uses a DataReader
> internally.
> Here are some things that I'm pretty sure it's *NOT*:
> It's not because our data readers are not being closed - they are. If they
> weren't being closed, then the same error would occur in single user
> operation. It doesn't - it's only when two users (threads) are running at
> once.
> It's not connection pooling as we've turned that off using Pooling=False
> in
> the SqlConnection connection string. I've verified it really is off by
> watching connections open & close manually in SQL Enterprise manager.
> The two threads are not (AFAIK) sharing the same connection object. The
> SqlConnection object is not in a shared or static variable - it's stored
> as
> a private variable within one of our classes (called MSSQL) in a variable
> called m_Connection. This m_Connection variable is created each time we
> open
> a connection using m_Connection = New SqlConnection(ConnString), opened,
> used (ExecuteScalar) closed and then destroyed using m_Connection =
> nothing.
> The instance of MSSQL is stored in the session cache but I've verified
> that
> the two users/threads are indeed using their own instance of MSSQL and
> therefore their own instance of m_Connection. The two instances of
> m_Connection will have the same connection string (SQL login used, not
> Windows authentication).
> There are no exceptions/error conditions occuring elsewhere apart from
> this
> final error.
> I don't think it's the connection/data reader closing - I've triple
> checked
> all open/close pairs and the close is always within a Try...Catch with the
> connection closed in the Finally section.
> The error never occurs in our own use of data readers, only from within
> ExecuteScalar. Hmm, I guess I could write my own version of
> ExecuteScaler...
> So, pretty flummoxed at the moment. I willing to try any suggestions!
> Cheers, Rob.
>
sql connections (until yukon), only support one request at a time. ther
error mens a second request was made on the connection without the previous
request reading all the result sets.
ExecuteScaler only reads the first row of the first result set, it does not
clear the results, you still need to close the underlying connection.
also be sure your command objects are not shared - you will get the same
results.
if you are getting this with pooling on, then you are definitely sharing the
data between thread in your code.
note: be sure you are not using fields in a vb module, as these are shared
across threads even if private. public/private just controls varible
accessibilty not sharing.
Public Module Test
private myData as myObj = new myObj ' shared across threads
End Module
-- bruce (sqlwork.com)
"Rob Nicholson" <informed@.community.nospam> wrote in message
news:uz%23T7dGwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> I'm starting to worry a bit now. We're getting the above error when two
> users hit the same database/page on an ASP.NET application using ADO.NET,
> talking to a SQL 7 server. The error is perfectly repeatable :-( But this
> should help!
> The error is occurring inside ExecuteReader which uses a DataReader
> internally.
> Here are some things that I'm pretty sure it's *NOT*:
> It's not because our data readers are not being closed - they are. If they
> weren't being closed, then the same error would occur in single user
> operation. It doesn't - it's only when two users (threads) are running at
> once.
> It's not connection pooling as we've turned that off using Pooling=False
> in
> the SqlConnection connection string. I've verified it really is off by
> watching connections open & close manually in SQL Enterprise manager.
> The two threads are not (AFAIK) sharing the same connection object. The
> SqlConnection object is not in a shared or static variable - it's stored
> as
> a private variable within one of our classes (called MSSQL) in a variable
> called m_Connection. This m_Connection variable is created each time we
> open
> a connection using m_Connection = New SqlConnection(ConnString), opened,
> used (ExecuteScalar) closed and then destroyed using m_Connection =
> nothing.
> The instance of MSSQL is stored in the session cache but I've verified
> that
> the two users/threads are indeed using their own instance of MSSQL and
> therefore their own instance of m_Connection. The two instances of
> m_Connection will have the same connection string (SQL login used, not
> Windows authentication).
> There are no exceptions/error conditions occuring elsewhere apart from
> this
> final error.
> I don't think it's the connection/data reader closing - I've triple
> checked
> all open/close pairs and the close is always within a Try...Catch with the
> connection closed in the Finally section.
> The error never occurs in our own use of data readers, only from within
> ExecuteScalar. Hmm, I guess I could write my own version of
> ExecuteScaler...
> So, pretty flummoxed at the moment. I willing to try any suggestions!
> Cheers, Rob.
>
Bruce is on to something here. If you don't close the DataReader (even with
an ExecuteScalar), the pending rowset will not be flushed until you do (or
close the connection). This can take some time if the query returns a large
rowset or multiple resultsets. If you try to reuse the DataReader before
this is done, you'll get this exception.
hth
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Bruce Barker" <brubar_nospamplease_@.safeco.com> wrote in message
news:u1SomHHwFHA.2232@.TK2MSFTNGP11.phx.gbl...
> sql connections (until yukon), only support one request at a time. ther
> error mens a second request was made on the connection without the
> previous request reading all the result sets.
> ExecuteScaler only reads the first row of the first result set, it does
> not clear the results, you still need to close the underlying connection.
> also be sure your command objects are not shared - you will get the same
> results.
> if you are getting this with pooling on, then you are definitely sharing
> the data between thread in your code.
> note: be sure you are not using fields in a vb module, as these are shared
> across threads even if private. public/private just controls varible
> accessibilty not sharing.
> Public Module Test
> private myData as myObj = new myObj ' shared across threads
> End Module
> -- bruce (sqlwork.com)
>
> "Rob Nicholson" <informed@.community.nospam> wrote in message
> news:uz%23T7dGwFHA.2132@.TK2MSFTNGP15.phx.gbl...
>
> Does it happen in EVERY circumstance? It just sounds like your connected
> access is being hit twice simultaneously.
Yes, pretty much. When the two users hit the "Go" button, it always fails at
the same line.
Cheers, Rob.
> Are any of the objects involved being stored in Application variables?
No, session used throughout.
Cheers, Rob.
> sql connections (until yukon), only support one request at a time. ther
> error mens a second request was made on the connection without the
previous
> request reading all the result sets.
Hi Bruce,
Just to clarify this, this means one "SqlConnection" object can only handle
one request at once. I'm aware of this issue hence the reason I said I'm
pretty sure we're closing the data reader and connections each time (I've
countred the opens and closes).
> ExecuteScaler only reads the first row of the first result set, it does
not
> clear the results, you still need to close the underlying connection.
Yes, we do that.
> also be sure your command objects are not shared - you will get the same
> results.
Yup, not shared - created each time within a non-shared class/object.
> if you are getting this with pooling on, then you are definitely sharing
the
> data between thread in your code.
Nope, occurs with pooling turned off.
> note: be sure you are not using fields in a vb module, as these are shared
> across threads even if private. public/private just controls varible
> accessibilty not sharing.
Not sure what you mean by fields?
> Public Module Test
> private myData as myObj = new myObj ' shared across threads
> End Module
Nope, nothing like this anyway. The only thing we do have is code like this:
Readonly Property SomePointer As SomeObject
Get
Static CachedSomePointer As SomeObject
If CachedSomePointer Is Nothing Then
CachedSomePointer = GetThePointerFromSomwhere()
End If
Return CachedSomePointer
End Get
End Property
Cheers, Rob.
> Bruce is on to something here. If you don't close the DataReader (even
with
> an ExecuteScalar), the pending rowset will not be flushed until you do (or
We do close the data reader I'm pretty sure. The basic problem is I think
the datareader is being closed. If it wasn't then the error would occur when
a single user tried the operation. Therefore, it looks like *somehow* the
two threads are sharing each other's connections. But I'm 99% sure they are
not sharing the same connection object.
Cheers, Rob.
> Does it happen in EVERY circumstance? It just sounds like your connected
> access is being hit twice simultaneously.
I realised that I can set-up a test environment that should repeat this by
simply a loop into a page_load that simply executes 10,000 ExecuteScaler
statements:
For i As Integer = 1 To 10000
Dim NumPeople As Integer =
CIMS_App.DataServer.ExecuteScaler(SQL)
Next
The SQL being executed is Select Count(*) From People. The source for
ExecuteScaler, OpenConnnection and CloseConnection are included below. No
parameters are being passed in this example so that bit can be ignored.
Now this is where something strange happened. I ran the above code from
within VS 2003 and it crashes with "SQL Server does not exist or access
denied" error. I think I need to work out why this is happening first! Okay,
so firing off 10,000 open connections, select and close connection isn't
something that would normally happen but it shouldn't crash...
Later... if the loop is changed to this:
CIMS_App.DataServer.OpenConnection()
For i As Integer = 1 To 10000
Dim NumPeople As Integer =
CIMS_App.DataServer.ExecuteScaler(SQL)
Next
CIMS_App.DataServer.CloseConnection()
The error doesn't occur. It's the rapid opening and closing of the
connection that's crashing - SQL 7 box BTW.
Cheers, Rob.
[SqlException: SQL Server does not exist or access denied.]
System.Data.SqlClient.SqlInternalConnection.OpenAndLogin()
System.Data.SqlClient.SqlInternalConnection..ctor(SqlConnection
connection, SqlConnectionString connectionOptions)
System.Data.SqlClient.SqlConnection.Open()
Granite.MSSQL.OpenConnection() in
C:\Projects\CIMS_Dev\Granite\Databases\M
SSQL.vb:379
[GException: Unable to open connection to INFORMED02]
Granite.MSSQL.OpenConnection() in
C:\Projects\CIMS_Dev\Granite\Databases\M
SSQL.vb:381
Granite.MSSQL.ExecuteScaler(String SQL, SQL_Parameter[] Parameters) in
C:\Projects\CIMS_Dev\Granite\Databases\M
SSQL.vb:501
[GException: Unable to load data from INFORMED02 server.]
Granite.MSSQL.ThrowOpenException(Exception ex, String SQL) in
C:\Projects\CIMS_Dev\Granite\Databases\M
SSQL.vb:451
Granite.MSSQL.ExecuteScaler(String SQL, SQL_Parameter[] Parameters) in
C:\Projects\CIMS_Dev\Granite\Databases\M
SSQL.vb:533
CIMS.DefaultPage.Page_Load(Object sender, EventArgs e) in
c:\inetpub\wwwroot\CIMS_Dev\Default.aspx.vb:78
System.EventHandler.Invoke(Object sender, EventArgs e) +0
System.Web.UI.Control.OnLoad(EventArgs e)
System.Web.UI.Control.LoadRecursive()
System.Web.UI.Page.ProcessRequestMain()
Here's the code:
Public Overrides Function ExecuteScaler(ByVal SQL As String, ByVal
ParamArray Parameters() As SQL_Parameter) As Object
' Trap the database operation.
Dim AlreadyOpen As Boolean
Try
' Open the connection.
AlreadyOpen = OpenConnection()
' Create SQL command.
Dim SQL_Command As New SqlCommand(SQL, m_Connection)
' Add SQL parameters.
For Each SQL_Parameter As SQL_Parameter In Parameters
Dim p As SqlParameter
Dim Value As Object = SQL_Parameter.Value
If Value.GetType Is GetType(System.Byte()) Then
Dim Data() As Byte = Value
p = New SqlParameter(SQL_Parameter.Name,
SqlDbType.Image, Data.Length, ParameterDirection.Input, False, 0, 0,
Nothing, DataRowVersion.Current, Data)
Else
Throw New Exception("MSSQL.ExecuteScalar can't handle "
& Value.GetType.FullName & " data types!")
End If
SQL_Command.Parameters.Add(p)
Next
' Add transaction if enabled.
If Not m_Trans Is Nothing Then
SQL_Command.Transaction = m_Trans
End If
' Execute the command.
ExecuteScaler = SQL_Command.ExecuteScalar
Catch ex As Exception
ThrowOpenException(ex, SQL)
Finally
' Close the connection.
If Not AlreadyOpen Then
CloseConnection()
End If
End Try
End Function
Public Overrides Function OpenConnection() As Boolean
If m_Connection Is Nothing Then
m_Connection = New SqlConnection(ConnectionString)
End If
If m_Connection.State = ConnectionState.Closed Then
Try
m_Connection.Open()
Catch ex As Exception
Throw New GException("Unable to open connection to " &
m_DataLink.ServerName, GraniteErrors.Error0016, ex)
End Try
Else
OpenConnection = True
End If
End Function
Public Overrides Sub CloseConnection()
If Not m_Connection Is Nothing Then
m_Connection.Close()
m_Connection = Nothing
End If
End Sub
Showing posts with label database. Show all posts
Showing posts with label database. Show all posts
Saturday, March 31, 2012
There is already an open DataReader associated with this Connection which must be closed f
I'm starting to worry a bit now. We're getting the above error when two
users hit the same database/page on an ASP.NET application using ADO.NET,
talking to a SQL 7 server. The error is perfectly repeatable :-( But this
should help!
users hit the same database/page on an ASP.NET application using ADO.NET,
talking to a SQL 7 server. The error is perfectly repeatable :-( But this
should help!
The error is occurring inside ExecuteReader which uses a DataReader
internally.
Here are some things that I'm pretty sure it's *NOT*:
It's not because our data readers are not being closed - they are. If they
weren't being closed, then the same error would occur in single user
operation. It doesn't - it's only when two users (threads) are running at
once.
It's not connection pooling as we've turned that off using Pooling=False in
the SqlConnection connection string. I've verified it really is off by
watching connections open & close manually in SQL Enterprise manager.
The two threads are not (AFAIK) sharing the same connection object. The
SqlConnection object is not in a shared or static variable - it's stored as
a private variable within one of our classes (called MSSQL) in a variable
called m_Connection. This m_Connection variable is created each time we open
a connection using m_Connection = New SqlConnection(ConnString), opened,
used (ExecuteScalar) closed and then destroyed using m_Connection = nothing.
The instance of MSSQL is stored in the session cache but I've verified that
the two users/threads are indeed using their own instance of MSSQL and
therefore their own instance of m_Connection. The two instances of
m_Connection will have the same connection string (SQL login used, not
Windows authentication).
There are no exceptions/error conditions occuring elsewhere apart from this
final error.
I don't think it's the connection/data reader closing - I've triple checked
all open/close pairs and the close is always within a Try...Catch with the
connection closed in the Finally section.
The error never occurs in our own use of data readers, only from within
ExecuteScalar. Hmm, I guess I could write my own version of ExecuteScaler...
So, pretty flummoxed at the moment. I willing to try any suggestions!
Cheers, Rob.Does it happen in EVERY circumstance? It just sounds like your connected
access is being hit twice simultaneously.
"Rob Nicholson" <informed@.community.nospam> wrote in message
news:uz%23T7dGwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> I'm starting to worry a bit now. We're getting the above error when two
> users hit the same database/page on an ASP.NET application using ADO.NET,
> talking to a SQL 7 server. The error is perfectly repeatable :-( But this
> should help!
> The error is occurring inside ExecuteReader which uses a DataReader
> internally.
> Here are some things that I'm pretty sure it's *NOT*:
> It's not because our data readers are not being closed - they are. If they
> weren't being closed, then the same error would occur in single user
> operation. It doesn't - it's only when two users (threads) are running at
> once.
> It's not connection pooling as we've turned that off using Pooling=False
> in
> the SqlConnection connection string. I've verified it really is off by
> watching connections open & close manually in SQL Enterprise manager.
> The two threads are not (AFAIK) sharing the same connection object. The
> SqlConnection object is not in a shared or static variable - it's stored
> as
> a private variable within one of our classes (called MSSQL) in a variable
> called m_Connection. This m_Connection variable is created each time we
> open
> a connection using m_Connection = New SqlConnection(ConnString), opened,
> used (ExecuteScalar) closed and then destroyed using m_Connection =
> nothing.
> The instance of MSSQL is stored in the session cache but I've verified
> that
> the two users/threads are indeed using their own instance of MSSQL and
> therefore their own instance of m_Connection. The two instances of
> m_Connection will have the same connection string (SQL login used, not
> Windows authentication).
> There are no exceptions/error conditions occuring elsewhere apart from
> this
> final error.
> I don't think it's the connection/data reader closing - I've triple
> checked
> all open/close pairs and the close is always within a Try...Catch with the
> connection closed in the Finally section.
> The error never occurs in our own use of data readers, only from within
> ExecuteScalar. Hmm, I guess I could write my own version of
> ExecuteScaler...
> So, pretty flummoxed at the moment. I willing to try any suggestions!
> Cheers, Rob.
Are any of the objects involved being stored in Application variables?
--
Sincerely,
S. Justin Gengo, MCP
Web Developer / Programmer
www.aboutfortunate.com
"Out of chaos comes order."
Nietzsche
"Rob Nicholson" <informed@.community.nospam> wrote in message
news:uz%23T7dGwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> I'm starting to worry a bit now. We're getting the above error when two
> users hit the same database/page on an ASP.NET application using ADO.NET,
> talking to a SQL 7 server. The error is perfectly repeatable :-( But this
> should help!
> The error is occurring inside ExecuteReader which uses a DataReader
> internally.
> Here are some things that I'm pretty sure it's *NOT*:
> It's not because our data readers are not being closed - they are. If they
> weren't being closed, then the same error would occur in single user
> operation. It doesn't - it's only when two users (threads) are running at
> once.
> It's not connection pooling as we've turned that off using Pooling=False
> in
> the SqlConnection connection string. I've verified it really is off by
> watching connections open & close manually in SQL Enterprise manager.
> The two threads are not (AFAIK) sharing the same connection object. The
> SqlConnection object is not in a shared or static variable - it's stored
> as
> a private variable within one of our classes (called MSSQL) in a variable
> called m_Connection. This m_Connection variable is created each time we
> open
> a connection using m_Connection = New SqlConnection(ConnString), opened,
> used (ExecuteScalar) closed and then destroyed using m_Connection =
> nothing.
> The instance of MSSQL is stored in the session cache but I've verified
> that
> the two users/threads are indeed using their own instance of MSSQL and
> therefore their own instance of m_Connection. The two instances of
> m_Connection will have the same connection string (SQL login used, not
> Windows authentication).
> There are no exceptions/error conditions occuring elsewhere apart from
> this
> final error.
> I don't think it's the connection/data reader closing - I've triple
> checked
> all open/close pairs and the close is always within a Try...Catch with the
> connection closed in the Finally section.
> The error never occurs in our own use of data readers, only from within
> ExecuteScalar. Hmm, I guess I could write my own version of
> ExecuteScaler...
> So, pretty flummoxed at the moment. I willing to try any suggestions!
> Cheers, Rob.
sql connections (until yukon), only support one request at a time. ther
error mens a second request was made on the connection without the previous
request reading all the result sets.
ExecuteScaler only reads the first row of the first result set, it does not
clear the results, you still need to close the underlying connection.
also be sure your command objects are not shared - you will get the same
results.
if you are getting this with pooling on, then you are definitely sharing the
data between thread in your code.
note: be sure you are not using fields in a vb module, as these are shared
across threads even if private. public/private just controls varible
accessibilty not sharing.
Public Module Test
private myData as myObj = new myObj ' shared across threads
End Module
-- bruce (sqlwork.com)
"Rob Nicholson" <informed@.community.nospam> wrote in message
news:uz%23T7dGwFHA.2132@.TK2MSFTNGP15.phx.gbl...
> I'm starting to worry a bit now. We're getting the above error when two
> users hit the same database/page on an ASP.NET application using ADO.NET,
> talking to a SQL 7 server. The error is perfectly repeatable :-( But this
> should help!
> The error is occurring inside ExecuteReader which uses a DataReader
> internally.
> Here are some things that I'm pretty sure it's *NOT*:
> It's not because our data readers are not being closed - they are. If they
> weren't being closed, then the same error would occur in single user
> operation. It doesn't - it's only when two users (threads) are running at
> once.
> It's not connection pooling as we've turned that off using Pooling=False
> in
> the SqlConnection connection string. I've verified it really is off by
> watching connections open & close manually in SQL Enterprise manager.
> The two threads are not (AFAIK) sharing the same connection object. The
> SqlConnection object is not in a shared or static variable - it's stored
> as
> a private variable within one of our classes (called MSSQL) in a variable
> called m_Connection. This m_Connection variable is created each time we
> open
> a connection using m_Connection = New SqlConnection(ConnString), opened,
> used (ExecuteScalar) closed and then destroyed using m_Connection =
> nothing.
> The instance of MSSQL is stored in the session cache but I've verified
> that
> the two users/threads are indeed using their own instance of MSSQL and
> therefore their own instance of m_Connection. The two instances of
> m_Connection will have the same connection string (SQL login used, not
> Windows authentication).
> There are no exceptions/error conditions occuring elsewhere apart from
> this
> final error.
> I don't think it's the connection/data reader closing - I've triple
> checked
> all open/close pairs and the close is always within a Try...Catch with the
> connection closed in the Finally section.
> The error never occurs in our own use of data readers, only from within
> ExecuteScalar. Hmm, I guess I could write my own version of
> ExecuteScaler...
> So, pretty flummoxed at the moment. I willing to try any suggestions!
> Cheers, Rob.
Bruce is on to something here. If you don't close the DataReader (even with
an ExecuteScalar), the pending rowset will not be flushed until you do (or
close the connection). This can take some time if the query returns a large
rowset or multiple resultsets. If you try to reuse the DataReader before
this is done, you'll get this exception.
hth
--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
www.sqlreportingservices.net
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________
"Bruce Barker" <brubar_nospamplease_@.safeco.com> wrote in message
news:u1SomHHwFHA.2232@.TK2MSFTNGP11.phx.gbl...
> sql connections (until yukon), only support one request at a time. ther
> error mens a second request was made on the connection without the
> previous request reading all the result sets.
> ExecuteScaler only reads the first row of the first result set, it does
> not clear the results, you still need to close the underlying connection.
> also be sure your command objects are not shared - you will get the same
> results.
> if you are getting this with pooling on, then you are definitely sharing
> the data between thread in your code.
> note: be sure you are not using fields in a vb module, as these are shared
> across threads even if private. public/private just controls varible
> accessibilty not sharing.
> Public Module Test
> private myData as myObj = new myObj ' shared across threads
> End Module
> -- bruce (sqlwork.com)
>
> "Rob Nicholson" <informed@.community.nospam> wrote in message
> news:uz%23T7dGwFHA.2132@.TK2MSFTNGP15.phx.gbl...
>> I'm starting to worry a bit now. We're getting the above error when two
>> users hit the same database/page on an ASP.NET application using ADO.NET,
>> talking to a SQL 7 server. The error is perfectly repeatable :-( But this
>> should help!
>>
>> The error is occurring inside ExecuteReader which uses a DataReader
>> internally.
>>
>> Here are some things that I'm pretty sure it's *NOT*:
>>
>> It's not because our data readers are not being closed - they are. If
>> they
>> weren't being closed, then the same error would occur in single user
>> operation. It doesn't - it's only when two users (threads) are running at
>> once.
>>
>> It's not connection pooling as we've turned that off using Pooling=False
>> in
>> the SqlConnection connection string. I've verified it really is off by
>> watching connections open & close manually in SQL Enterprise manager.
>>
>> The two threads are not (AFAIK) sharing the same connection object. The
>> SqlConnection object is not in a shared or static variable - it's stored
>> as
>> a private variable within one of our classes (called MSSQL) in a variable
>> called m_Connection. This m_Connection variable is created each time we
>> open
>> a connection using m_Connection = New SqlConnection(ConnString), opened,
>> used (ExecuteScalar) closed and then destroyed using m_Connection =
>> nothing.
>> The instance of MSSQL is stored in the session cache but I've verified
>> that
>> the two users/threads are indeed using their own instance of MSSQL and
>> therefore their own instance of m_Connection. The two instances of
>> m_Connection will have the same connection string (SQL login used, not
>> Windows authentication).
>>
>> There are no exceptions/error conditions occuring elsewhere apart from
>> this
>> final error.
>>
>> I don't think it's the connection/data reader closing - I've triple
>> checked
>> all open/close pairs and the close is always within a Try...Catch with
>> the
>> connection closed in the Finally section.
>>
>> The error never occurs in our own use of data readers, only from within
>> ExecuteScalar. Hmm, I guess I could write my own version of
>> ExecuteScaler...
>>
>> So, pretty flummoxed at the moment. I willing to try any suggestions!
>>
>> Cheers, Rob.
>>
>>
> Does it happen in EVERY circumstance? It just sounds like your connected
> access is being hit twice simultaneously.
Yes, pretty much. When the two users hit the "Go" button, it always fails at
the same line.
Cheers, Rob.
> Are any of the objects involved being stored in Application variables?
No, session used throughout.
Cheers, Rob.
> sql connections (until yukon), only support one request at a time. ther
> error mens a second request was made on the connection without the
previous
> request reading all the result sets.
Hi Bruce,
Just to clarify this, this means one "SqlConnection" object can only handle
one request at once. I'm aware of this issue hence the reason I said I'm
pretty sure we're closing the data reader and connections each time (I've
countred the opens and closes).
> ExecuteScaler only reads the first row of the first result set, it does
not
> clear the results, you still need to close the underlying connection.
Yes, we do that.
> also be sure your command objects are not shared - you will get the same
> results.
Yup, not shared - created each time within a non-shared class/object.
> if you are getting this with pooling on, then you are definitely sharing
the
> data between thread in your code.
Nope, occurs with pooling turned off.
> note: be sure you are not using fields in a vb module, as these are shared
> across threads even if private. public/private just controls varible
> accessibilty not sharing.
Not sure what you mean by fields?
> Public Module Test
> private myData as myObj = new myObj ' shared across threads
> End Module
Nope, nothing like this anyway. The only thing we do have is code like this:
Readonly Property SomePointer As SomeObject
Get
Static CachedSomePointer As SomeObject
If CachedSomePointer Is Nothing Then
CachedSomePointer = GetThePointerFromSomwhere()
End If
Return CachedSomePointer
End Get
End Property
Cheers, Rob.
> Bruce is on to something here. If you don't close the DataReader (even
with
> an ExecuteScalar), the pending rowset will not be flushed until you do (or
We do close the data reader I'm pretty sure. The basic problem is I think
the datareader is being closed. If it wasn't then the error would occur when
a single user tried the operation. Therefore, it looks like *somehow* the
two threads are sharing each other's connections. But I'm 99% sure they are
not sharing the same connection object.
Cheers, Rob.
> Does it happen in EVERY circumstance? It just sounds like your connected
> access is being hit twice simultaneously.
I realised that I can set-up a test environment that should repeat this by
simply a loop into a page_load that simply executes 10,000 ExecuteScaler
statements:
For i As Integer = 1 To 10000
Dim NumPeople As Integer =
CIMS_App.DataServer.ExecuteScaler(SQL)
Next
The SQL being executed is Select Count(*) From People. The source for
ExecuteScaler, OpenConnnection and CloseConnection are included below. No
parameters are being passed in this example so that bit can be ignored.
Now this is where something strange happened. I ran the above code from
within VS 2003 and it crashes with "SQL Server does not exist or access
denied" error. I think I need to work out why this is happening first! Okay,
so firing off 10,000 open connections, select and close connection isn't
something that would normally happen but it shouldn't crash...
Later... if the loop is changed to this:
CIMS_App.DataServer.OpenConnection()
For i As Integer = 1 To 10000
Dim NumPeople As Integer =
CIMS_App.DataServer.ExecuteScaler(SQL)
Next
CIMS_App.DataServer.CloseConnection()
The error doesn't occur. It's the rapid opening and closing of the
connection that's crashing - SQL 7 box BTW.
Cheers, Rob.
[SqlException: SQL Server does not exist or access denied.]
System.Data.SqlClient.SqlInternalConnection.OpenAn dLogin()
System.Data.SqlClient.SqlInternalConnection..ctor( SqlConnection
connection, SqlConnectionString connectionOptions)
System.Data.SqlClient.SqlConnection.Open()
Granite.MSSQL.OpenConnection() in
C:\Projects\CIMS_Dev\Granite\Databases\MSSQL.vb:37 9
[GException: Unable to open connection to INFORMED02]
Granite.MSSQL.OpenConnection() in
C:\Projects\CIMS_Dev\Granite\Databases\MSSQL.vb:38 1
Granite.MSSQL.ExecuteScaler(String SQL, SQL_Parameter[] Parameters) in
C:\Projects\CIMS_Dev\Granite\Databases\MSSQL.vb:50 1
[GException: Unable to load data from INFORMED02 server.]
Granite.MSSQL.ThrowOpenException(Exception ex, String SQL) in
C:\Projects\CIMS_Dev\Granite\Databases\MSSQL.vb:45 1
Granite.MSSQL.ExecuteScaler(String SQL, SQL_Parameter[] Parameters) in
C:\Projects\CIMS_Dev\Granite\Databases\MSSQL.vb:53 3
CIMS.DefaultPage.Page_Load(Object sender, EventArgs e) in
c:\inetpub\wwwroot\CIMS_Dev\Default.aspx.vb:78
System.EventHandler.Invoke(Object sender, EventArgs e) +0
System.Web.UI.Control.OnLoad(EventArgs e)
System.Web.UI.Control.LoadRecursive()
System.Web.UI.Page.ProcessRequestMain()
Here's the code:
Public Overrides Function ExecuteScaler(ByVal SQL As String, ByVal
ParamArray Parameters() As SQL_Parameter) As Object
' Trap the database operation.
Dim AlreadyOpen As Boolean
Try
' Open the connection.
AlreadyOpen = OpenConnection()
' Create SQL command.
Dim SQL_Command As New SqlCommand(SQL, m_Connection)
' Add SQL parameters.
For Each SQL_Parameter As SQL_Parameter In Parameters
Dim p As SqlParameter
Dim Value As Object = SQL_Parameter.Value
If Value.GetType Is GetType(System.Byte()) Then
Dim Data() As Byte = Value
p = New SqlParameter(SQL_Parameter.Name,
SqlDbType.Image, Data.Length, ParameterDirection.Input, False, 0, 0,
Nothing, DataRowVersion.Current, Data)
Else
Throw New Exception("MSSQL.ExecuteScalar can't handle "
& Value.GetType.FullName & " data types!")
End If
SQL_Command.Parameters.Add(p)
Next
' Add transaction if enabled.
If Not m_Trans Is Nothing Then
SQL_Command.Transaction = m_Trans
End If
' Execute the command.
ExecuteScaler = SQL_Command.ExecuteScalar
Catch ex As Exception
ThrowOpenException(ex, SQL)
Finally
' Close the connection.
If Not AlreadyOpen Then
CloseConnection()
End If
End Try
End Function
Public Overrides Function OpenConnection() As Boolean
If m_Connection Is Nothing Then
m_Connection = New SqlConnection(ConnectionString)
End If
If m_Connection.State = ConnectionState.Closed Then
Try
m_Connection.Open()
Catch ex As Exception
Throw New GException("Unable to open connection to " &
m_DataLink.ServerName, GraniteErrors.Error0016, ex)
End Try
Else
OpenConnection = True
End If
End Function
Public Overrides Sub CloseConnection()
If Not m_Connection Is Nothing Then
m_Connection.Close()
m_Connection = Nothing
End If
End Sub
> The error doesn't occur. It's the rapid opening and closing of the
> connection that's crashing - SQL 7 box BTW.
Later...
It manages to open and close about 3,800 times before crashing out :-)
Cheers, Rob.
> within VS 2003 and it crashes with "SQL Server does not exist or access
> denied" error. I think I need to work out why this is happening first!
Okay,
Later...
Okay, we can ignore this error - see following KB article:
http://support.microsoft.com/default.aspx/kb/328476
Basically, if you don't use pooling (which is turned off at the moment for
debug), then opening and closing ~4,000 connections rapidly (sockets) causes
SQL Server to fall over. This is exactly what's happening. As I said,
normally you wouldn't do something like this but I'm trying to diagnose the
main problem with datareader.
Cheers, Rob.
Rob,
If you're caching connection objects that could be the same as placing the
object into an application level variable. Try creating new objects one at a
time instead of caching them and then testing. I believe that eventually one
changing one of the cached objects to a new one will solve the problem.
--
Sincerely,
S. Justin Gengo, MCP
Web Developer / Programmer
www.aboutfortunate.com
"Out of chaos comes order."
Nietzsche
"Rob Nicholson" <informed@.community.nospam> wrote in message
news:OWFc5HPwFHA.1256@.TK2MSFTNGP09.phx.gbl...
>> sql connections (until yukon), only support one request at a time. ther
>> error mens a second request was made on the connection without the
> previous
>> request reading all the result sets.
> Hi Bruce,
> Just to clarify this, this means one "SqlConnection" object can only
> handle
> one request at once. I'm aware of this issue hence the reason I said I'm
> pretty sure we're closing the data reader and connections each time (I've
> countred the opens and closes).
>> ExecuteScaler only reads the first row of the first result set, it does
> not
>> clear the results, you still need to close the underlying connection.
> Yes, we do that.
>> also be sure your command objects are not shared - you will get the same
>> results.
> Yup, not shared - created each time within a non-shared class/object.
>> if you are getting this with pooling on, then you are definitely sharing
> the
>> data between thread in your code.
> Nope, occurs with pooling turned off.
>> note: be sure you are not using fields in a vb module, as these are
>> shared
>> across threads even if private. public/private just controls varible
>> accessibilty not sharing.
> Not sure what you mean by fields?
>> Public Module Test
>> private myData as myObj = new myObj ' shared across threads
>> End Module
> Nope, nothing like this anyway. The only thing we do have is code like
> this:
> Readonly Property SomePointer As SomeObject
> Get
> Static CachedSomePointer As SomeObject
> If CachedSomePointer Is Nothing Then
> CachedSomePointer = GetThePointerFromSomwhere()
> End If
> Return CachedSomePointer
> End Get
> End Property
> Cheers, Rob.
> If you're caching connection objects that could be the same as placing the
> object into an application level variable. Try creating new objects one at
a
I'm beginning to suspect the fault lies in another of our developers code as
in the tests I mention above, I did this:
Begin
Open connection
Fire off 10,000 ExecuteScaler operations
Close connection
End
I then ran the above on four PCs at once, all executing the above code at
the same time on the same web server and that works flawlessly. This suggest
that they are not using the same connection object.
Actually, the current fault does occur in the same programmers code
everytime...
Cheers, Rob.
> I'm beginning to suspect the fault lies in another of our developers code
as
> in the tests I mention above, I did this:
This bug hunt is like a detective story :-) My suspicion that the problem
does like with the two threads attempting to use the same variables when
they shouldn't be...
The MSSQL object has a variable called m_Connection which holds the
SqlConnection object. It's created like this in OpenConnection:
If m_Connection Is Nothing Then
m_Connection = New SqlConnection(ConnectionString)
End If
When the program is run with one user, m_Connection is always nothing and
therefore creates the connection everytime.
However, when the program is run with two users, m_Connection sometimes
isn't always nothing so a simple trap like this fires:
If Not m_Connection Is Nothing Then Stop
This is a the cause of the error - the two threads ARE trying to re-use same
connection object hence the OpenReader problem.
The big question is WHY this is happening as it shouldn't... The
m_Connection object is in effect stored in the session cache. Each user
*should* have their own session cache and therefore own m_Connection.
Later :-)
Cheers, Rob.
> also be sure your command objects are not shared - you will get the same
> results.
> if you are getting this with pooling on, then you are definitely sharing
the
FOUND IT!! One of the other developers on the team had used a Private Shared
variable in a class which is a BIG no-no in ASP.NET land or in any
multi-threaded application. Therefore, User A and User B were sharing the
same connection variable - well actually they were sharing a higher level
object holding the connection so net result is the same.
Heads will roll I've spent most of my Saturday trying to track this down :-(
Thanks all for the suggestions.
Cheers, Rob.
There is no position at row 0
Hi:
You are using an own Class called Transactions here. What do the methods from that Class used here look like?
The class is to long to post here for you to look at. Can I email it to you?
Firstly, sharpening up your communication skills would be a great help. How do you expect us to know what is going on if you don't give us the appropriate information? For example, what is the error that you talked about in your first post!?!
I am getting this error when a transaction needs to write to the Database after the user clicks the submit button.
Any help on what is the cause is greatly appreciated.
Thanks in advance for your help.It's impossible to answer this without seeing some code and also the eventual stored procedure you are using to execute the transaction.
************* Edited by moderator Adec ***************
Inserted missing < code></ code> tags. Always include such
tags when including code in your postings. Many readers
disregard postings without the code tags.
**************************************************
Thanks. Here is the situation. I am updating a site which was given to me, and fairly new to .net. I am competent to decifer the situation with the help by a person who knows.
Now correct me if I am wrong. I see that it is going to write the transaction to a table called 'Transactions'. I'm looking at the table in the DB and the fields do not look like the fields it is looking for below. Now I am assuming that I should change the fields in this code to match exactly what is in the table. Also the fields in the table do not have the letters in front of the field name. Ex: iUserID, dtDateTime. It has UserID, DateTime.. etc. what are the letters in front for? Also the last line of this code, should there be a table called PublicPayInvoice created in the DB, because I don't see it. Not sure if that is the table it is looking for.
Thanks
The Code:
'Write the Transaction
If sX_Response_Code = "1" Then
Transactions.WriteTransaction(iUSerID, dtDateTime, iAccountID, sTransactionType, sMethod, sRecipientEmail, _
sx_Email, "YES", "NO", sX_Response_Code, sX_Response_Subcode, _
sX_Response_Reason_Code, sX_Response_Reason_Text, _
sX_Auth_Code, sX_AVS_Code, sX_Trans_ID, sX_Description, dProcessing, _
dTransactionFee, dChargeBackFee, dRefundFee, dPremiereFee, dRequestedAmount, _
dX_Amount, sIP, "PP")
Else
If sX_Auth_Code = "" Then
sX_Auth_Code = "0"
End IfTransactions.WriteTransaction(iUSerID, dtDateTime, iAccountID, sTransactionType, sMethod, sRecipientEmail, _
sx_Email, "NO", "YES", sX_Response_Code, sX_Response_Subcode, _
sX_Response_Reason_Code, sX_Response_Reason_Text, _
sX_Auth_Code, sX_AVS_Code, sX_Trans_ID, sX_Description, dProcessing, _
dTransactionFee, dChargeBackFee, dRefundFee, dPremiereFee, dRequestedAmount, _
dX_Amount, sIP, "PP")
End If
'Get the TransactionID
iTransactionID = Transactions.GetPublicTransactionID(iUSerID, sRecipientEmail, sx_Email)Dim sTo As String
Dim sFrom As String
Dim sSubject As String
Dim sBody As String
Dim mail As New MailMessage()
Dim iTemp As IntegerIf sX_Response_Code = 1 Then
Transactions.PublicPayInvoice(iUSerID, sCustomerInvoivceNum, iTransactionID, sTransactionType, sMethod, _
Left(Session.Contents.Item("x_Card_Num"), 4) & "-XXXX-XXXX-" _
& Right(Session.Contents.Item("x_Card_Num"), 4), sExpDate, _
Session.Contents.Item("x_Card_Code"), Session.Contents.Item(" _
"x_bank_Name"), Left(Session.Contents.Item("x_Bank_Acct_Num"), 2) _
& "XXXXXXXXXXXXXX" & Right(Session.Contents.Item("x_Bank_Acct_Num"), 4), _
Session.Contents.Item("x_Bank_ABA_Code"), sFirstName & " " & sLastName, _
sAddress, sCity, sState, sZip, sCountry, sCardType)
You are using an own Class called Transactions here. What do the methods from that Class used here look like?
The class is to long to post here for you to look at. Can I email it to you?
Firstly, sharpening up your communication skills would be a great help. How do you expect us to know what is going on if you don't give us the appropriate information? For example, what is the error that you talked about in your first post!?!
Secondly, the additional "letters in front of the field name" are simply a naming convention and relate to the ID of the Controls/Varaibles that you are using.
Thirdly (to reiterate Andre's point), Transaction is actually an Object (an instantiated Class) and isn't a table in the database.
Finally, PublicPayInvoice is a method of the Invoice Object, not another table.
> I am competent to decifer the situation with the help by a person who knows
Evidently not!!
WoW. Thanks for being so CRUEL!
I was just thinking the same about2flip thing after reading the post from stevenbey.
Its amazing why some tech's get a bad wrap for having god-like complex's, lol. Dealing with people (or answering forums) this guy should not!!!
>>Dealing with people (or answering forums) this guy should not!!!
There are many who would disagree (i.e. those people who's problems I have solved).
Wednesday, March 28, 2012
This connections gonna kill me
This is a long one. (Question that is) an I have been working on this problem for a week now and still have no idea. There are no systems/database people here to help.
I have used the following string to succesfully connect to my SQL Server
database :
"Provider=SQLOLEDB.1;
Password=mypassword;
Persist Security Info=True;
User ID=VSdotNET;
Initial Catalog=ITPSCustomers;
Data Source=DELL\VSdotNET"
The problem is that I do not want SQL Server authentication so I opened up
SQL Server, removed the login VBdotNET, added it again and changed the user
authentication to Windows. I then changed the connection string to :
"Provider=SQLOLEDB.1;
Integrated Security=SSPI;
Persist Security Info=False;
Initial Catalog=ITPSCustomers;
Data Source=DELL\VSdotNET"
DELL\VSdotNET is a Login and has been given permission to access the
database ITPSCustomers.
Within the Database node for ITPSCustomers database DELL\VSdotNET has been
added as a user and has been given all permisions to the tables and views
within the database.
When I now run my web page I get the errors :
System.Data.OleDb.OleDbException: Cannot open database requested in login
'ITPSCustomers'. Login fails.
at System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr)
at System.Data.OleDb.OleDbConnection.InitializeProvider()
at System.Data.OleDb.OleDbConnection.Open()
at DreamweaverCtrls.DataSet.DoInit() System.Exception:
The DefaultView was requested but no tables yet exist.
at DreamweaverCtrls.DataSet.get_DefaultView()
Can anyone help ?two things:
You should be using the SqlClient Namespace for all things related to data since you're using SQL Server (it's much faster)
Try this connection when using a SqlConnection
"Data Source=DELL\VSdotNET;
Trusted_Connection=True;
Initial Catalog=ITPSCustomers"
Hope that helps :)
Well the question is when you switched to windows authentication did you add the user unders security=>users in enterprise manager. Under the general tab beside name you get a browse button to pick a windows login to add and then you specify what database it can access under the database access tab. The SqlClient namespace is better for sql server (or msde) but it is unlikely to be the cause of the problem.
I have used the following string to succesfully connect to my SQL Server
database :
"Provider=SQLOLEDB.1;
Password=mypassword;
Persist Security Info=True;
User ID=VSdotNET;
Initial Catalog=ITPSCustomers;
Data Source=DELL\VSdotNET"
The problem is that I do not want SQL Server authentication so I opened up
SQL Server, removed the login VBdotNET, added it again and changed the user
authentication to Windows. I then changed the connection string to :
"Provider=SQLOLEDB.1;
Integrated Security=SSPI;
Persist Security Info=False;
Initial Catalog=ITPSCustomers;
Data Source=DELL\VSdotNET"
DELL\VSdotNET is a Login and has been given permission to access the
database ITPSCustomers.
Within the Database node for ITPSCustomers database DELL\VSdotNET has been
added as a user and has been given all permisions to the tables and views
within the database.
When I now run my web page I get the errors :
System.Data.OleDb.OleDbException: Cannot open database requested in login
'ITPSCustomers'. Login fails.
at System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr)
at System.Data.OleDb.OleDbConnection.InitializeProvider()
at System.Data.OleDb.OleDbConnection.Open()
at DreamweaverCtrls.DataSet.DoInit() System.Exception:
The DefaultView was requested but no tables yet exist.
at DreamweaverCtrls.DataSet.get_DefaultView()
Can anyone help ?two things:
You should be using the SqlClient Namespace for all things related to data since you're using SQL Server (it's much faster)
Try this connection when using a SqlConnection
"Data Source=DELL\VSdotNET;
Trusted_Connection=True;
Initial Catalog=ITPSCustomers"
Hope that helps :)
Well the question is when you switched to windows authentication did you add the user unders security=>users in enterprise manager. Under the general tab beside name you get a browse button to pick a windows login to add and then you specify what database it can access under the database access tab. The SqlClient namespace is better for sql server (or msde) but it is unlikely to be the cause of the problem.
This Dataset is going to be the death of me! Please Help
I am trying to update a database table through this dataset. The error is
ambigous: "System.NullReferenceException: Object reference not set to an
instance of an object." I have tried replacing the variable "month" with a
string and I tried replacing "ProductList.ToString()" with a string but I
still get the error:
ambigous: "System.NullReferenceException: Object reference not set to an
instance of an object." I have tried replacing the variable "month" with a
string and I tried replacing "ProductList.ToString()" with a string but I
still get the error:
Me.sqlDataAdapter1.Fill(DataSet1, "Queue")
Dim QueueRow As DataRow
QueueRow = DataSet1.Tables("Queue").Rows.Find(myCustomerID)
>>ERROR HERE<< QueueRow(month) = ProductList.ToString()
Me.sqlDataAdapter1.Update(DataSet1, "Queue")
Please Help!
Thanks, Justin.Justin wrote:
> (abridged) I have tried replacing the variable "month" with a
> string and I tried replacing "ProductList.ToString()" with a string but I
> still get the error:
What is the value of the month variable? It looks as if you're trying to
change the value of the "month" column in the DataRow. If so you must
place quotes around the word "month". Like this:
QueueRow("month") = ProductList.ToString()
Anders Nor?s
http://dotnetjunkies.com/weblog/anoras
month is a string variable representing a table column to save the data to, I
have tried replacing this with a static column string but I still get the
same error.
"Anders Nor?s" wrote:
> Justin wrote:
> > (abridged) I have tried replacing the variable "month" with a
> > string and I tried replacing "ProductList.ToString()" with a string but I
> > still get the error:
> What is the value of the month variable? It looks as if you're trying to
> change the value of the "month" column in the DataRow. If so you must
> place quotes around the word "month". Like this:
> QueueRow("month") = ProductList.ToString()
> Anders Nor?s
> http://dotnetjunkies.com/weblog/anoras
Justin wrote:
> month is a string variable representing a table column to save the data to, I
> have tried replacing this with a static column string but I still get the
> same error.
Ok, then I suspect that the statement
DataSet1.Tables("Queue").Rows.Find(myCustomerID) evaluates to null. If
this is the case, the ensuing statement will cause an
NullReferenceException. You can place an assertion in your code to
display a message if the QueueRow is null.
The following statement check whether QueueRow is null and outputs a
message if it is:
Debug.Assert( Not (QueueRow Is Nothing), "QueueRow parameter is null",
"Can't get object for null type")
Anders Nor?s
http://dotnetjunkies.com/weblog/anoras/
This Dataset is going to be the death of me! Please Help
I am trying to update a database table through this dataset. The error is
ambigous: "System.NullReferenceException: Object reference not set to an
instance of an object." I have tried replacing the variable "month" with a
string and I tried replacing "ProductList.ToString()" with a string but I
still get the error:
Me.sqlDataAdapter1.Fill(DataSet1, "Queue")
Dim QueueRow As DataRow
QueueRow = DataSet1.Tables("Queue").Rows.Find(myCustomerID)
Me.sqlDataAdapter1.Update(DataSet1, "Queue")
Please Help!
Thanks, Justin.Justin wrote:
> (abridged) I have tried replacing the variable "month" with a
> string and I tried replacing "ProductList.ToString()" with a string but I
> still get the error:
What is the value of the month variable? It looks as if you're trying to
change the value of the "month" column in the DataRow. If so you must
place quotes around the word "month". Like this:
QueueRow("month") = ProductList.ToString()
Anders Nor?s
http://dotnetjunkies.com/weblog/anoras
month is a string variable representing a table column to save the data to,
I
have tried replacing this with a static column string but I still get the
same error.
"Anders Nor?s" wrote:
> Justin wrote:
> What is the value of the month variable? It looks as if you're trying to
> change the value of the "month" column in the DataRow. If so you must
> place quotes around the word "month". Like this:
> QueueRow("month") = ProductList.ToString()
> Anders Nor?s
> http://dotnetjunkies.com/weblog/anoras
>
Justin wrote:
> month is a string variable representing a table column to save the data to
, I
> have tried replacing this with a static column string but I still get the
> same error.
Ok, then I suspect that the statement
DataSet1.Tables("Queue").Rows.Find(myCustomerID) evaluates to null. If
this is the case, the ensuing statement will cause an
NullReferenceException. You can place an assertion in your code to
display a message if the QueueRow is null.
The following statement check whether QueueRow is null and outputs a
message if it is:
Debug.Assert( Not (QueueRow Is Nothing), "QueueRow parameter is null",
"Can't get object for null type")
Anders Nor?s
http://dotnetjunkies.com/weblog/anoras/
ambigous: "System.NullReferenceException: Object reference not set to an
instance of an object." I have tried replacing the variable "month" with a
string and I tried replacing "ProductList.ToString()" with a string but I
still get the error:
Me.sqlDataAdapter1.Fill(DataSet1, "Queue")
Dim QueueRow As DataRow
QueueRow = DataSet1.Tables("Queue").Rows.Find(myCustomerID)
Me.sqlDataAdapter1.Update(DataSet1, "Queue")
Please Help!
Thanks, Justin.Justin wrote:
> (abridged) I have tried replacing the variable "month" with a
> string and I tried replacing "ProductList.ToString()" with a string but I
> still get the error:
What is the value of the month variable? It looks as if you're trying to
change the value of the "month" column in the DataRow. If so you must
place quotes around the word "month". Like this:
QueueRow("month") = ProductList.ToString()
Anders Nor?s
http://dotnetjunkies.com/weblog/anoras
month is a string variable representing a table column to save the data to,
I
have tried replacing this with a static column string but I still get the
same error.
"Anders Nor?s" wrote:
> Justin wrote:
> What is the value of the month variable? It looks as if you're trying to
> change the value of the "month" column in the DataRow. If so you must
> place quotes around the word "month". Like this:
> QueueRow("month") = ProductList.ToString()
> Anders Nor?s
> http://dotnetjunkies.com/weblog/anoras
>
Justin wrote:
> month is a string variable representing a table column to save the data to
, I
> have tried replacing this with a static column string but I still get the
> same error.
Ok, then I suspect that the statement
DataSet1.Tables("Queue").Rows.Find(myCustomerID) evaluates to null. If
this is the case, the ensuing statement will cause an
NullReferenceException. You can place an assertion in your code to
display a message if the QueueRow is null.
The following statement check whether QueueRow is null and outputs a
message if it is:
Debug.Assert( Not (QueueRow Is Nothing), "QueueRow parameter is null",
"Can't get object for null type")
Anders Nor?s
http://dotnetjunkies.com/weblog/anoras/
Monday, March 26, 2012
This is driving me nuts
Ok, the weblog is still giving some problems. My database table fields are set to not allow nulls but I'm stil able to insert data into the fields. Now everything works fine on my computer but craps up when I upload the project.
I need some help.I think I'm going to just host my weblog on my on computer at home. EhostingBiz.com, is just not doing it for me. I create my tables and set the columns to not null, but they still allow null values. Everything works on my computer, so I know its not my code. I'm going to bed now.
Have you tried another host - a free one just to compare whether its your current.
No, I have not tried another host. Thats gonna take too much time.
All I wanna know is. How is it possible to insert null values in columns that are set to not allow null values.
Its not possible - except you can add checks which only apply to new data ignoring previous rows but cannot recall if this can apply to nullability!!!
I can't believe this can happen against the way SQL Server works.
Can you post you tabel schema?
I would bet that it might be a database setting that is doing this for you. I would go to the database forum and ask why this isn't working like you want it.
I always verify the data I am throwing at the database anyway, I can usually head off reads and writes that won't work based on the rules I have set. This first acts like a filter, and also as a performance enhancement because you don't have to needlessly access the database unless you are 95% sure the data is good.
If I have time today, I will check into whether or not I can do this on my DB.
Also, it would be helpful if you generated a setup script of you database and posted it here. We might be able to find a problem in there.
Originally posted by hellswraith
I would bet that it might be a database setting that is doing this for you. I would go to the database forum and ask why this isn't working like you want it.
I always verify the data I am throwing at the database anyway, I can usually head off reads and writes that won't work based on the rules I have set. This first acts like a filter, and also as a performance enhancement because you don't have to needlessly access the database unless you are 95% sure the data is good.
If I have time today, I will check into whether or not I can do this on my DB.
Also, it would be helpful if you generated a setup script of you database and posted it here. We might be able to find a problem in there.
I think thats what is happening. Another problem I have is that my RequiredValidator controls wont fire when I upload my project. As I've said before everything works fine on my computer, so this is kinda strange. To force the RequiredValidators to fire, I had to put my code in an if(Page.IsValid) block. I did'nt really want to do that since the page is posting back.
Here is the script.
If you select NO to allow nulls then you will have to supply data to be inserted into the field when a record is inserted. If you have no data to populate the field then sometimes an empty string will do.
But the easiest way I have found is to supply a default value for the field. For string fields and empty string will do. For numerics a zero. For date fields you can use GetDate() as the default or supply a non-sense date like '01-01-1900'.
And a null is a null is nothing...
Should'nt the DB throw an exception if the value is null? If I enter data from enterprise manager and leave a column empty, an exception is thrown.
OK, I am not sure what columns your having problems with. Here is what I see from the scripts...
URL and Email in the comments table are set to ALLOW null values. If these are the columns that are giving you trouble, then you need to switch them to Not Null.
Another thing I have found with ASP.Net is that a empty text box will return a empty string instead of a null value if there is nothing in it. You may be allowed to enter the data because the empty string is being passed therefore it isn't really a null value.
Originally posted by hellswraith
OK, I am not sure what columns your having problems with. Here is what I see from the scripts...
URL and Email in the comments table are set to ALLOW null values. If these are the columns that are giving you trouble, then you need to switch them to Not Null.
Another thing I have found with ASP.Net is that a empty text box will return a empty string instead of a null value if there is nothing in it. You may be allowed to enter the data because the empty string is being passed therefore it isn't really a null value.
The url and email columns are suppose to be like that. I'll have to check out what you said about the empty text box. Thanks for help.
I need some help.I think I'm going to just host my weblog on my on computer at home. EhostingBiz.com, is just not doing it for me. I create my tables and set the columns to not null, but they still allow null values. Everything works on my computer, so I know its not my code. I'm going to bed now.
Have you tried another host - a free one just to compare whether its your current.
No, I have not tried another host. Thats gonna take too much time.
All I wanna know is. How is it possible to insert null values in columns that are set to not allow null values.
Its not possible - except you can add checks which only apply to new data ignoring previous rows but cannot recall if this can apply to nullability!!!
I can't believe this can happen against the way SQL Server works.
Can you post you tabel schema?
I would bet that it might be a database setting that is doing this for you. I would go to the database forum and ask why this isn't working like you want it.
I always verify the data I am throwing at the database anyway, I can usually head off reads and writes that won't work based on the rules I have set. This first acts like a filter, and also as a performance enhancement because you don't have to needlessly access the database unless you are 95% sure the data is good.
If I have time today, I will check into whether or not I can do this on my DB.
Also, it would be helpful if you generated a setup script of you database and posted it here. We might be able to find a problem in there.
Originally posted by hellswraith
I would bet that it might be a database setting that is doing this for you. I would go to the database forum and ask why this isn't working like you want it.
I always verify the data I am throwing at the database anyway, I can usually head off reads and writes that won't work based on the rules I have set. This first acts like a filter, and also as a performance enhancement because you don't have to needlessly access the database unless you are 95% sure the data is good.
If I have time today, I will check into whether or not I can do this on my DB.
Also, it would be helpful if you generated a setup script of you database and posted it here. We might be able to find a problem in there.
I think thats what is happening. Another problem I have is that my RequiredValidator controls wont fire when I upload my project. As I've said before everything works fine on my computer, so this is kinda strange. To force the RequiredValidators to fire, I had to put my code in an if(Page.IsValid) block. I did'nt really want to do that since the page is posting back.
Here is the script.
If you select NO to allow nulls then you will have to supply data to be inserted into the field when a record is inserted. If you have no data to populate the field then sometimes an empty string will do.
But the easiest way I have found is to supply a default value for the field. For string fields and empty string will do. For numerics a zero. For date fields you can use GetDate() as the default or supply a non-sense date like '01-01-1900'.
And a null is a null is nothing...
Should'nt the DB throw an exception if the value is null? If I enter data from enterprise manager and leave a column empty, an exception is thrown.
OK, I am not sure what columns your having problems with. Here is what I see from the scripts...
URL and Email in the comments table are set to ALLOW null values. If these are the columns that are giving you trouble, then you need to switch them to Not Null.
Another thing I have found with ASP.Net is that a empty text box will return a empty string instead of a null value if there is nothing in it. You may be allowed to enter the data because the empty string is being passed therefore it isn't really a null value.
Originally posted by hellswraith
OK, I am not sure what columns your having problems with. Here is what I see from the scripts...
URL and Email in the comments table are set to ALLOW null values. If these are the columns that are giving you trouble, then you need to switch them to Not Null.
Another thing I have found with ASP.Net is that a empty text box will return a empty string instead of a null value if there is nothing in it. You may be allowed to enter the data because the empty string is being passed therefore it isn't really a null value.
The url and email columns are suppose to be like that. I'll have to check out what you said about the empty text box. Thanks for help.
Saturday, March 24, 2012
This should be easy, but...
I have a simple framed site with a Banner, Contents, & Main frames. The
contents page is an .aspx page that queries an MS Access database and dumps
its results into varios Dropdownlists and 1 Listbox. The users would select
from the list then click a button and the Table of Contents is updated
(works perfectly). The problem is that I can not get it to call a page to
the "main" frame. Redirect as I understand it will not redirect to a target
frame. Does anyone know the solution or work around. Keep in mind that it
is vital to refresh the TOC then call the "main" frame page.Thanks anyways, but I found the solution. Inserting javascript into vb
script.
contents page is an .aspx page that queries an MS Access database and dumps
its results into varios Dropdownlists and 1 Listbox. The users would select
from the list then click a button and the Table of Contents is updated
(works perfectly). The problem is that I can not get it to call a page to
the "main" frame. Redirect as I understand it will not redirect to a target
frame. Does anyone know the solution or work around. Keep in mind that it
is vital to refresh the TOC then call the "main" frame page.Thanks anyways, but I found the solution. Inserting javascript into vb
script.
"Mark Sandfox" <NoSpam@.NoSpam.com> wrote in message
news:09gKd.24076$by5.2210@.newssvr19.news.prodigy.c om...
>I have a simple framed site with a Banner, Contents, & Main frames. The
>contents page is an .aspx page that queries an MS Access database and dumps
>its results into varios Dropdownlists and 1 Listbox. The users would
>select from the list then click a button and the Table of Contents is
>updated (works perfectly). The problem is that I can not get it to call a
>page to the "main" frame. Redirect as I understand it will not redirect to
>a target frame. Does anyone know the solution or work around. Keep in
>mind that it is vital to refresh the TOC then call the "main" frame page.
This should be easy, but...
I have a simple framed site with a Banner, Contents, & Main frames. The
contents page is an .aspx page that queries an MS Access database and dumps
its results into varios Dropdownlists and 1 Listbox. The users would select
from the list then click a button and the Table of Contents is updated
(works perfectly). The problem is that I can not get it to call a page to
the "main" frame. Redirect as I understand it will not redirect to a target
frame. Does anyone know the solution or work around. Keep in mind that it
is vital to refresh the TOC then call the "main" frame page.Thanks anyways, but I found the solution. Inserting javascript into vb
script.
"Mark Sandfox" <NoSpam@.NoSpam.com> wrote in message
news:09gKd.24076$by5.2210@.newssvr19.news.prodigy.com...
>I have a simple framed site with a Banner, Contents, & Main frames. The
>contents page is an .aspx page that queries an MS Access database and dumps
>its results into varios Dropdownlists and 1 Listbox. The users would
>select from the list then click a button and the Table of Contents is
>updated (works perfectly). The problem is that I can not get it to call a
>page to the "main" frame. Redirect as I understand it will not redirect to
>a target frame. Does anyone know the solution or work around. Keep in
>mind that it is vital to refresh the TOC then call the "main" frame page.
>
contents page is an .aspx page that queries an MS Access database and dumps
its results into varios Dropdownlists and 1 Listbox. The users would select
from the list then click a button and the Table of Contents is updated
(works perfectly). The problem is that I can not get it to call a page to
the "main" frame. Redirect as I understand it will not redirect to a target
frame. Does anyone know the solution or work around. Keep in mind that it
is vital to refresh the TOC then call the "main" frame page.Thanks anyways, but I found the solution. Inserting javascript into vb
script.
"Mark Sandfox" <NoSpam@.NoSpam.com> wrote in message
news:09gKd.24076$by5.2210@.newssvr19.news.prodigy.com...
>I have a simple framed site with a Banner, Contents, & Main frames. The
>contents page is an .aspx page that queries an MS Access database and dumps
>its results into varios Dropdownlists and 1 Listbox. The users would
>select from the list then click a button and the Table of Contents is
>updated (works perfectly). The problem is that I can not get it to call a
>page to the "main" frame. Redirect as I understand it will not redirect to
>a target frame. Does anyone know the solution or work around. Keep in
>mind that it is vital to refresh the TOC then call the "main" frame page.
>
This should be easy
Hi
Use a literal control.
Use a literal control.
>
>
MyLiteral.Text = "<h1>" + MyVariable + "</h1>";
>
<h1><%# variable %></h1>
Cheers Chris it's good to know there are different ways to do this. I
haven't tried those out yet because I used a literal as suggested by Ray and
it worked a treat (cheers Ray) but I will definately try your methods on the
next one.
Glad it helped! :)
>
>
>
I want to take a variable I have read from a database and place it in
<h1></h1tags on the web page. What's the best/easiest way to do this?
I don't want to use an asp:label because this is rendered as a <span>.
Thanks in advance
JohnJohn Haycock wrote:
Quote:
Originally Posted by
Hi
>
I want to take a variable I have read from a database and place it in
<h1></h1tags on the web page. What's the best/easiest way to do this?
>
I don't want to use an asp:label because this is rendered as a <span>.
>
Thanks in advance
>
John
>
>
>
Use a literal control.
MyLiteral.Text = "<h1>" + MyVariable + "</h1>";
Thank you Ray
I will read up on literal controls
John
Quote:
Originally Posted by
Quote:
Originally Posted by
>>
Use a literal control.
>
>
MyLiteral.Text = "<h1>" + MyVariable + "</h1>";
>
<h1><%# variable %></h1>
You can also use
<h1 id="hgcHeader" runat="server" />
and then in your code-behind, set the text as follows:
hgcHeader.InnerText = variable;
--
Christopher A. Reed
"The oxen are slow, but the earth is patient."
"John Haycock" <jhayc@.blueyonder.co.ukwrote in message
news:1Jovg.2800$q97.1399@.fe3.news.blueyonder.co.uk ...
Quote:
Originally Posted by
Hi
>
I want to take a variable I have read from a database and place it in
<h1></h1tags on the web page. What's the best/easiest way to do this?
>
I don't want to use an asp:label because this is rendered as a <span>.
>
Thanks in advance
>
John
>
>
Cheers Chris it's good to know there are different ways to do this. I
haven't tried those out yet because I used a literal as suggested by Ray and
it worked a treat (cheers Ray) but I will definately try your methods on the
next one.
Many thanks
John
"Christopher Reed" <carttu@.nospam.nospamwrote in message
news:uUxCGqyqGHA.4424@.TK2MSFTNGP05.phx.gbl...
Quote:
Originally Posted by
<h1><%# variable %></h1>
>
You can also use
>
<h1 id="hgcHeader" runat="server" />
>
and then in your code-behind, set the text as follows:
>
hgcHeader.InnerText = variable;
>
--
Christopher A. Reed
"The oxen are slow, but the earth is patient."
>
Glad it helped! :)
John Haycock wrote:
Quote:
Originally Posted by
Cheers Chris it's good to know there are different ways to do this. I
haven't tried those out yet because I used a literal as suggested by Ray and
it worked a treat (cheers Ray) but I will definately try your methods on the
next one.
>
Many thanks
>
John
"Christopher Reed" <carttu@.nospam.nospamwrote in message
news:uUxCGqyqGHA.4424@.TK2MSFTNGP05.phx.gbl...
Quote:
Originally Posted by
><h1><%# variable %></h1>
>>
>You can also use
>>
><h1 id="hgcHeader" runat="server" />
>>
>and then in your code-behind, set the text as follows:
>>
>hgcHeader.InnerText = variable;
>>
>--
>Christopher A. Reed
>"The oxen are slow, but the earth is patient."
>>
>
>
>
This shouldn't be this hard
Hello,
I need to connect a treeview control to and Access database.
It seems that I can only use a site map or a XML file. I cannot
figure out why there is not a choice for any other databases.
I was thinking about using ADO.net code but I wanted to find out if
there is a better way. I am using Visual Web Developer 2008.
Thanks for any help or examples.
TyOn Mar 5, 5:08=A0pm, Ty <tbar...@.lewistownhospital.org> wrote:
> Hello,
> =A0 I need to connect a treeview control to and Access database.
> =A0 It seems that I can only use a site map or a XML file. I cannot
> figure out why there is not a choice for any other databases.
> =A0 I was thinking about using ADO.net code but I wanted to find out if
> there is a better way. I am using Visual Web Developer 2008.
> Thanks for any help or examples.
> Ty
http://forums.asp.net/t/339295.aspx
I need to connect a treeview control to and Access database.
It seems that I can only use a site map or a XML file. I cannot
figure out why there is not a choice for any other databases.
I was thinking about using ADO.net code but I wanted to find out if
there is a better way. I am using Visual Web Developer 2008.
Thanks for any help or examples.
TyOn Mar 5, 5:08=A0pm, Ty <tbar...@.lewistownhospital.org> wrote:
> Hello,
> =A0 I need to connect a treeview control to and Access database.
> =A0 It seems that I can only use a site map or a XML file. I cannot
> figure out why there is not a choice for any other databases.
> =A0 I was thinking about using ADO.net code but I wanted to find out if
> there is a better way. I am using Visual Web Developer 2008.
> Thanks for any help or examples.
> Ty
http://forums.asp.net/t/339295.aspx
this.Connection.Close(); does not close the Oracle session! Pleasehelp!
Hi all!
Did you close the reader?
you will need to turn connection pooling off (see your connect string
properties). with pooling on, connection.close only returns the connection to
the pool, it does not close it. you may make your dba happy by lowing the
pool timeout.
On Dec 6, 3:13 pm, bruce barker
<brucebar...@.discussions.microsoft.comwrote:
>
>
>
>
>
>
>
>
>
>
- Show quoted text -
I don't actually CLOSE the reader but I DO Dispose the reader:
reader.Dispose();
- Show quoted text -
>
I don't actually CLOSE the reader but I DO Dispose the reader:
reader.Dispose();
>
Doesn't that close the reader as well?
>
Also, how do you turn off the connection pooling in the connection
string? All I have is the basic connection string.
>
Thanks for your quick replies.
>
Steve
>
OracleConnection is in the System.Data.OracleClient namespace.
>
>
>
>
>
>
>
>
>
>
- Show quoted text -
I'm writing an ASP.NET web application that uses an Oracle database.
I OPEN the Oracle connection by using the following code:
if (this.ConnectionString != "")
{
this.Connection = new OracleConnection(this.ConnectionString);
this.Connection.Open();
return;
}
I then create the command object by using the following:
OracleCommand DBCmd = new
OracleCommand("PRL_STORE_PAYMENT_REQ_PKG.GET_PAYMENT_REQ_LOOKUP",
objConnect.Connection);
DBCmd.CommandType = System.Data.CommandType.StoredProcedure;
I then I run the OracleCommand.ExecuteReader method to execute the
stored procedure.
I then CLOSE the connection by using the
this.Connection.Close(); command.
However, the sessions stay active!!!
I have a very angry DBA on my tail about this one!
Please help!
Thanks
SteveI'm pretty sure "OracleConnection" is not a standard Framework class. You'll
probably have better luck asking the component vendor (or in their forums).
My guess is that it has to do with connection pooling. Do the number of
connections grow endlessly, or are they reused over and over? If it's the
later, then you should actually have a very happy DBA.
Scott
"S_K" <steve_kershaw@.yahoo.comwrote in message
news:3619d1f2-ef79-41cb-a0af-5df58dd223a0@.e10g2000prf.googlegroups.com...
Quote:
Originally Posted by
Hi all!
>
I'm writing an ASP.NET web application that uses an Oracle database.
>
I OPEN the Oracle connection by using the following code:
>
if (this.ConnectionString != "")
{
this.Connection = new OracleConnection(this.ConnectionString);
this.Connection.Open();
return;
}
>
I then create the command object by using the following:
>
OracleCommand DBCmd = new
OracleCommand("PRL_STORE_PAYMENT_REQ_PKG.GET_PAYMENT_REQ_LOOKUP",
objConnect.Connection);
DBCmd.CommandType = System.Data.CommandType.StoredProcedure;
>
I then I run the OracleCommand.ExecuteReader method to execute the
stored procedure.
>
I then CLOSE the connection by using the
this.Connection.Close(); command.
>
However, the sessions stay active!!!
I have a very angry DBA on my tail about this one!
Please help!
>
Thanks
Steve
Did you close the reader?
if(null!=reader)
{
reader.Close();
}
?
A reader is a live firehouse...thus you use it, then you tidy up.
You can't get reader, close the connection , then use the reader. That
doesn't make sense.
Use the reader as quickly as possible, then close it.
"S_K" <steve_kershaw@.yahoo.comwrote in message
news:3619d1f2-ef79-41cb-a0af-5df58dd223a0@.e10g2000prf.googlegroups.com...
Quote:
Originally Posted by
Hi all!
>
I'm writing an ASP.NET web application that uses an Oracle database.
>
I OPEN the Oracle connection by using the following code:
>
if (this.ConnectionString != "")
{
this.Connection = new OracleConnection(this.ConnectionString);
this.Connection.Open();
return;
}
>
I then create the command object by using the following:
>
OracleCommand DBCmd = new
OracleCommand("PRL_STORE_PAYMENT_REQ_PKG.GET_PAYMENT_REQ_LOOKUP",
objConnect.Connection);
DBCmd.CommandType = System.Data.CommandType.StoredProcedure;
>
I then I run the OracleCommand.ExecuteReader method to execute the
stored procedure.
>
I then CLOSE the connection by using the
this.Connection.Close(); command.
>
However, the sessions stay active!!!
I have a very angry DBA on my tail about this one!
Please help!
>
Thanks
Steve
you will need to turn connection pooling off (see your connect string
properties). with pooling on, connection.close only returns the connection to
the pool, it does not close it. you may make your dba happy by lowing the
pool timeout.
-- bruce (sqlwork.com)
"S_K" wrote:
Quote:
Originally Posted by
Hi all!
>
I'm writing an ASP.NET web application that uses an Oracle database.
>
I OPEN the Oracle connection by using the following code:
>
if (this.ConnectionString != "")
{
this.Connection = new OracleConnection(this.ConnectionString);
this.Connection.Open();
return;
}
>
I then create the command object by using the following:
>
OracleCommand DBCmd = new
OracleCommand("PRL_STORE_PAYMENT_REQ_PKG.GET_PAYMENT_REQ_LOOKUP",
objConnect.Connection);
DBCmd.CommandType = System.Data.CommandType.StoredProcedure;
>
I then I run the OracleCommand.ExecuteReader method to execute the
stored procedure.
>
I then CLOSE the connection by using the
this.Connection.Close(); command.
>
However, the sessions stay active!!!
I have a very angry DBA on my tail about this one!
Please help!
>
Thanks
Steve
>
On Dec 6, 3:13 pm, bruce barker
<brucebar...@.discussions.microsoft.comwrote:
Quote:
Originally Posted by
you will need to turn connection pooling off (see your connect string
properties). with pooling on, connection.close only returns the connection to
the pool, it does not close it. you may make your dba happy by lowing the
pool timeout.
>
-- bruce (sqlwork.com)
>
>
>
"S_K" wrote:
Quote:
Originally Posted by
Hi all!
>
Quote:
Originally Posted by
I'm writing an ASP.NET web application that uses an Oracle database.
>
Quote:
Originally Posted by
I OPEN the Oracle connection by using the following code:
>
Quote:
Originally Posted by
if (this.ConnectionString != "")
{
this.Connection = new OracleConnection(this.ConnectionString);
this.Connection.Open();
return;
}
>
Quote:
Originally Posted by
I then create the command object by using the following:
>
Quote:
Originally Posted by
OracleCommand DBCmd = new
OracleCommand("PRL_STORE_PAYMENT_REQ_PKG.GET_PAYMENT_REQ_LOOKUP",
objConnect.Connection);
DBCmd.CommandType = System.Data.CommandType.StoredProcedure;
>
Quote:
Originally Posted by
I then I run the OracleCommand.ExecuteReader method to execute the
stored procedure.
>
Quote:
Originally Posted by
I then CLOSE the connection by using the
this.Connection.Close(); command.
>
Quote:
Originally Posted by
However, the sessions stay active!!!
I have a very angry DBA on my tail about this one!
Please help!
>
Quote:
Originally Posted by
Thanks
Steve- Hide quoted text -
>
- Show quoted text -
I don't actually CLOSE the reader but I DO Dispose the reader:
reader.Dispose();
Doesn't that close the reader as well?
Also, how do you turn off the connection pooling in the connection
string? All I have is the basic connection string.
Thanks for your quick replies.
Steve
no, dispose will return it to the pool if pooling is enabled (all dispose
does is call close if needed).
you will need the connection string settings docs for the driver you are
using..
-- bruce (sqlwork.com)
"S_K" wrote:
Quote:
Originally Posted by
On Dec 6, 3:13 pm, bruce barker
<brucebar...@.discussions.microsoft.comwrote:
Quote:
Originally Posted by
you will need to turn connection pooling off (see your connect string
properties). with pooling on, connection.close only returns the connection to
the pool, it does not close it. you may make your dba happy by lowing the
pool timeout.
-- bruce (sqlwork.com)
"S_K" wrote:
Quote:
Originally Posted by
Hi all!
Quote:
Originally Posted by
I'm writing an ASP.NET web application that uses an Oracle database.
Quote:
Originally Posted by
I OPEN the Oracle connection by using the following code:
Quote:
Originally Posted by
if (this.ConnectionString != "")
{
this.Connection = new OracleConnection(this.ConnectionString);
this.Connection.Open();
return;
}
Quote:
Originally Posted by
I then create the command object by using the following:
Quote:
Originally Posted by
OracleCommand DBCmd = new
OracleCommand("PRL_STORE_PAYMENT_REQ_PKG.GET_PAYMENT_REQ_LOOKUP",
objConnect.Connection);
DBCmd.CommandType = System.Data.CommandType.StoredProcedure;
Quote:
Originally Posted by
I then I run the OracleCommand.ExecuteReader method to execute the
stored procedure.
Quote:
Originally Posted by
I then CLOSE the connection by using the
this.Connection.Close(); command.
Quote:
Originally Posted by
However, the sessions stay active!!!
I have a very angry DBA on my tail about this one!
Please help!
Quote:
Originally Posted by
Thanks
Steve- Hide quoted text -
- Show quoted text -
>
I don't actually CLOSE the reader but I DO Dispose the reader:
reader.Dispose();
>
Doesn't that close the reader as well?
>
Also, how do you turn off the connection pooling in the connection
string? All I have is the basic connection string.
>
Thanks for your quick replies.
>
Steve
>
OracleConnection is in the System.Data.OracleClient namespace.
Although Oracle also provides such a class, in their
Oracle.DataAccess.Client namespace.
If the connection pool advice doesn't work out, try also calling
Dispose on the connection. Unless the issue has been resolved in a
recent version, there's a flaw in the Framework class that
necessitates this. And it can't hurt in any event.
On Dec 6, 4:52 pm, "Scott Roberts" <srobe...@.no.spam.here-webworks-
software.comwrote:
Quote:
Originally Posted by
I'm pretty sure "OracleConnection" is not a standard Framework class. You'll
probably have better luck asking the component vendor (or in their forums).
My guess is that it has to do with connection pooling. Do the number of
connections grow endlessly, or are they reused over and over? If it's the
later, then you should actually have a very happy DBA.
>
Scott
>
"S_K" <steve_kers...@.yahoo.comwrote in message
>
news:3619d1f2-ef79-41cb-a0af-5df58dd223a0@.e10g2000prf.googlegroups.com...
>
>
>
Quote:
Originally Posted by
Hi all!
>
Quote:
Originally Posted by
I'm writing an ASP.NET web application that uses an Oracle database.
>
Quote:
Originally Posted by
I OPEN the Oracle connection by using the following code:
>
Quote:
Originally Posted by
if (this.ConnectionString != "")
{
this.Connection = new OracleConnection(this.ConnectionString);
this.Connection.Open();
return;
}
>
Quote:
Originally Posted by
I then create the command object by using the following:
>
Quote:
Originally Posted by
OracleCommand DBCmd = new
OracleCommand("PRL_STORE_PAYMENT_REQ_PKG.GET_PAYMENT_REQ_LOOKUP",
objConnect.Connection);
DBCmd.CommandType = System.Data.CommandType.StoredProcedure;
>
Quote:
Originally Posted by
I then I run the OracleCommand.ExecuteReader method to execute the
stored procedure.
>
Quote:
Originally Posted by
I then CLOSE the connection by using the
this.Connection.Close(); command.
>
Quote:
Originally Posted by
However, the sessions stay active!!!
I have a very angry DBA on my tail about this one!
Please help!
>
Quote:
Originally Posted by
Thanks
Steve- Hide quoted text -
>
- Show quoted text -
this.Connection.Close(); does not close the Oracle session! Please
Hi all!
I'm writing an ASP.NET web application that uses an Oracle database.
I OPEN the Oracle connection by using the following code:
if (this.ConnectionString != "")
{
this.Connection = new OracleConnection(this.ConnectionString);
this.Connection.Open();
return;
}
I then create the command object by using the following:
OracleCommand DBCmd = new
OracleCommand("PRL_STORE_PAYMENT_REQ_PKG.GET_PAYMENT_REQ_LOOKUP",
objConnect.Connection);
DBCmd.CommandType = System.Data.CommandType.StoredProcedure;
I then I run the OracleCommand.ExecuteReader method to execute the
stored procedure.
I then CLOSE the connection by using the
this.Connection.Close(); command.
However, the sessions stay active!!!
I have a very
DBA on my tail about this one!
Please help!
Thanks
Steveyou will need to turn connection pooling off (see your connect string
properties). with pooling on, connection.close only returns the connection t
o
the pool, it does not close it. you may make your dba happy by lowing the
pool timeout.
-- bruce (sqlwork.com)
"S_K" wrote:
> Hi all!
> I'm writing an ASP.NET web application that uses an Oracle database.
> I OPEN the Oracle connection by using the following code:
> if (this.ConnectionString != "")
> {
> this.Connection = new OracleConnection(this.ConnectionString);
> this.Connection.Open();
> return;
> }
> I then create the command object by using the following:
> OracleCommand DBCmd = new
> OracleCommand("PRL_STORE_PAYMENT_REQ_PKG.GET_PAYMENT_REQ_LOOKUP",
> objConnect.Connection);
> DBCmd.CommandType = System.Data.CommandType.StoredProcedure;
> I then I run the OracleCommand.ExecuteReader method to execute the
> stored procedure.
> I then CLOSE the connection by using the
> this.Connection.Close(); command.
> However, the sessions stay active!!!
> I have a very DBA on my tail about this one!
> Please help!
> Thanks
> Steve
>
I'm writing an ASP.NET web application that uses an Oracle database.
I OPEN the Oracle connection by using the following code:
if (this.ConnectionString != "")
{
this.Connection = new OracleConnection(this.ConnectionString);
this.Connection.Open();
return;
}
I then create the command object by using the following:
OracleCommand DBCmd = new
OracleCommand("PRL_STORE_PAYMENT_REQ_PKG.GET_PAYMENT_REQ_LOOKUP",
objConnect.Connection);
DBCmd.CommandType = System.Data.CommandType.StoredProcedure;
I then I run the OracleCommand.ExecuteReader method to execute the
stored procedure.
I then CLOSE the connection by using the
this.Connection.Close(); command.
However, the sessions stay active!!!
I have a very
DBA on my tail about this one!Please help!
Thanks
Steveyou will need to turn connection pooling off (see your connect string
properties). with pooling on, connection.close only returns the connection t
o
the pool, it does not close it. you may make your dba happy by lowing the
pool timeout.
-- bruce (sqlwork.com)
"S_K" wrote:
> Hi all!
> I'm writing an ASP.NET web application that uses an Oracle database.
> I OPEN the Oracle connection by using the following code:
> if (this.ConnectionString != "")
> {
> this.Connection = new OracleConnection(this.ConnectionString);
> this.Connection.Open();
> return;
> }
> I then create the command object by using the following:
> OracleCommand DBCmd = new
> OracleCommand("PRL_STORE_PAYMENT_REQ_PKG.GET_PAYMENT_REQ_LOOKUP",
> objConnect.Connection);
> DBCmd.CommandType = System.Data.CommandType.StoredProcedure;
> I then I run the OracleCommand.ExecuteReader method to execute the
> stored procedure.
> I then CLOSE the connection by using the
> this.Connection.Close(); command.
> However, the sessions stay active!!!
> I have a very
DBA on my tail about this one!> Please help!
> Thanks
> Steve
>
Thursday, March 22, 2012
Thread doesnt do anything
I have a Sub called SendMessages() that is supposed to do some database work and send an email message in my web app.
Now if I call it directly, it does what it should. I've now been trying to place this Sub into a thread of its own..
i.e
Dim sendMessageThread as New Thread(AddressOf SendMessages)
sendMessageThread.Start()
But the page ends up doing nothing...
Any ideas?Is your SendMessages sub within the same class or in another class?
Have you stepped through your code to see if the thread is entering the sub?
It is in the same class. I'm not using VS.NET so I can't do any stepping...
The thread is being called from Page_Load()
Post the code.
As far as it seems, the Sub ReceiveMessages is not being called at all!!!
Here is my code
Sub Page_Load()
Dim NewThread As Thread = New Thread(AddressOf ReceiveMessages)
NewThread.Priority = ThreadPriority.Lowest
NewThread.Start()
End Sub
Public Sub ReceiveMessages()
' Code here to connect to pop3 server and loop through messages
End Sub
Now if I call it directly, it does what it should. I've now been trying to place this Sub into a thread of its own..
i.e
Dim sendMessageThread as New Thread(AddressOf SendMessages)
sendMessageThread.Start()
But the page ends up doing nothing...
Any ideas?Is your SendMessages sub within the same class or in another class?
Have you stepped through your code to see if the thread is entering the sub?
It is in the same class. I'm not using VS.NET so I can't do any stepping...
The thread is being called from Page_Load()
Post the code.
As far as it seems, the Sub ReceiveMessages is not being called at all!!!
Here is my code
Sub Page_Load()
Dim NewThread As Thread = New Thread(AddressOf ReceiveMessages)
NewThread.Priority = ThreadPriority.Lowest
NewThread.Start()
End Sub
Public Sub ReceiveMessages()
' Code here to connect to pop3 server and loop through messages
End Sub
Subscribe to:
Posts (Atom)