Cannot perform this operation on a closed dataset

[bazzer747 25]

Hi,

When I open my project I cycle through all the queries I use making sure they are all active, this works fine.

 

Depending on what year I want to view I have a radio box with '2019' and '2020' as selections and from that I need to 'filter' the data to only show the year chosen., I load the value into cYear. So I have this line:

 

qQueryname.Open( 'SELECT * FROM tTablename WHERE mYear = :pY ORDER BY eDate',[ cYear ] );

 

When this line is executed I get the error message above on this query, which is confusing me as it is an 'Open' command. The weirder thing is that the SQL is executed OK and the resulting data is the correct year's data.

 

Using: Filtered:= False; Filter:= 'MatchYear = ' + cYear; Filtered:= True; code instead works perfectly with no issues.

[Vandrovnik 214]

May be some event? OnBeforeOpen etc.? Or a master-detail relation, so that this error might happen on another query?

[Guest]

Try putting a aQueryname.Close; before that row.

What DAC (Data Access Components) do you use?

The DACs usually have functionality for updating a query after you change the parameter value.

 

So if you include the parameter from start i'd guess you just have to:

qQueryName.Params[0].AsInteger := 2019;

qQueryName.Refresh;

 

But the documentation for your DAC is key.

[Attila Kovacs 629]

why don't you hit 'Break' and look into the call stack?

 

[bazzer747 25]

That doesn't work - I get the same message when it tries to run aQueryname.Close

 

I'm using Firedac for these commands, I've used this shorthand version of opening a query with a new SQL many times with no problem. Just annoying the error message is so unhelpful, almost saying you can't open this until you open it!

 

The old fashioned filtering works fine; I'll try the param setting in the query and see if that works better. Thanks for replying.

[bazzer747 25]

Attila,

 

Did that but I'm no wiser, I'm not that versed in the deeper Delphi layers. What/how would I trace what the 'real' problem is?

[bazzer747 25]

Vandrovnik,

 

Not doing Master-Detail, and this occurs in the FormActivate so the first time the query is accessed other than when it is opened when the application starts.

[Guest]

3 hours ago, bazzer747 said:

That doesn't work - I get the same message when it tries to run aQueryname.Close

Please. If that was a reaction to my post then RTFM on you. It was a GUESS because i did not know you use FireDAC. Had i known that i'd not even commented because i do not have access to the FireDAC sources. Thus i do not use it.

Edited January 24, 2020 by Guest

[bazzer747 25]

Dany,

I meant no disrespect with my words, often they don't convey meaning very well. I did thank you for replying and am trying the other ways you mentioned. The documentation with Firedac, whilst extensive, is very poor at 'practical solutions' and error diagnosis. I have Cary Jenson's Delphi in Depth: FireDac (about the only one there is about Firedac) and again this doesn't cover the issues I'm having, so reading the manual isn't helping, hence my question on the forum.

[Guest]

@bazzer747, thanks! I was also a bit fast in my reply and should duly apologise too. All good!!

 

I have no idea of your economy or Delphi edition. But tracing into library code is my way of getting understanding.

I have Delhi Pro so FireDAC sources are not included. So i do not use FireDAC because it will leave me hanging. Docs are key but almost never enough IMHO.

 

Regards and good luck!

[Uwe Raabe 2057]

The message is thrown inside the CheckActive method of TDataSet. I also suggest some debugging session. I guess the reason lies at place you don't expect.

[Lars Fosdal 1792]

Use the FireDAC Monitor to check the operations sent to the DB.

 

Can it be type related?  What field type is mYear, what type is cYear?

Or - looking at the error message. Is the connection actually open?

Is the connection or dataset used elsewhere?

 

[bazzer747 25]

Hi,

Interesting suggestion, use the FireDac Monitor. I've never used that, never thought about this either, didn't know it existed even! I've checked the documentation about it and like a lot of Embarcadero documentation it rarely thinks the reader is a novice and doesn't give clear instructions. I've grappled by, but cannot get any output into the monitor, nor can I see any output using FDQuery.Text, so somewhere I haven't set a value or checked a box. I'll carry on searching/testing etc. as it seems like a very useful tool.

 

A suggestion above said to close the query first before opening it and this has worked, so another confusing event here - it cannot work on a closed dataset but after closing it it will open. Who or what closes a dataset? Nothing I do in the application specifically closes any dataset. Many times I respecify the query to change parameters but never knowingly close it. 

[Guest]

15 hours ago, bazzer747 said:

A suggestion above said to close the query first before opening it and this has worked, so another confusing event here - it cannot work on a closed dataset but after closing it it will open. Who or what closes a dataset? Nothing I do in the application specifically closes any dataset. Many times I respecify the query to change parameters but never knowingly close it.

That might have been me.

Generally, when you change the SQL text, a re-prepare is needed. That cannot be done on an open query. Not your problem above.

When you change the parameter values, the query needs to be re-executed (in a prepared state) and that entails close + re-open.

In a lot of cases the DAC will help you with this (like in a master-detail relationship) and i can confess that back when it took me some time to understand this.

You DAC should offer different ways of handling this, IMHO.

15 hours ago, bazzer747 said:

Who or what closes a dataset?

I cannot answer that (no FireDAC sources), @Uwe Raabe pointed in the right direction.

I would put an event handler on the query's OnClose and a brekapint in that handler. Then look at the call-stack and put breakpoints below that. Eventually you will find the "culprit".

 

One more thing, you use a method called "Open" that takes the parameter value as argument. To me that sound like something you do only once.

When the value is changed, some other method should be used. Maybe something like "qQueryname.Refresh([ cYear ] );". But again, i do not know.

 

I have a lot of [pseudocode]:

aQ.Params[0].AsInteger := aVar;

if aQ.Open then aQ.Refresh else aQ.Open;

 

HTH

[Lars Fosdal 1792]

@bazzer747

Added a rough outline of how to use the FireDAC monitor

 

[bazzer747 25]

Hi,

Thanks for this, I've got monitoring working OK now. Although there is so much information it takes ages to find what I need. I'll play with the controls to try to restrict this output to just what I need to look at. Many thanks for the lesson.

 

And Dany, thanks for the info. All very useful.