none
DataView.RowFilter() - Exclude Rows Containing Text in a Numeric Filter

    Question

  • Here is my current scenario: I have DataGridView with a DataTable as it's DataSource (filled via DataAdapter(sql stored proc)); i then have logic that will change cells with certain values in certain columns to ComboBoxCells if criteria is met, what this now means is that a column will have numeric values (typeof(string) in table layout) as well as text (also typeof(string)). After all of this is done, i build a form that is used to perform text/numeric filtering on the DataGridView; in which case i cast the DataTable (DataSource) as a DataView to then use DataView.RowFilter().

    As stated, the column holding the mixed numeric/string values is typeof(string); through testing, i've found that using comparative operators (<, >, =, etc...) will work on 'numbers' with string types using DataView.RowFilter()...my issue comes into play with two scenarios, first being when there is a blank or empty string (i believe NULL in database) in the field/column i'm filtering against; second when i use the ComboBoxCell to populate a cell with a string (both scenarios leading to a comparative operator on a true string); both of these scenarios lead to the 'Cannot perform comparative operation on system.string and system.int32' error.

    Essentially, i need to ignore these 'problem' records when applying the DataView.RowFilter, then choose whether or not to add them back in once the filtering has completed.

    The most common 'solution' found through my searching has lead to wrapping the value in apostrophes, which gets rid of the error, but leads to innacurate filtering of numeric values (not acceptable, and not sure why either).

    The only workaround I can think of at the moment is to clone the original table and fill it only with the rows that do not contain the empty string or ComboBoxCell; filter the new table and set it as the new datasource, adding back the excluded rows after the filtering is complete (if required). My main concern with this method is that the average set of data i'm working with will result in 6k-7k records...i have a feeling this would slow down the application significantly and likely includes a lot of unneccesary overhead.

    I realize that this is a long-winded description and question, but I dont want to waste anyone's time due to lack of clarity on my end. I'm looking for an alternative to my proposed solution, or some direction to better approaches/best practices when dealing with scenarios similar to this. Thank you for your time in advance.

    Monday, November 07, 2011 8:51 PM

Answers

  • This has not necessarily been solved; however I thought it best to share the workaround i decided to move forward with for the time being, just in case anyone was following or has a similar task.

    In the database, there are many tables that feed this tool; one of which lists field names and a few attribues. I threw a flag field into the db letting me know whether or not a datagridviewcolumn would need to be treated as 'special' (hold numbers as text + comboboxcells; plus be filtered as number). If i see this flag upon grabbing the data, i create a helper column for this field in which i replace all true text (comboboxcells or empty strings) with 0,-1,-n; this column is not visible, but is the column used for filtering/sorting when the user calls a filter function.

    Thanks again for all of your suggestions and help.

     

    Friday, November 11, 2011 4:05 PM

