How to perform a case insensitive filter when the DataTable has CaseSensitive=True
I have a grid bound to a DataView. The underlying DataTable has case sensitivity turned on because the SQL 2005 database uses the Latin1_General_BIN (case sensitive) collation.
When I build a RowFilter expression using LIKE, it is treated as case sensitive, but I want to be able to do case insensitive filtering. I experimented using a ">=" AND "<" expression, but then I lose wild card capability and I can't use that approach if I want to find all rows with data greater than (or less than, etc) a specified string.
Any suggestions will be appreciated. Thanks in advance.
[620 byte] By [
JohnnieK] at [2007-12-24]
I revisited that and I could avoid the exception by turning off AllowAddNew, AllowDelete and AllowUpdate before changing CaseSensitive. But still a simple "RowFilter=[Description] LIKE 't*'" will match entries beginning with "t" but not "T".
Odd, the docs for the DataColumn.Expression property state....
To concatenate a string, use the + character. The value of the CaseSensitive property of the DataSet class determines whether string comparisons are case-sensitive. However, you can override that value with the CaseSensitive property of the DataTable class.
If all else fails...you can do something like...
use "Select t.*, Upper(coalesce(t.[Description],'')) as UpperDescription from table1
for selection then filter on the pseudo-field.
Thanks Brad. I'm going to keep fishing. We want to avoid returning to the database and some of our table are big (>30,000 rows with dozens of string columns), so we don't want to create virtual columns if we can help it.
It turns out that the documentation is correct and I misled myself. The problem I had was that in the same routine I would disable case sensitivity, apply the filter, and then re-enable case sensitivity. When I watched what was happening in slow motion (the debugger), I initially had rows with mixed case, but when the case sensitivity was re-applied I lost some of those rows. Thus I had the perception that turning off case sensitivity wasn't working. Now I leave case-sensitivity turned off until it is needed (which in my case is when the user initiates add mode).
