[solved] IBDynamicGrid - Parameters do not work.

[Nicole]

To my mind, there is everything ok with all parameters and syntax.
But there is a substantial error in the way I put them into events or I used them.

Let us have a closer look, there is an ibdynamicGrid_Tilgung, which is linked to a ibQuery_Tilgung.
its SQL reads,

Code: MySQL  [Select][+][-]

1. SELECT ID_KUPON, DATUM, FK_EINKOMMEN, BETRAG, BEZEICHNUNG, BEZEICHNUNG_LANG
2. FROM TBKUPONS a join tbeinkommen
3. on FK_EINKOMMEN = id_einkommen
4. where (betrag > :Betrag) and (Datum > :Datum)  

now the first oups awaits us:
If I activate allow active transcation the where clause works, if I deactive it there where clause works all the time no matter, if the switch is on or off.

So where is the switch:
I put it into the before_open-event and it reads (the checkbox is the switch):

Code: Pascal  [Select][+][-]

1. procedure ......IBQuery_TilgungBeforeOpen(DataSet: TDataSet);
2. begin
3.   IBQuery_Tilgung.ParamByName('Betrag').AsInteger:=1000;
4.  
5.   if CheckBox_TilgungDatum.Checked  // es wird ab einem Datum geprüft (default "heute")
6.        then IBQuery_Tilgung.ParamByName('Datum').AsDateTime:=DateTimePicker_Tilgung.Date
7.        else IBQuery_Tilgung.ParamByName('Datum').AsDateTime:=0; // oder "alles"
8. end;

probably the solution is easy for the one, who knows, how it works.

[Zvoni]

IBQuery.Refresh?

[Nicole]

There is no refresh. There is a beforeRefresh, beforePost...

I tried beforeRefresh and it did not work.

[Zvoni]

There is no refresh. There is a beforeRefresh, beforePost...

I tried beforeRefresh and it did not work.

Huh??
https://docwiki.embarcadero.com/Libraries/Athens/en/IBX.IBQuery.TIBQuery_Methods
I clearly see a refresh-Method....

EDIT: Might even be an idea to call "Prepare" explicitely before refreshing/opening again

[Nicole]

Thank you for your answers, pls again for dummies:

My issue is, that I need to set query-paramters for an IBDynamicGrid, which is different from an DBGrid, because it allows sorting.
The trigger for activating the query is a button in my user interface.

What I tried is to go to the object inspector and guessing, which event may work.
You named "refresh" and "prepare", which I do not find in the object inspector.

Does this mean, that I have to write such events and name them like this?
And my parameters would be set there?
What about this "allow transaction" (or similar). Should it be checked or not?

Or something different?
I remember, that Tony once named an event to use. Unfortunately I can neither remember which, nor find the thread any more.

[Zvoni]

Thank you for your answers, pls again for dummies:

My issue is, that I need to set query-paramters for an IBDynamicGrid, which is different from an DBGrid, because it allows sorting.
The trigger for activating the query is a button in my user interface.

What I tried is to go to the object inspector and guessing, which event may work.
You named "refresh" and "prepare", which I do not find in the object inspector.

Does this mean, that I have to write such events and name them like this?
And my parameters would be set there?
What about this "allow transaction" (or similar). Should it be checked or not?

Or something different?
I remember, that Tony once named an event to use. Unfortunately I can neither remember which, nor find the thread any more.

Prepare/Refresh are NOT Events!
Those are Methods of the IBQuery-Object itself!

Code: Pascal  [Select][+][-]

1. .....
2. IBQuery_Tilgung.Close;
3. IBQuery_Tilgung.ParamByName('Datum').AsDateTime:=Blablabla
4. IBQuery_Tilgung.Prepare;
5. IBQuery_Tilgung.Open;  //Or refresh. No Idea

And Transactions are NOT neccessary for pure SELECT's, so choose what works

[Nicole]

 

It works, thank you so much.
I was quite sure, that there is a very easy solution for which I am blindfolded.

[tonyw]

It works, thank you so much.
I was quite sure, that there is a very easy solution for which I am blindfolded.

Nicole,

You should always set query parameters in the dataset's BeforeOpen event handler. This is good practice generally, but is essential for IBDynamicGrid.

[Nicole]

Thank you for the hint.

I will try it, may be you have a second hint for me?
It is hard to work with it.
At the moment there is a topic let us say ListCustomers. I generate a SQL-thing e.g. IBSQL_ListCustomers and a button, e.g. button_ListCustomers. In the click-event there I wirte the SQL-code and fill in the parameters.
If the sql-text is in the edit field and the parameters in an event, it needs a lot of clicks more to find out, what belongs to which.

Or is my style here poor as well?
I never learnt programming. I am a lonely girl and learnt what I need to perform a certain task.

[tonyw]

Thank you for the hint.

I will try it, may be you have a second hint for me?

Nicole,

it may be that the point that you are missing is that (as a design choice) IBX always clears query parameter values when a dataset is closed. It is thus OK to do something like this:

MyDataset.ParamByName('foobar').AsInteger := ...
MyDataset.Active := true;

but only if you do not close "MyDataset" until the form closes. If you close and re-open "MyDataset" without updating the query parameters then you get an error.

A datasets may be closed and re-opened for several reasons:

1. You have done this explicitly in your program.
2. It is the "Detail" dataset in a "Master/Detail" relationship, and the Master dataset is scrolled.
3. A dynamic control (e.g. IBDynamicGrid) changes its view on the data (e.g. by clicking on a column header).

The way to ensure that query parameters are always set every time a dataset is opened is to set each parameter in the Dataset's BeforeOpen eventhandler. This works because this event handler is always called when a dataset is opened or re-opened and immediately before the select query is executed.

It is hard to work with it.
At the moment there is a topic let us say ListCustomers. I generate a SQL-thing e.g. IBSQL_ListCustomers and a button, e.g. button_ListCustomers. In the click-event there I wirte the SQL-code and fill in the parameters.
If the sql-text is in the edit field and the parameters in an event, it needs a lot of clicks more to find out, what belongs to which.

Or is my style here poor as well?
I never learnt programming. I am a lonely girl and learnt what I need to perform a certain task.

You don't need to follow a prescribed course in order to be a successful programmer. The only course I have ever been on was in 1975 when I did a two week introductory course on AlgolW (the precursor to Pascal) in between the first year exams of my Maths degree and getting the results. What I really learnt from that course was that I liked programming and went on to have (I believe successful) career in the IT industry over the next five decades

[Nicole]

Thank you so much for the explanation.
I will safe it in my code-snippets, where I always look into in case of troubles.

And thank you for the story about yourself. Then you really started earlier than me. I started with Pascal 2.0 and then did a huge step to Borland's  unbelievable Pascal 6.0. In between I made my master in law and worked in economy. However I must say, that law and programming are not that different of the way of thinking.

After this I was blessed by this guy, who cracked all Delphi versions. After he gave up, I cracked it by myself, this was D 2007, - I hated it. Delphi XE 3 I bought. The more I used it, the more I started to hate it. And it made me anxious: Because it was online bound to the Embarcadero-server. The day, they wanted to shoot me, they have the option to do it and destroy a decade of work with a click of a mouse.

Then I found Lazarus and there is my home since some years and I feel great here. And I am that grateful for this forum, which was the only way for me to make it at all. I never would have managed the migration by myself.