Page 1 of 2
"Connection Must Be Opened Error" When Already open
Posted: Fri 29 Aug 2008 08:36
by leeottaway
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
Posted: Mon 01 Sep 2008 10:00
by leeottaway
So am I to understand that you are just ignoring this problem since everyone else has been getting a response and this hasn't??
Posted: Mon 01 Sep 2008 11:42
by Shalex
We are investigating the reason of this error. You will be notified about the results as soon as possible.
Posted: Mon 01 Sep 2008 12:20
by leeottaway
OK, just wanted to know that it was being handled.
Posted: Fri 05 Sep 2008 12:05
by Shalex
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.
Posted: Thu 25 Sep 2008 01:23
by phoebe007
Hi Was this issue resolved?
I am experiencing the same problem and it is intermittent and happens on different tables.
Thanks
Phoebe
Posted: Thu 25 Sep 2008 07:39
by leeottaway
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.
Same Issue
Posted: Sun 14 Dec 2008 13:26
by irimawi
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.
Posted: Mon 15 Dec 2008 13:21
by AndreyR
Can anybody experiencing the problem above send me (andreyr * devart * com) a small test project that illustrates the problem?
That would greatly help the investigation.
Posted: Mon 15 Dec 2008 15:08
by leeottaway
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
Posted: Thu 19 Mar 2009 14:57
by Shalex
We cannot reproduce the "Connection must be open" error neither with the 4.0.22 version nor with 4.50.24. We need a test project that illustrates the problem.
Posted: Mon 30 Mar 2009 15:10
by hmuscroft
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 :-
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)
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
Posted: Tue 31 Mar 2009 07:23
by Shalex
This workaround can be used. As for disabling and re-enabling network card, you have got the designed behaviour. Because connection is lost when network is not available, and dotConnect for PostgreSQL doesn't have any internal implementation for re-establishing the lost connection.
Posted: Wed 01 Apr 2009 14:28
by hmuscroft
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?
Posted: Thu 02 Apr 2009 09:04
by Shalex
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.