"Connection Must Be Opened Error" When Already open
-
- Posts: 30
- Joined: Sat 10 Nov 2007 20:36
"Connection Must Be Opened Error" When Already open
Hi
We are regularly getting the error:
Error Number : 5
Error Description : Connection must be opened.
Error Stack
CoreLab.Common.Utils.CheckConnectionOpen(IDbConnection connection)
CoreLab.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
System.Data.Common.DbCommand.ExecuteReader()
CoreLab.Common.DbCommandBase.ExecuteNonQuery()
7gvΠ9
We are regularly getting the error:
Error Number : 5
Error Description : Connection must be opened.
Error Stack
CoreLab.Common.Utils.CheckConnectionOpen(IDbConnection connection)
CoreLab.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
System.Data.Common.DbCommand.ExecuteReader()
CoreLab.Common.DbCommandBase.ExecuteNonQuery()
7gvΠ9
-
- Posts: 30
- Joined: Sat 10 Nov 2007 20:36
-
- Posts: 30
- Joined: Sat 10 Nov 2007 20:36
Could you please send me (alexsh at devart.com) the SQL script that creates tables and fills them with data. Give us a clearer example of the code with opening and closing connections and where the error arises.
What is the version of PostgreSQL Server you are using?
We will try to reproduce this problem.
What is the version of PostgreSQL Server you are using?
We will try to reproduce this problem.
-
- Posts: 30
- Joined: Sat 10 Nov 2007 20:36
No the issue is not resolved. Devart want a sample project, however I simply don't have the time to be doing this at the moment.
Shalex, the problem seems to have nothing to do with the schema of the table, therefore I'm sure if you create a table with a global connection, put a timer on the form and get it to interrogate the table every minute using a pgsqlcommand you will eventually run into the problem. Obviously this issue is a genuine problem as others are experiencing exactly the same anomally. If it's any help I have noticed that this problem seems to happen more consistently on Vista than XP (surprise surprise).
Phoebe, I have simply placed a globalconnection.open just before I execute the command. I don't know if this will help yet as the latest version of the product has not been released to our customers and it's them that are reporting this issue to us.
Shalex, the problem seems to have nothing to do with the schema of the table, therefore I'm sure if you create a table with a global connection, put a timer on the form and get it to interrogate the table every minute using a pgsqlcommand you will eventually run into the problem. Obviously this issue is a genuine problem as others are experiencing exactly the same anomally. If it's any help I have noticed that this problem seems to happen more consistently on Vista than XP (surprise surprise).
Phoebe, I have simply placed a globalconnection.open just before I execute the command. I don't know if this will help yet as the latest version of the product has not been released to our customers and it's them that are reporting this issue to us.
Same Issue
I am having the same problem, built a search engine, non of the stored procedures take more than half a second, yet I keep getting this error. I am afraid I have to change corelab with any other provider that does not have this problem.
-
- Posts: 30
- Joined: Sat 10 Nov 2007 20:36
It would be pointless, as it's completely unreproducable to order. It's an intermittent problem that seems to occur when doing executenonquery on a global connection. We only use it rarely, for example when we exit one of our forms we run this code:
ExecuteCommand("DELETE * FROM ", gcsAppointmentStaff, " WHERE [ID] = " & goUser.ID)
For Each oItem In lvw.Items
If oItem.Checked Then
ExecuteCommand("INSERT INTO ", gcsAppointmentStaff, " ([ID], [Owner ID]) VALUES (" & goUser.ID & ", " & oItem.Tag & ")")
End If
Next
Simple stuff but it results in a "Connection must be open" error every now and again. Of course this wouldn't happen with a dataadapter because I understand it automatically opens a connection when it needs one anyway, unlike a datareader. I could send a test project with this code in it, but come on, surely you can put one together yourself. Doesn't matter on the table structure, doesn't matter what query you execute. For some reason the global connection thinks it is closed, so something is telling the connection to close somewhere, whether this be a simple length of time open issue, or if the connection genuinely loses connection to the database for a moment (as can happen on any network) and then reestablishes itself and so thinks it is closed...I don't know. I do know that these intermittent, unreproducable errors are the hardest the fix so you have my sympathies.
Lee Ottaway
Fusion Software (UK) Ltd
ExecuteCommand("DELETE * FROM ", gcsAppointmentStaff, " WHERE [ID] = " & goUser.ID)
For Each oItem In lvw.Items
If oItem.Checked Then
ExecuteCommand("INSERT INTO ", gcsAppointmentStaff, " ([ID], [Owner ID]) VALUES (" & goUser.ID & ", " & oItem.Tag & ")")
End If
Next
Simple stuff but it results in a "Connection must be open" error every now and again. Of course this wouldn't happen with a dataadapter because I understand it automatically opens a connection when it needs one anyway, unlike a datareader. I could send a test project with this code in it, but come on, surely you can put one together yourself. Doesn't matter on the table structure, doesn't matter what query you execute. For some reason the global connection thinks it is closed, so something is telling the connection to close somewhere, whether this be a simple length of time open issue, or if the connection genuinely loses connection to the database for a moment (as can happen on any network) and then reestablishes itself and so thinks it is closed...I don't know. I do know that these intermittent, unreproducable errors are the hardest the fix so you have my sympathies.
Lee Ottaway
Fusion Software (UK) Ltd
Hi there - I am experiencing EXACTLY the same problem.
However, this problem has only started occuring since I updated my application as per your suggestions in this thread :-
http://devart.com/forums/viewtopic.php?t=14287
Just to recap, previously I was using "Pooling=true" in my ConnectionString and I was then creating and disposing a PgsqlConnection object for EVERY database call.
Following your suggestion, I changed this to "Pooling=false" and I *only* have ONE global instance of PgsqlConection which is used throughout the application. This instance is created at program startup and disposed on termination.
Since making this change, a number of users are complaining of receiving a "Connection must be opened" error. Here is a typical Stack Trace which a user emailed me from their error report :-
This problem never happened before this change. The only way I have managed to reproduce this problem is as follows :-
[1] Create a test app with a single global connection object (which is created and opened at startup) and button which fills a table and assigns the table to a DataView.
[2] Run the program and connect to a Postgres database on a different PC
[3] While the program is running, disable and re-enable your network card.
[4] Click the button to fill the datatable.
You will (more than likely) receive the "Connection must be opened" message or if not, you will still get an error message.
The work-around for this that I have implemented in my application is to manually call CoreLab.Common.Utils.CheckConnectionOpen(my_con); whenever I am about to use the global connection. If that line throws an exception, I call my_con.Close(); my_con.Open(); to re-establish the connection.
This seems to resolve the issue (for me at least). I hope this helps anyone else who is suffering from this problem.
Regards,
Hedley
However, this problem has only started occuring since I updated my application as per your suggestions in this thread :-
http://devart.com/forums/viewtopic.php?t=14287
Just to recap, previously I was using "Pooling=true" in my ConnectionString and I was then creating and disposing a PgsqlConnection object for EVERY database call.
Following your suggestion, I changed this to "Pooling=false" and I *only* have ONE global instance of PgsqlConection which is used throughout the application. This instance is created at program startup and disposed on termination.
Since making this change, a number of users are complaining of receiving a "Connection must be opened" error. Here is a typical Stack Trace which a user emailed me from their error report :-
Code: Select all
System.InvalidOperationException
Connection must be opened.
Server stack trace:
at CoreLab.Common.Utils.CheckConnectionOpen(IDbConnection connection)
at CoreLab.Common.DbCommandBase.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
at CoreLab.Common.DbCommandBase.AsyncExecuteReader(CommandBehavior behavior)
at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
at System.Runtime.Remoting.Messaging.StackBuilderSink.PrivateProcessMessage(RuntimeMethodHandle md, Object[] args, Object server, Int32 methodPtr, Boolean fExecuteInContext, Object[]& outArgs)
at System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(IMessage msg, IMessageSink replySink)
Exception rethrown at [0]:
at System.Runtime.Remoting.Proxies.RealProxy.EndInvokeHelper(Message reqMsg, Boolean bProxyCase)
at System.Runtime.Remoting.Proxies.RemotingProxy.Invoke(Object NotUsed, MessageData& msgData)
at CoreLab.Common.DbCommandBase.a.EndInvoke(IAsyncResult A_0)
at CoreLab.Common.DbCommandBase.EndExecuteReader(IAsyncResult result)
at CoreLab.PostgreSql.PgSqlCommand.EndExecuteReader(IAsyncResult result)
at ClinicOffice.cDB_PGSQL.FillTable(DataTable Table, String SQL)
at ClinicOffice.cDB.GetDataRow(String SQL)
at ClinicOffice.cController.CheckStillLoggedOn()
at ClinicOffice.cController.m_timerSystem_Tick(Object sender, EventArgs e)
at System.Windows.Forms.Timer.OnTick(EventArgs e)
at System.Windows.Forms.Timer.TimerNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
[1] Create a test app with a single global connection object (which is created and opened at startup) and button which fills a table and assigns the table to a DataView.
[2] Run the program and connect to a Postgres database on a different PC
[3] While the program is running, disable and re-enable your network card.
[4] Click the button to fill the datatable.
You will (more than likely) receive the "Connection must be opened" message or if not, you will still get an error message.
The work-around for this that I have implemented in my application is to manually call CoreLab.Common.Utils.CheckConnectionOpen(my_con); whenever I am about to use the global connection. If that line throws an exception, I call my_con.Close(); my_con.Open(); to re-establish the connection.
This seems to resolve the issue (for me at least). I hope this helps anyone else who is suffering from this problem.
Regards,
Hedley
Thanks for your reply.
>> you have got the designed behaviour
But when I had "Pooling=true" and was constantly creating and disposing of the PgsqlConnection object everytime, I never experienced this problem.
In fact, since changing to a single persistent PgsqlConnection object, I have had an absolute slew of technical support problems like the "Connection must be opened" error.
Is there any compelling reason why I shouldn't switch back to the previous method of creating/disposing the connection objects?
>> you have got the designed behaviour
But when I had "Pooling=true" and was constantly creating and disposing of the PgsqlConnection object everytime, I never experienced this problem.
In fact, since changing to a single persistent PgsqlConnection object, I have had an absolute slew of technical support problems like the "Connection must be opened" error.
Is there any compelling reason why I shouldn't switch back to the previous method of creating/disposing the connection objects?
By "designed behaviour" I mean the error after disabling and re-enabling network card.
We cannot reproduce the "Connection must be opened" issue. We don't know the reason why you are getting this error. Maybe connection is closed before it is used somewhere in your code. Please review your code.
There is no compelling reason to use the approach with one global connection only. You choose yourself the implementation that is more convenient for you.
We cannot reproduce the "Connection must be opened" issue. We don't know the reason why you are getting this error. Maybe connection is closed before it is used somewhere in your code. Please review your code.
There is no compelling reason to use the approach with one global connection only. You choose yourself the implementation that is more convenient for you.