All replies

  • EMB91909,

    Did you consider dealing with null values in your stored procedure? I mean you can replace your null values that would be passed into your app on the server side. Just modify your stored proc accordingly. For example: CASE WHEN FIELD1 IS NULL THEN '9999' ELSE FIELD1 END AS TESTCOLUMN (something along these lines).

    As for the second scenario: What values are available in the ComboBoxCells?

    Maybe you can share the stored procedure and the code you use to filter the DataGrid view?

     

    Tuesday, November 08, 2011 3:04 AM
  • Can you show the problem code here and the error message?

    That will help others to understand better I bet.

    As the same time you can also revisit http://msdn.microsoft.com/en-us/library/bb669073.aspx.

    chanmm


    chanmm
    Tuesday, November 08, 2011 3:08 AM
  • Hi EMB91909,

    DataView.RowFilter Property provides the expression syntax to filter out the null values first.
    For example, if you want to filter the results which fulfill the condition Col1>10, you can write like this:
    dataView.RowFilter = "Isnull(Col1, 0) > 10";

    Please check out the official description of the DataView.RowFilter Property:
    http://msdn.microsoft.com/en-us/library/system.data.dataview.rowfilter.aspx.

    Have a nice day,

    Leo Liu [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, November 08, 2011 9:41 AM
    Moderator
  • I'll start from the top:

    Toby.NY

    "Did you consider dealing with null values in your stored procedure?..."

    Not until you mentioned it, however i believe the proposed "isnull(field,0)" will take care of that now.

    "As for the second scenario: What values are available in the ComboBoxCells?..."

    This is where i feel the real issue will come into play; they are all text statements...for example, "Do this" or "Do that". These do not come from the proc themselves, but are added at runtime based on what the proc brings back. Records with comboboxcell updates will be stored elsewhere and never really 'touch' the original data.

    Chanm m

    "Can you show the problem code here and the error message?"

    Here is a small sample of where the problem occurs...

       case ">":
            txtFilters += "Isnull(" + f.Field + ",0)>" + f.FieldValue;                            
            break;
    }
    dv.RowFilter = txtFilters;
    

    where f.Field is the column/field and f.FieldValue is the value being applied in the filter: example(Revenue>0)

    The exact error will show as "Cannot perform '>' operation on System.String and System.Int32." as the field has numbers (stored a strings, but evaluated as numbers) and strings (see comboboxcell explanation above).

    Leo Liu

    "if you want to filter the results which fulfill the condition Col1>10, you can write like this:
    dataView.RowFilter = "Isnull(Col1, 0) > 10";"

    Thanks for the advice, and as you can see in the code sample above i've now taken your suggestion. However, as stated in my response to Toby.NY, the comboboxcells will hold nothing but text which will still bring out the error...it seems i almost need something like IsText or !Isnumber. That said, i've been unable to find this as it relates to the rowfilter property. 

     

     

     

     

    Tuesday, November 08, 2011 1:42 PM
  • As stated in your prior post:
    >> As stated, the column holding the mixed numeric/string values is typeof(string); through testing, i've found that using comparative operators (<, >, =, etc...) will work on 'numbers' with string types using DataView.RowFilter()...my issue comes into play with two scenarios, first being when there is a blank or empty string (i believe NULL in database) in the field/column i'm filtering against; second when i use the ComboBoxCell to populate a cell with a string (both scenarios leading to a comparative operator on a true string); both of these scenarios lead to the 'Cannot perform comparative operation on system.string and system.int32' error.
    Should I interprete as: This column is of type string (varchar(n) in the database maybe) but has three kind of datas: pure numbers, word(s) that are not numbers, and null values? the column holding the mixed numeric/string values means that the second kind may be a mixed string of word(s) and numbers?
    If so you should filter out the records which have only pure numbers stored in this column, in the DataTable, then fill the DataView with the DataTable and assign "Col1> 10" to dv.RowFilter.

    Have a nice day,

    Leo Liu [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Wednesday, November 09, 2011 7:38 AM
    Moderator
  • Leo,

       Thanks for the reply, let me clarify a little further. The data type in the database is actually Decimal; however, part of the goal or requirement is that I can have numeric data shown with text data in the same column in this application...so my solution to allow for text data to be entered into a column that was originally a decimal type is to clone the original table (filled via DataAdapter.Fill(DataSet)), then change the type of the desired 'mixed' columns to typeof(system.string), then inserting the rows into the new cloned table. What this does is allow me to override cells with certain values (just use 0 for example) with ComboBoxCells holding desired phrases, into the same DataTable column as the numbers that did not meet the override criteria. When pushing data back to the database, these ComboBoxCells are handled first, being replaced with a numeric representation of the text that was chosen from the dropdown. So to clarify even further, the ComboBoxCell functionality is purely visual, in that the text represented never gets pushed back to the database, but an ID paired with that phrase does.

    for example, if I were to illustrate the original column that is pulled back from the database it would look something like this: {5;3;100;6;0;14;0;1;2;88;0}

    If i were to illustrate what the new column looks like in the cloned table, after the ComboBoxCell logic was implemented, it would look something like this: {'5';'3';'100';'6';ComboBoxCell;'14';ComboBoxCell;'1';'2';'88';ComboBoxCell} in which the ComboBoxCell would have words available, like "Get This" or "Dont Get That". This is where my filtering challenge begins, as i need to allow the user to filter on the numeric cells while choosing whether or not to include the ComboBoxCells in the result set.

    Hope this helps with clarification. Let me know what you think. Thanks again.

     

     


    • Edited by EMB91909 Wednesday, November 09, 2011 10:07 AM
    Wednesday, November 09, 2011 10:06 AM
  • This has not necessarily been solved; however I thought it best to share the workaround i decided to move forward with for the time being, just in case anyone was following or has a similar task.

    In the database, there are many tables that feed this tool; one of which lists field names and a few attribues. I threw a flag field into the db letting me know whether or not a datagridviewcolumn would need to be treated as 'special' (hold numbers as text + comboboxcells; plus be filtered as number). If i see this flag upon grabbing the data, i create a helper column for this field in which i replace all true text (comboboxcells or empty strings) with 0,-1,-n; this column is not visible, but is the column used for filtering/sorting when the user calls a filter function.

    Thanks again for all of your suggestions and help.

     

    Friday, November 11, 2011 4:05 PM
  • Hi EMB91909,

    Really good catch!
    Thanks for sharing this idea to our friends.
    Now could you mark your last reply and possibly other useful replies as answers?

    Have a nice weekend,


    Leo Liu [MSFT]
    MSDN Community Support | Feedback to us

    Sunday, November 13, 2011 8:54 AM
    Moderator
  • Hello Emb91909.

    Be aware that one of the reasons to create Linq was to get a good substitution for the a little bit quick and dirty approach in the Expression.

    So if you have something which won't go with Ado.Net Expressions (the rowfilter uses that) then take that one.

     http://msdn.microsoft.com/en-us/library/bb386977.aspx


    Success
    Cor
    Sunday, November 13, 2011 9:47 AM