Help with Filter on BindingSource

[Troy Bull]

Greetings

I have a simple application 2 tables related via foreign key. Table 1
Parts, table 2 Clients. ClientID is the primary key in the Client
Table, clientID is a foreign key in the Parts table. I have a simple
form that displays "parts" rows one at a time. I want to do the
following on my partsBindingSource

partsBindingSource.Filter = "client_id in (select clientId from clients
where (name like 'KI%'))";

It blows up. Is there a way to get this same functionality?
Thanks
Troy

 

[Nicholas Paldino [.NET/C# MVP]]

Troy,

Assuming that you are using a BindingSource which attaches to a
DataSet/DataTable, etc, etc, you are limited to the syntax of what is
allowed in the Expression property of the DataColumn class.

If that is the case, you can reference child with the child keyword. I
^think^ that for what you want (assuming you only have one relation on the
table) you would do something like:

child.name like 'KI%'

If you are bound to another collection (which has to implement
IBindingListView interface) then you need to abide by the syntax that is
used for the Filter property on that implementation.

 

[Troy Bull]

Troy,

Assuming that you are using a BindingSource which attaches to a
DataSet/DataTable, etc, etc, you are limited to the syntax of what is
allowed in the Expression property of the DataColumn class.

If that is the case, you can reference child with the child keyword.
I ^think^ that for what you want (assuming you only have one relation on
the table) you would do something like:

child.name like 'KI%'

Thanks for the tip, but this was the first thing I tried and it doesnt
work. What I ended up doing was basically the following (this is
pseudo code):

using System.Data.SqlClient;

open sql connection
create sqlcommand
command text = select clientId from client where name like 'KI%'
execute command returning a reader
comma = ""
while (reader.Read())
instring = instring + comma + currentclientID
comma = ","
end while

partsBindingSource.filter = "client_id in (" + instring + ")";

this seems really inefficient but it works. Also, this uses "raw" sql
commands to the database where it seems like I should be able to do all
this with the binding source..

 

[Troy Bull]

Troy,

Assuming that you are using a BindingSource which attaches to a
DataSet/DataTable, etc, etc, you are limited to the syntax of what is
allowed in the Expression property of the DataColumn class.

If that is the case, you can reference child with the child keyword.
I ^think^ that for what you want (assuming you only have one relation on
the table) you would do something like:

child.name like 'KI%'

Thanks for the tip, but this was the first thing I tried and it doesn't
work. What I ended up doing was basically the following (this is
pseudo code):

using System.Data.SqlClient;

open sql connection
create sqlcommand
command text = select clientId from client where name like 'KI%'
execute command returning a reader
comma = ""
while (reader.Read())
instring = instring + comma + currentclientID
comma = ","
end while

partsBindingSource.filter = "client_id in (" + instring + ")";

this seems really inefficient but it works. Also, this uses "raw" sql
commands to the database where it seems like I should be able to do all
this with the binding source..

 

[Nicholas Paldino [.NET/C# MVP]]

I wasn't sure that it was going to work, as the documentation on the
Expression property on the DataColumn class doesn't specify how it applies
to the Filter property.

Did you make sure to have a DataRelation set up between the tables?

As a side note, the filter property is not meant to emulate sql. Since
the DataSet can handle data from many different sources, each with its own
query syntax, it's doesn't make sense to have the filter syntax be modelled
after SQL.

Also you shouldn't have to select against the database if you have the
contents of the Client table in memory. You should just be able to filter
on that table, and get the results from that (use a DataView on the
DataTable containing the clients).