none
Data Table - Filtering RRS feed

  • Question

  • Hi,

    When working in DataTable i have faced some wrong things Filtering.

    Here I have attached my code snippet

    DataTable dt = new DataTable();

                dt.Columns.Add("CustomerID");

                dt.Columns.Add("CustomerName");

                dt.Columns.Add("ProductID");

                dt.Columns.Add("ProductName");

                dt.Columns.Add("ProductPrice");

                dt.Columns.Add("Quantity");

                dt.Columns.Add("IsSelected"typeof(bool));

                dt.Rows.Add(1, "Thomas",  44,  "Reebok Shoes",  200,  4,null);

                dt.Rows.Add(2, "Nestor"null"Adidas Shoes", 150, 5, null);

                dt.Rows.Add(3, "Vincent"null"Nokia Mobile", 50, 6, null);

                dt.Rows.Add(4, null, 40, null, 65, 12, null);

                dt.Rows.Add(5, "Peter", 32, "LG Mobile"null, 3, null);

                dt.Rows.Add(6, "Vincent",29, "Basics Shirt", 25,20,null);

                dt.Rows.Add(7, "David", 35, "Raymond Shirt"null, 15, null);

                dt.Rows.Add( 8, "Peterson",12, "Clientage Shirt",20, 13,null);

                dt.Rows.Add(9, null, 76, "Sony Mobile", 989, 54, null);

                dt.Rows.Add(10, "Nestor",98, "Samsung camera",  989,  09,null);

                DataTable newdt = dt;

                DataView dv = new DataView(newdt);

                dv.RowFilter = "[ProductID] <> '12'";

                PrintTableOrView(dv);

    //

      private void PrintTableOrView(DataView dv1)

            {

                for (int i = 0; i < dv1.Count; i++)

                {

                    Console.WriteLine("\t''" + dv1[i]["ProductID"]);

                }

                Console.WriteLine();

            }

     

    In the above code snippet i have set the filter as ProductID notequals to 12 and pass as RowFilter value.

    Output I have got:

    ''44

    ''40

    ''32

    ''29

    ''35

    ''76

    ''98

    The null values are not getting printed. How to overcome this? I have tried to filter only null values by

    “IsNull(ProductID, 'Null Column')='Null Column'”. Its works fine.

    Thanks,

    Ravi


    ravi

    Monday, January 7, 2013 11:41 AM

Answers

  • Hello,

    In your filter condition, try following code

    dv.RowFilter = "ISNULL([ProductID],0) <> '12'";
    HTH


    please Mark as the Answer, If this answers your question. If this post is helpful, please vote as helpful.

    • Marked as answer by Ravi04 Tuesday, January 8, 2013 4:30 AM
    Monday, January 7, 2013 12:01 PM

All replies

  • Hello,

    In your filter condition, try following code

    dv.RowFilter = "ISNULL([ProductID],0) <> '12'";
    HTH


    please Mark as the Answer, If this answers your question. If this post is helpful, please vote as helpful.

    • Marked as answer by Ravi04 Tuesday, January 8, 2013 4:30 AM
    Monday, January 7, 2013 12:01 PM
  • Ravi :

    Product ID is null ?  does this help  ?

        DataTable newDt = dt.AsEnumerable().Where( x=> ( x["ProductID"] != System.DBNull.Value && Convert.ToInt32(x["ProductID"]) != 12) ).CopyToDataTable<DataRow>();
                DataTable nullDt = dt.AsEnumerable().Where(x => x["ProductID"] == System.DBNull.Value ).CopyToDataTable<DataRow>();
                newDt.Merge(nullDt);
    Monday, January 7, 2013 4:00 PM
  • You shouldn't use .net 'null' with DataTable, use DBNull.Value instead. It would be understood by ISNULL function, so you can write "ISNULL(ProductID,0) <> 12" or "ProductID IS NULL OR ProductID <> 12" then.
    • Marked as answer by Ravi04 Tuesday, January 8, 2013 4:30 AM
    • Unmarked as answer by Ravi04 Tuesday, January 8, 2013 6:48 AM
    Monday, January 7, 2013 5:53 PM
  • Thanks exactly what i need.

    -Ravi


    ravi

    Tuesday, January 8, 2013 7:27 AM
  • Hi,

    please help how to check the double value as notequal to NULLCOLUMN, what string to assign in dv.RowFilter?

    Regards,

    Ravi


    ravi

    Thursday, January 10, 2013 1:08 PM