Subject AW: [IBO] Cannot insert new records after changing the where clause
Author Herbert Senner



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);

  [If Condition2]

  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

AfterPrepare Event:



  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:







Von: []
Gesendet: Samstag, 6. Dezember 2014 19:54
Betreff: Re: [IBO] Cannot insert new records after changing the where clause




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.