I have a Dataset who's row source is set to a table.  I also have a grid that uses this Dataset.  All of this is set at design time. I would like for the user to narrow the rows based on search criteria that maybe uses the LIKE function; I am not even sure that EWB has a LIKE function.

I looked at some existing posting regarding filtering and from them I see where you can base the Dataset on a query with params.

My questions:

1. Can you change the run time row source of a Dataset to use a query instead of a table then change it back to the table, all at run time?

2. If I set the row source of a Dataset to use a query that doesn't include parameters, can I add parameters to this query at run time then change it back to have no parameters?

Hope all of this makes sense as I appear to be all over the place in trying to accomplish this.  Any suggestions as to how to implement filtering of a Dataset at run time would be greatly appreciated.

Thanks,
TD

You can't filter a dataset in EWB, but it's no problem to load a second
dataset with different params.

Just set the "dataset" property of your databound controls to the
filtered dataset. You can do that at runtime without problems.

To simplify things, a procedure like this helps:

procedure TMainForm.ChangeDataset(FDataset: TDataset);
begin
   lb1.dataset := FDataset;
   lb1.dataset := FDataset;
   edit1.dataset := FDataset;
   ...
end;

Uses this procedure in the AfterLoad event of the dataset.

Another approach would be to use the same dataset with or without params:

SELECT * from MyTable
WHERE UserID = {UserID=1}
AND Field1 like '%'+{Field1=''}+'%'
AND "Field2" like '%'+{Field2=''}+'%'

You can run this query without params or with 1 or 2 params. If you
clear the params, the "%%" placeholders will always return allrows.


Uli

Uli Becker wrote:

Another approach would be to use the same dataset with or without params:

SELECT * from MyTable
WHERE UserID = {UserID=1}
AND Field1 like '%'+{Field1=''}+'%'
AND "Field2" like '%'+{Field2=''}+'%'

You can run this query without params or with 1 or 2 params. If you
clear the params, the "%%" placeholders will always return allrows.

-----

Here is my sql statement for my dataset.  I am using Firebird 2.5 with their ODBC driver so I had to replace the plus signs with double pipes to eliminate one error.  Firebird also doesn't like the wildcards surrounded by single quotes.  When I run this I get the error in the attached screen capture:

SELECT * FROM incidents WHERE (location_type LIKE %||{location_type=''}||%) AND (incident_date
BETWEEN %||{incident_date=''}||% AND %||{incident_date=''}||%);

Thanks,
TD

 Field1 like '%'+{Field1=''}+'%'

works with string fields only.

In your case I's suggest to use a different dataset with matching params.

Uli

Uli Becker wrote:

 Field1 like '%'+{Field1=''}+'%'

works with string fields only.

In your case I's suggest to use a different dataset with matching params.

Ul

Thanks Uli,
TDi