OnCalcField modifying its own DataSet - Error

[MoZo1]

Well... I could copy the data, and use that, but that would be very slow, and I would have to copy often and a lot. I have a table where records depends on another one. Still, it will be the backup if I can't solve this.
So every time I try to move the active record from inside the OnCalcField event I get a lot of some veird EOleException invalid argument error, even when I don't even read or write any data. I know, I must restore the dataset at the end of the event, as I do every time before EnableControls.

This is my event:

procedure TForm1.calc(DataSet: TDataSet);
begin
try
Dataset.FieldByName('Calc').AsInteger:=10;
if qwe then exit;
qwe:=true;
dataset.DisableControls;
//dataset.next;
//if not dataset.Eof then dataset.prior;
dataset.EnableControls;
ListBox1.AddItem(Dataset.FieldByName('SampleNum').AsString,nil);
qwe:=false;
except
showmessage('This never shows!');
ADOQuery1.OnCalcFields:=nil;
end;
end;

When I try to uncomment the 2 lines it throws the errors, but I can't even catch it. If something handles the error, it couldn't become recursive because qwe prevents it. And I've already tryed with GotoBookmark and First, in case eof would cause this, but it's the same.

Also I can't find anything on google, so it would be nice if somebody could only show me a working example.
Thanks!

[ecostas]

Hi. If you are using ClientDatasets, maybe you can try RecNo and Recordcount to find out if you are in the last record. But from your code I can' t understand why you need it. Maybe that's not exactly your code. Haven't tried my solution, to be honest.

Eduardo

Well... I could copy the data, and use that, but that would be very slow, and I would have to copy often and a lot. I have a table where records depends on another one. Still, it will be the backup if I can't solve this.
So every time I try to move the active record from inside the OnCalcField event I get a lot of some veird EOleException invalid argument error, even when I don't even read or write any data. I know, I must restore the dataset at the end of the event, as I do every time before EnableControls.

This is my event:

procedure TForm1.calc(DataSet: TDataSet);
begin
try
Dataset.FieldByName('Calc').AsInteger:=10;
if qwe then exit;
qwe:=true;
dataset.DisableControls;
//dataset.next;
//if not dataset.Eof then dataset.prior;
dataset.EnableControls;
ListBox1.AddItem(Dataset.FieldByName('SampleNum').AsString,nil);
qwe:=false;
except
showmessage('This never shows!');
ADOQuery1.OnCalcFields:=nil;
end;
end;

When I try to uncomment the 2 lines it throws the errors, but I can't even catch it. If something handles the error, it couldn't become recursive because qwe prevents it. And I've already tryed with GotoBookmark and First, in case eof would cause this, but it's the same.

Also I can't find anything on google, so it would be nice if somebody could only show me a working example.
Thanks!

[MoZo1]

This code obviously doesn't need it, but I will in the program I'm writing. I've just made a test program to separate the error with the minimal code needed to reproduce it.
Of course in the final code there will be some procesing, but I'm happy that I haven't written 100 lines of code in a much more complicated program to debug this stupid error there.
And I've already written that I've tryed bookmark+first to eliminate this, but probably it's not this. Also I've tryed now if dataset.recno>dataset.recordcount-3 then exit; after the 1st line with the exit, everything is the same. But I don't even reach the end of the dataset, because the dbgrid is too small to draw it (I know this by the logging line), so it really can't be this.

Also a new thing I've noticed that if I make a breakpoint then I see that this code runs for some time without error, and then never runs when the errors come.

[Micheus]

So every time I try to move the active record from inside the OnCalcField event I get a lot of some veird EOleException invalid argument error, even when I don't even read or write any data.

Does you have looked in the help file and search for OnCalcFields event?

MoZo1, this event occurs in many circumstances and many times. See this small portion:
OnCalcFields is called frequently, so the code it contains should be brief.
When the AutoCalcFields property is True, OnCalcFields is triggered when:

• A dataset is opened.
• A dataset is put into dsEdit state.
• Focus moves from one visual control to another, or from one column to another is a data-aware grid control and modifications were made to the record.
• A record is retrieved from a database.

So, from the moment that dataset is opened until all data is showed in the DBGrid, your application will notice that OnCalcEvent was triggered a lote of times.
You can have an idea from this by putting one TMemo (Memo1) into your form and adding this code to your OnCalcFields event:

procedure TForm1.calc(DataSet: TDataSet);
begin
  Memo1.Lines.Add('OnCalcFields passed...');
end;

You never must change any data field from dataset owned by calculated field (except from the calculated fields), as well dataset position at this event (it will generate recursive calls).

If You need to make some thing when a DBGrid selection is changed (current record has its position changed), You must use the OnDataChange evento from TDataDouce componente connected to desired dataset.

Bye

[MoZo1]

