locked
DataTable.Compute - selecting NULL values in filter RRS feed

  • Question

  • User-379729683 posted

    I have a datatable I am trying to run calculations on but I am unable to test for NULL in the filter criteria of the DataTable.Compute() method.

    I want to do this: DataTable.Compute("Sum(PopulationCount)", "LastName is null")

    but receive this error:

    object cannot be cast from dbnull to other types

    How to I test for NULL in the filter of DataTable.Compute()?

    Monday, September 22, 2008 4:02 AM

Answers

  • User1716267170 posted

    Hi StopGo,

    Object cannot be cast from DBNull to other types

    First I need to confirm that you could use "xx is null" expression in Compute method. From the message, it may also be caused by the PopulationCount field. Please check it and see whether it has null value. If yes, the error is probably caused by the null value.

    If the problem still exists, please show us the table data in database, and more code.

    Thanks.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 25, 2008 10:27 PM

All replies

  • User-927253188 posted

    Try by 1st filtering in the table and then computing.

     datatable.RowFilter = "LastName IS NULL"

    and then compute.

     

    (Sorry by mistake I clicked on this twice. So same post is posted twice.)

    Monday, September 22, 2008 6:05 AM
  • User-927253188 posted

    Try by 1st filtering in the table and then computing.

     datatable.RowFilter = "LastName IS NULL"

    and then compute.

    Monday, September 22, 2008 6:05 AM
  • User-379729683 posted

    Thanks for your help.  Filtering on the DataView first and then running .Compute() worked but it forces me to use more code than I would need to otherwise because I only want this NULL filter applied in this instance, not when I'm doing my other calculations from the DataTable.  I'm still unclear why I can't do a NULL check directly in the compute method's filter expression as you would with any other filter criteria.

    Monday, September 22, 2008 12:18 PM
  • User1716267170 posted

    Hi StopGo,

    I want to do this: DataTable.Compute("Sum(PopulationCount)", "LastName is null")

    Actually, it can work with null values. To narrow down the confused problem, I suggest you to follow these steps:

    1) Check the database, and ensure there're null values in the LastName field. Please note: null value is different with empty string.

    2) You can bind the DataTable to a representation control e.g. GridView to show the data. In this case, you can check whether it contains the correct data.

    3) You could test your application with empty string like "LastName = ''".

    Thanks.

    Wednesday, September 24, 2008 11:31 PM
  • User-379729683 posted

     Running this code example:

        DataTable.Compute("Sum(PopulationCount)", "LastName is null")

    Returns the following error:

        Object cannot be cast from DBNull to other types

    I know there are null records in the resultset, not just empty strings, so there's no need to bind to a GridView and test for empty string.  I simply want to be able to run my code sample and calc rows from my DataTable with a NULL LastName.  Why is this so difficult?  Why do I have to do a RowFilter on the entire DataTable in order to accomplish this?  It makes no sense.  Why should the filter "LastName IS NULL" behave any differently than "LastName = 'JONES'"?

    Thursday, September 25, 2008 3:10 PM
  • User1716267170 posted

    Hi StopGo,

    Object cannot be cast from DBNull to other types

    First I need to confirm that you could use "xx is null" expression in Compute method. From the message, it may also be caused by the PopulationCount field. Please check it and see whether it has null value. If yes, the error is probably caused by the null value.

    If the problem still exists, please show us the table data in database, and more code.

    Thanks.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, September 25, 2008 10:27 PM