bindingsource.filter syntax

[Crash893]

hi all

I want to run a few different filters on different feilds i have in a bindingsource

i understand how to do filter one COL to match a string but all the examples i have found only have this


i want to know how to filter by date or between dates and use OR's or ANDs to add another filter

right now i have

gridinfo.Filter = "rootcause = '" + comboBox2.Text + "'"; but what if i wanted to filter rootcause to something and User to something else

or rootcuase to something and opendate to between 12/12/07 and 12/12/06

[jmcilhinney]

You basically answered your own question: put ANDs or ORs between each expression.

[Crash893]

what about a between?

[jmcilhinney]

When you read the documentation for the BindingSource.Filter property you'd have seen this:

The accepted syntax depends on the underlying data source. If the underlying data source is a DataSet, DataTable, or DataView, you can specify Boolean expressions using the syntax documented for the DataColumn.Expression property.

When you followed the link to the topic for the DataColumn.Expression property you'd have seen what's valid and what's not. You'd have seen that the BETWEEN operator is not supported, so you'd have realised that you'd have to use two conditions: one with > and one with <.

[Crash893]

is this not a valid search?

gridinfo.Filter = "rootcause LIKE '5'";
or 
gridinfo.Filter = "rootcause LIKE '5*'";

// "Author LIKE 'Va%'"
// "Year_Born > 1940 AND Year_Born < 1948" (no, the BETWEEN operator is not supported)
// "Author LIKE 'O''M%'". Note that the apostrophe is doubled to deal with the "O'Malley" issue.
// "NOT Author IS NULL". This filters (out) rows where the Author column is NULL.
// "Au_ID > 35 AND Author LIKE 'B%' OR Year_Born = 1947

[jmcilhinney]

What data type is the rootcause column? If it's numeric then no, that's not a valid expression. The LIKE operator is for text columns only. Although I've never tried it myself, I'd guess that the syntax provides a way to convert a numeric column to a string and then use the LIKE operator on the result.

[Crash893]

yes that one happens to be numeric but the number

one other thing we are trying is to search on 3/06/* so any thing that happened on 3/06 through through out the years stuff like that

I will continue to search if i find anything ill be sure to post it.

[jmcilhinney]

If you haven't already, take a look at the documentation for the DataColumn.Expression property. It describes the valid syntax for all properties and arguments that accept SQL-style strings. Your date search would be just like the number search: convert the column value to a string first, then use LIKE.

[Crash893]

dates need a #date#

gridinfo.Filter = "category_name LIKE '*" + comboBox1.Text + "*' AND open_date < #" + dateTimePicker1.Value.ToString("MM-dd-yyyy") + " 12:01 AM #" ;


still working on the int portion i will let you all know

[jmcilhinney]

Use the String.Format method. It makes the code much clearer and mistakes easier to detect:

gridinfo.Filter = string.Format("category_name LIKE '*{0}*' AND open_date < #{1:MM/dd/yyyy hh:mm TT}#",
                                comboBox1.Text,
                                dateTimePicker.Value.Date.AddMinutes(1))