As you can see, I already have a logging code. My event is called every time something read a calculated field. So at the start of the program, when the dbgrid reads whatever it displays, and when I scroll it later, then for only those records.
And I know, that I shouldn't do this, but I'm handling recursive calls, and I'm returning the cursor where it was, so I should be able to do this.
And now the case is that this table has a "foregin key" that points into the same table, so I have to access those records, and I have to make calculated fields from them. To be worse, I have to show the effect when the user changes one value instantly, so this is why I use calculated fields.
Anybody that knows how to fix this, or can link here some webpage about this topic would be appreciated. Thanks!

[Micheus]

My event is called every time something read a calculated field.

when you must to initilize it.

... but I'm handling recursive calls, and I'm returning the cursor where it was, so I should be able to do this.

You can't.
DisableControls only will "stops" to update any data-aware control connected to this dataset. Moving across dataset will trigger this evento in order to update the calculated fields for this new row (cursor position).

And now the case is that this table has a "foregin key" that points into the same table, so I have to access those records, and I have to make calculated fields from them. To be worse, I have to show the effect when the user changes one value instantly, so this is why I use calculated fields.

In this case, You must use an other dataset only to reach this goal. I sugest that You use some query componente to do this.

Did you understood? If You explain better, putting table fields and relationships, maybe I could examplify.

Bye

[MoZo1]

when you must to initilize it.

I must initialize it every time something reads it, because delphi doesn't store them, and TADOQuery can't use internally calculated fields. But this doesn't really matter, because if it can run once, it could run any times.

You can't.
DisableControls only will "stops" to update any data-aware control connected to this dataset. Moving across dataset will trigger this evento in order to update the calculated fields for this new row (cursor position).
So with other words, only outside calls will need that data, but when the data is being calculated, the object (that will use this database) will turn off the calculation internally, because every call that occurs there are from inside (of course, because delphi is strictly single threaded), so it won't use that data ever, so other calculation requests won't cause recursions, but instantly returns with nothing instead.

But that second calculate call won't trigger another one, so it stops. DisableControls only helps that these null values won't be cached into the visual controls. I've successfully used this technic once, the only difference was that it was a phisycally stored field.

In this case, You must use an other dataset only to reach this goal. I sugest that You use some query componente to do this.

And refresh it with all of the records every time the other one get changed. If it can't be helped, I'll do this of course, but I still don't know what is this exeption, and why is it there.

Did you understood? If You explain better, putting table fields and relationships, maybe I could examplify.

Bye

I know what I'm doing. I don't know what the hell Delphi is doing. T_T
Of course I could solve this with the second table, or a memory cache, or whatever easily, but this sollution would be the easyest and fastest, and I don't see any obstacle in theory why I can't do this. Well, at least I've tryed... Thanks for the help anyway!

[fishsqzr]

