"_" SQL jolly character doesn't function in C# DataView.RowFilter set

[polocar]

Hello,
I'm writing a C# program that interfaces with an Access database.
The database is a simple table with 2 fields: "IDPerson" (Counter field) and "NamePerson" (Text field).
In this table there are 3 records:
1) IDPerson = 1, NamePerson = "Bill"
2) IDPerson = 2, NamePerson = "Robert"
3) IDPerson = 3, NamePerson = "Tom"

After some tests I noticed that, if I create a DataView object and I use the "_" SQL jolly character to set its RowFilter property, the program doesn't function as expected:

DataView dvPersonsFiltered = new DataView();
dvPersonsFiltered.Table = dtPersons;
dvPersonsFiltered.RowFilter = "NamePerson like '_ill'";
dvPersonsFiltered.Sort = "IDPerson ASC";

int numPersonsFiltered = dvPersons.Count;

(search all the names that have "ill" string after the first generic character, and then end).

If I put a breakpoint after the numPersonsFiltered setting statement, the result is numPersonsFiltered = 0.

The strange thing is that the other SQL jolly character "%" functions correctly (but of course it has a different meaning respect to "_").

DataView dvPersonsFiltered = new DataView();
dvPersonsFiltered.Table = dtPersons;
dvPersonsFiltered.RowFilter = "NamePerson like '%ill'";
dvPersonsFiltered.Sort = "IDPerson ASC";

int numPersonsFiltered = dvPersons.Count;

(search all the names that end with "ill" string).

This time numPersons filtered = 1.

Do you know the reason of that?
Is there in C# a way to obtain a dvPersons.RowFilter setting with first character set as generic and then the "ill" string?

I could use the statement:

dvPersons.RowFilter = "NamePerson = 'Aill' OR NamePerson = 'Bill' OR ... OR NamePerson = 'Zill'";

but it would be awful, wouldn't it?

Thank you very much

 

[JurkMonkey]

umm... you're trying to use a wild card? Doesn't * work?

 

[chiph]

Asterisk (*) is the wildcard for Access databases. It's not ANSI-SQL compliant, but it's what Microsoft chose to use instead of the percent-sign (%) that everyone wishes they had used.

Chip H.


____________________________________________________________________
Donate to Katrina relief:

http://s1.amazon.com/paypage/PELYGQVJ8Q7IB/103-6821258-5919825

If you want to get the best response to a question, please read FAQ222-2244 first

 

[polocar]

What I have noticed in my tests is that both "%" and "*" jolly characters function correctly in the DataView.RowFilter setting statement, but the problem is that they are equivalent:

dvPersonsFiltered.RowFilter = "NamePerson like '%ill'";
dvPersonsFiltered.RowFilter = "NamePerson like '*ill'";

means that I want all the persons that have a generic string followed by "ill" string.

The "_" jolly character indicates 1 generic character, so that:

dvPersonsFiltered.RowFilter = "NamePerson like '_ill'";

means that I want all the persons that have a generic first character (not a generic string) followed by "ill" string.
The problem is that this statement doesn't function ("_" is considered a normal character, not a jolly one).

Another strange thing is that I tried the following code:

cn.Open();
OleDbCommand cmd = cn.CreateCommand();
cmd.CommandText = "UPDATE Persons SET NamePerson = 'Fill' WHERE NamePerson like '_ill'";
int numRows = cmd.ExecuteNonQuery();
cn.Close();

and the result is that numRows = 1 (correct!!!)
So Visual C# considers "_" as a SQL jolly character, but not when it is inserted in a RowFilter setting statement (I don't know why...)