|Subject||AW: [IBO] Cannot insert new records after changing the where clause|
just a hint for working with WHERE clauses.
The IB_Datasets have a SQLWhereItem property, which can be
changed in and only in the OnPrepareSQL event.
What I do if I only want to change the WHERE clause is with
[If Condition1 then]
SQLWhereItems.add(‘Column1 = :Param1’ or ‘Column1 = Value);
SQLWhereItems.add(‘Column2 = :Param2’) and so on.
By default the SQLWhereItems are ANDED together (see IBO-Help)
but can ORED as well (in some recent versions IBO however had a
bug in building ORED selections itself. If I need ORED selections I
build them myself as a single string).
If you are using Params, values can then be assigned in the
P := Dataset.FindParam(‘Param1’);
If Assigned(p) then
p.Value (AsInteger, AsString …) := Value;
In my experience this technique is very versatile. You can be sure that
only the WHERE clause is changed and the rest of your SQL text not
touched at all.
Rebuilding the WHERE clause would be triggered for example in your
components OnChange event:
thank you for the heads up.
Discarding components and start over with fresh ones, thats what I have done for last two days, to no evail.
But anyway, as I was building a little app to demonstrate the error, I realised that I do not only change the where clause but the whole SQL text. So I gave it a try and chanded it so that it is really only the where clause that gets changed. Lo and behold, that works.
Just a litte bit of explanation:
The code in question is old but so far only used to search in datasets, so that this error never came up before.
I store the original SQL text in a string variable before building the where clause, the new SQL text gets build by combining the two. When the user has cleared the searchbox, I revert to the original SQL text. This apparently broke the relation to the insert statement.
Now it works, thanks again.