I haven't tried to run your code, but I can tell you this:
When your uncomment the line which moves to the next record, what is happening is that Delphi is calling OnCalc again automatically as soon as the record changes, and you are probably trashing the stack when the call goes into an endless loop because each call to OnCalc moves the record which send it back to OnCalc.......... OnCalc is best used to just update the current record (or other data dependent on the current record). It is a very useful tool (I sure with C#.NET had it) but it can't be used for everything.

I'm not quite sure what it is you're actually trying to accomplish but it looks like you are using the wrong event.

[MoZo1]

Well, I'm going to forfeit this one, but please notice these:

Also a new thing I've noticed that if I make a breakpoint then I see that this code runs for some time without error, and then never runs when the errors come.

-> No recursion occurs on my level of code. And I've already told that the error is an EOleException and not a stack overflow.

And again this one too: Watch qwe! When my event is triggered, then it sets the qwe variable (disabling itself), then disablecontrols, and after then it moves the dataset, retriggers itself, but won't do anything while qwe is true, because it knows that calculating isn't needed, and when it've finished with the processing, it unsets qwe (enabling itself), enablecontrols and returns the value. The visual controls won't notice a thing because of disablecontrols, and the recursive calling on the dataset is handled.

And at last, no stack overflow error, no damn long stack, and no recursion can be detected through breakpoints. Probably if you haven't tryed my code, you will think, this isn't something strange, and I've just failed to notice a stack overflow, and writing stupidity here. But even then, you should be able to point out what's wrong with my handling.
And if you're interested, I use delphi 2005!

[Micheus]

And refresh it with all of the records every time the other one get changed.

Not all. Only use a query component with restricted SQL instruction - use WHERE clause in order to get only the respective record at once - and in SELECT clause, use only the necessary fields (not all, like "select * from...").

And again this one too: Watch qwe! When my event is triggered, then it sets the qwe variable (disabling itself), then disablecontrols, and after then it moves the dataset, retriggers itself, but won't do anything while qwe is true, because it knows that calculating isn't needed, and when it've finished with the processing, it unsets qwe (enabling itself), enablecontrols and returns the value. The visual controls won't notice a thing because of disablecontrols, and the recursive calling on the dataset is handled.

It's not totally truth.
Just make this change in your code:

...
  Dataset.FieldByName('Calc').AsInteger:=10;
  if qwe then 
  begin
    ListBox1.AddItem('OnCalcField event recalled',nil);
    exit;
  end;
  qwe:=true;
...

And at last, no stack overflow error, no damn long stack, and no recursion can be detected through breakpoints.

It's truth!

Is very difficult to debug this kind of code, because a break point at this event will unfortunately make the DBGrid paint its self. And in order to get data to display...

Take a look at the attached image. I have positioned the delphi windows in order to prevente redrawing influence.
The DBGrid's linked dataset has seven records where PersonCode has a sequencial value from 1 to 7. Notice this points:
- Call Stack window shows what is in process where the error occurs;
- Editor windows shows the break point at line where the error occurs - DBGrid unit. See tooltip window appoints the EOleException;
- At background, Form1 is the program running. Looking at the DBGrid we can ask: Where is displayed values when PersonCode is 2? And looking to ListBox we see that a recursive call was made when trying process this value.

You can try by your self.

I know what I'm doing. I don't know what the hell Delphi is doing. T_T
..., and I don't see any obstacle in theory why I can't do this.

Well, if You really need to know what the hell Delphi is doing, I think that now You have a track to go ahead.

My final words is: Do not use OnCalcFields event by this way.

Good luck.

[MoZo1]

Thanks for the last post. I've extended the logging and noticed, that something have messed up the dataset's navigation:

Calling: 01.30 | level=1
-Processing: 01.30
--beforenext: 01.30
Calling: 01.29 | level=2
OnCalcField event recall handled, returning: 01.29
--afternext: 01.29
--afterprior: 01.29
Returning: 01.29
Calling: 01.28 | level=1
-Processing: 01.28
--beforenext: 01.28
--afternext: 01.28
--afterprior: 01.28
Returning: 01.28
Calling: 01.27 | level=1
-Processing: 01.27
--beforenext: 01.27
--afternext: 01.27
--afterprior: 01.27
Returning: 01.27
Calling: 01.26 | level=1
-Processing: 01.26
--beforenext: 01.26
--afternext: 01.26
--afterprior: 01.26
Returning: 01.26
Calling: 01.06 | level=1
-Processing: 01.06
--beforenext: 01.06
--afternext: 01.06
--afterprior: 01.06
Returning: 01.06
Calling: 01.07 | level=1
-Processing: 01.07
--beforenext: 01.07
--afternext: 01.07
--afterprior: 01.07
Returning: 01.07
Calling: 01.08 | level=1
-Processing: 01.08
--beforenext: 01.08
--afternext: 01.08
--afterprior: 01.08
Returning: 01.08
!Error at DS position = 01.25: Az argumentumok nem megfelelő típusúak, az érvényes tartományon kívül esnek vagy ellentmondásosak

(Well, the numbers are not in order, I've used the 1st database I've found, and the last error's translation is the same that you get by setting the filter to 0=1. )
It's so easy, and I would have never tought, that my code doesn't work, because one of it's command doesn't work. Now I really have enough from this.
I'll send the data back to the server, and rerequest it with aother querry every time when I show a field on a visual control... I'm still not proud of it, but at least now I know why I have to.

[Micheus]

I'll send the data back to the server, and rerequest it with aother querry every time when I show a field on a visual control...

There is some options:
- use an other query with calculated fields and OnCalcFields event, if you using DBGrid ;
- use join in SQL, if your data is only to be read;
- use an other query to show complementary data in others data controls, by using OnDataChange event from DataSource;
...

Bye

[MoZo1]

There is some options:
1 - use an other query with calculated fields and OnCalcFields event, if you using DBGrid ;
2 - use join in SQL, if your data is only to be read;
3 - use an other query to show complementary data in others data controls, by using OnDataChange event from DataSource;
...

Bye

1: I mayl do this. I'll have to check how my transactions are affecting this. As much I can remember, there is some setting calles "isolation" or something associated with this... I'll google it...
2: Not only I have to write, but I would like to show changes instantly. I could join the data the calculation needs, but that wouldn't be the sollution to show changes instantly. Moreover it would be troublesome to reload it and then reset everything for the user to not notice. Reloading another read-only dataset, or sending a querry is more easy, and consumes max the same or less CPU.
3: Another cool idea. I just have to separate the calculation from the oncalc event. I can't give more reputation today... Then I'll use a memory array (or steam) instead of another visual control, to save the calculation there, EnableControls only when it's fully ready, then read this from the OnCalc event.
+1: Instead of 3, if I can, I make a persistent field, and set it to not send back to the server, maybe I can save data there, and the dataset could access it naturally. It will save me from a lot of typing. I know, I'm about to hack again, bad habit... :D

Anyway, see you later. The main topic have been solved, that was the point, thanks again!