none
DataView.RowFilter cannot find rows. RRS feed

  • Question

  • Hi!

    Why in sample below DataView is empty ? In table is row " a a " and filter is also the same but DataView is empty.

    			DataSet1 ds = new DataSet1();
    			ds.DataTable1.AddDataTable1Row(" a a ");
    			ds.AcceptChanges();
    
    			DataView view = new DataView(ds.DataTable1);
    			view.RowFilter = string.Format("Name LIKE '%{0}%'", " a a ");
    			Console.WriteLine(view.Count.ToString());
    
    			Console.ReadKey();


    Regards!
    Monday, November 9, 2009 8:32 AM

All replies

  • If you check RowFilter property of the view after you assigned filter string to it, do you see the same, expected filter string or not?
    Val Mazur (MVP) http://www.xporttools.net
    Monday, November 9, 2009 11:24 AM
    Moderator
  • From:
    "LIKE (Transact-SQL)"
    http://msdn.microsoft.com/en-us/library/ms179859.aspx

    (I believe the same rules apply for RowFilter and LIKE)

    "When you perform string comparisons by using LIKE, all characters in the pattern string are significant. This includes leading or trailing spaces. If a comparison in a query is to return all rows with a string LIKE 'abc ' (abc followed by a single space), a row in which the value of that column is abc (abc without a space) is not returned. However, trailing blanks, in the expression to which the pattern is matched, are ignored. If a comparison in a query is to return all rows with the string LIKE 'abc' (abc without a space), all rows that start with abc and have zero or more trailing blanks are returned."

    This gives that:

    dw.RowFilter = String.Format("name LIKE '%{0}%'", " a a ");
    will fail, but

    dw.RowFilter = String.Format("name LIKE '%{0}%'", " a a");
    will work.

    HTH
    //Michael
    This posting is provided "AS IS" with no warranties.
    • Marked as answer by Yichun_Feng Monday, November 16, 2009 7:30 AM
    • Unmarked as answer by kicaj Monday, March 1, 2010 9:09 PM
    Monday, November 9, 2009 3:15 PM
  • This is a very curious situation. There's a field value with trailing space, a filter value with a trailing space, and no match. According to the response above "like" will return values with zero or more trailing spaces. That's all well and good but you should be able to specifically return values with a minimum of 1 trailing space as in the original example.

    To satisfy my curiosity I checked with Sql Server(10.0.2531.0).

    Create Table tbl_1 (first_name varchar(250));
    INSERT INTO tbl_1(first_name) values('test '); /*Space at the end*/
    SELECT first_name FROM tbl_1 WHERE first_name LIKE '%st%'; /*No space at the end, wildcard at the end*/
    SELECT first_name FROM tbl_1 WHERE first_name LIKE '%st %'; /*Space at the end followed by wildcard*/
    SELECT first_name FROM tbl_1 WHERE first_name LIKE '%st% '; /*Space at the end after the wildcard*/
    SELECT first_name FROM tbl_1 WHERE first_name LIKE '%st'; /*No space at the end, no wildcard at the end*/

    The row that was created in the second statement will be returned by every SELECT statement. Unfortunately, the rowfilter only works for some of them. Isn't this a bug?

    -Ray

    Tuesday, November 10, 2009 8:52 AM
  • I don't know why this was marked as answer... It's not accurate. Just test it in MSSQL server like I did. In MSSQL filter criteria CAN have a space at the end and in the DataView.RowFilter you cannot. In the RowFilter having a space at the end returns an empty set regardless of whether or not there is data that actually does match the string that is set to the RowFilter property.

    Is this a bug?

    Monday, November 16, 2009 10:17 PM
  • I'd say it's a bug, but what you describe isn't totally accurate.

    If I have a row containing " a a " and a row containing " a a bc", and my filter is LIKE '% a a %', it *will* return the row with the " a a bc", but not the row with just " a a " ... which I think should be returned also. Sounds like a bug to me. =0(
    ~~Bonnie Berent [C# MVP]

    geek-goddess-bonnie.blogspot.com
    Tuesday, November 17, 2009 5:57 AM
  • yes, i agree with most of the solutions and workaround. this may be a bug with rowFilter(). and when i checked the length of result string it was 23

    view[0].ToString().Length;

    Sunday, October 21, 2012 1:48 PM
  •  this may be a bug with rowFilter(). and when i checked the length of result string it was 23

    That statement is pretty irrelevant if you ask me. Why would the length of a string have anything to do with this thread?!?!?!


    ~~Bonnie Berent DeWitt [C# MVP]

    geek-goddess-bonnie.blogspot.com

    Sunday, October 21, 2012 2:15 PM