Hi Helen, thanks again for your answer.

My code is very similar to the SearchinLinks Sample that comes with IBO. I borrowed some of that code, and that code works. See below...

--- In IBObjects@y..., Helen Borrie <helebor@t...> wrote:
> At 12:11 PM 07-11-02 +0000, you wrote:
> >Hi again:
> >
> >Thanks for your answer (very fast).
> >
> >The table on which I want to use SearchinLinks is defined in Interbase
> >with columns not using "NOT NULL", but there are no records with a NULL
> >value on it. If I make a little program modeled as the SearchingLinks
> >sample that comes with IBO I don't get the expected results. When I
> >reorder a column that hasn't got "NOT NULL" on its definition all data in
> >the dataset "disappears". I use the following code in the OnPrepareSQL
> >(all columns are of type text):
> >
> >procedure TfrmRelacionar.query1PrepareSQL(Sender: TIB_Statement);
> >const
> > START = 'STARTING WITH';
> > CONT = 'CONTAINING';
> > WHERECLAUSE ='UPPER(%s) %s :%s';
> >begin
> > with query1 do begin
> > if RadioGroup1.ItemIndex = 0 then
> > SQLWhereItems.Add(Format(WHERECLAUSE, [OrderingLink, START,
> > OrderingLink]))
> > else
> > SQLWhereItems.Add(Format(WHERECLAUSE, [OrderingLink, CONT,
> > OrderingLink]));
> > end; { with query1 do...}
> >
> >But if the OnPrepareSQL is not used and I reorder the columns (even those
> >with NULLs) everything works fine.
> >
> >What could be the problem in this case?. I think this a problem in IBO.
>
> When you change the SQL, you actually cause the dataset to recreate
> itself. All datasets are the encapsulation of a SELECT statement and, by
> changing the statement, you are effectively causing a new dataset object to
> be created. IBO mitigates the impact of this with SQLWhereItems but it's
> still a fairly radical renewal that takes place.
>
> In order to have the OnPrepareSQL event fire, you have to make sure to
> create a condition where the dataset actually knows it needs to call
> Prepare. So, in some preceding user event (perhaps your radiobutton click)
> you need to do this:
>
> ...
> if query1.Prepared then
> query1.Unprepare;
> ...

Yes, on my radiobutton click I have:

query1.InvalidateSQL; { same as SearchingLinks sample }
query1.Refresh;

> Once the query is unprepared, I think the OrderingLink property becomes
> inactive until the Prepare code fires and reactivates it - and this will
> not occur until *after* the OnPrepareSQL event. Probably you should at
> least test to see whether OrderingLink contains a non-empty string; but it
> would probably be safer to access the OrderingField
> (OrderingField.FieldName) property, since OrderingField is the actual
> TIB_Column object reference.

I debugged my code and OrderingLink always had the correct values.

>
> Also, once you do get this event firing, your current code is going to
> build up into a longer and long string of increasingly impossible search
> criteria. You need to call SQLWhereItems.Clear before adding the altered
> criteria.

Again, while debugging my source code SQL.Text always had the correct values.

>
> Helen


The only difference between SearchinLinks Sample and my program is the definition of the columns of the table. If you set the flag "NOT NULL" in Interbase you have no problems. It's the same symptoms of Message 17027.

Thanks,

Mene