At 10:10 AM 27/04/2003 +1000, you wrote:

>I don't seem to understand when/how SQLWhereItems get added so that they are
>active. The snippet below shows what I thought would be valid. I've even
>tried invalidating the sql and refreshing, but no matter what the sequence,
>I only ever get the SQL being executed.. i.e. without the where clauses. If
>I add the where clauses directly into the SQL, it works fine. What do need
>to do to take advantage of the SQLWhereItems add - I need to provide a lot
>definition to the where section - it's be nice not to worry about whether
>I've added a 'WHERE' and 'AND' stuff.
>If I use the onPrepareSQL, I don;t have valid values for building the where
>clause, so I have to use this sort of sequence.
>Can someone enlighten me?
>Thanks
>Alan

Alan,
First off, SQLWhereItems are never seen by an open dataset.

Secondly, the *one* place where you can add them is in the OnPrepareSQL
event so, of course, it doesn't make any sense to try to add them if your
app code doesn't provide for the current SQL image to be invalidated.

Since invalidation of the SQL happens anyway, whenever you clear the SQL
property, trying to use SQLWhereItems doesn't make sense if your code
clears the SQL on every visit to the dataset. You should just hard-code
the WHERE clause if that's the way you want to do the SQL.

The sequence needs to be:

1. Begin with an SQL "stub" image (usually applied at design-time, but not
an absolute requirement). By "stub", I mean the SELECT statement with no
WHERE clause.

2. Capture the signal from the user that she wants to re-define the WHERE
items. At this point, you should test her choices to determine whether the
columns are in fact going to change. If not, just jump out of the sequence
and proceed to collect her new parameters for the existing WHERE
items....otherwise...

3. Capture new WHERE items from the user, e.g. by having her select the
columns she wants to refine.

4. In the capture code, invalidate the SQL and close the dataset.

5. In OnPrepareSQL event handler, clear and reassign the SQLWhereItems.

6. In the BeforeOpen event, cycle through the parameters, matching them up
to the latest SQLWhereItems, and apply the values to the parameters. Use
ParamByName to ensure a) that you really have caught the correct parameters
and b) to ensure that Prepare gets called. (That's just advice: if you
are really careful, you can use Params[] and call Prepare yourself.)

7. Call Open.

Helen