Question about DataTable.Compute and SUM

2016-10-24

Question

_{Monday, October 24, 2016 12:11 PM}

Hi all,

I have a DataTable with some DECIMAL columns in it, and I'm using COMPUTE to get the SUM for those columns like this:

 Dim sum_Total As Decimal = BegrTable.Compute("SUM(Total)", BegrTable.DefaultView.RowFilter)

But in my DataTable, those columns can have a NULL value like this:

Column_a Column_b Total

5 7 7

8 3 8

6 4 NULL

7 2 NULL

If I'm running the code abobe, I don't get an error even if the TOTAL column on Row 3, and 4 have a NULL value. So SUm_Total becomes : 15

But, if the Total column on the first row has a NULL value like this:

Column_a Column_b Total

5 7 NULL

8 3 8

6 4 9

I get an error:

Additional information: Conversion from type 'DBNull' to type 'Decimal' is not valid.

So why does this error occur. Because the SUM SUMM's all the Total columns even if they are NULL, but the Total column on the first row has to start with a real decimal value, but if the first Total column is NULL, an error occurs.

Regards,

Mark

All replies (14)

_{Monday, October 24, 2016 12:38 PM ✅Answered}

You can use LINQ or Lambda to disregard null values

Dim dt As New DataTable
dt.Columns.Add(New DataColumn With {.ColumnName = "C1", .DataType = GetType(Decimal)})
dt.Columns.Add(New DataColumn With {.ColumnName = "C2", .DataType = GetType(Decimal)})
dt.Rows.Add(New Object() {1})
dt.Rows.Add(New Object() {2, 3.5D})
dt.Rows.Add(New Object() {3, 0.5D})
dt.Rows.Add(New Object() {3})

Dim sumResult As Decimal = dt.AsEnumerable _
    .Where(Function(row) Not IsDBNull(row.Item(1))) _
    .Select(Function(row) row.Field(Of Decimal)("C2")) _
    .Sum

MessageBox.Show(sumResult.ToString)

Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
VB Forums - moderator

_{Monday, October 24, 2016 5:06 PM ✅Answered}

Try this out

Dim dt As New DataTable
dt.Columns.Add(New DataColumn With {.ColumnName = "C1", .DataType = GetType(Decimal)})
dt.Columns.Add(New DataColumn With {.ColumnName = "C2", .DataType = GetType(Decimal)})
dt.Rows.Add(New Object() {1})
dt.Rows.Add(New Object() {2})
dt.Rows.Add(New Object() {3})
dt.Rows.Add(New Object() {3})

Dim sum As Object = dt.Compute("Sum(C2)", "C2 IS NOT NULL")
If sum IsNot DBNull.Value Then
    Console.WriteLine(CDec(sum))
Else
    Console.WriteLine("Nothing to sum")
End If

_{Monday, October 24, 2016 1:05 PM}

Hi Karen,

Thanks for the reply!

I'm going to implement your code in my code, but I still don't understand why I get an error in my code if the first Total column is NULL.

regards,

Mark

_{Monday, October 24, 2016 1:09 PM}

Are you sure you are talking about a Null value and not a whitespace?

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim dt As New DataTable
        dt.Columns.Add(New DataColumn("C1", GetType(System.Decimal)))
        dt.Columns.Add(New DataColumn("C2", GetType(System.Decimal)))
        dt.Rows.Add(New Object() {1, DBNull.Value})
        dt.Rows.Add(New Object() {2, 3.5D})
        dt.Rows.Add(New Object() {3, 0.5D})
        dt.Rows.Add(New Object() {3})
        TextBox2.Text = dt.Compute("sum(C2)", Nothing).ToString
    End Sub

No problem at all

Success
Cor

_{Monday, October 24, 2016 1:21 PM}

Also you can use

Dim sum As Decimal = dt.Compute("Sum(C2)", "C2 IS NOT NULL")

_{Monday, October 24, 2016 1:28 PM}

Mark,

Could you post how you populate your datatable in the first place?

I could not reproduce the described problem with the following code:

Public BegrTable As DataTable
    Public BegrTable2 As DataTable

    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
        'Creating Datatable
        BegrTable = New DataTable
        'Creating Columns
        BegrTable.Columns.Add(New DataColumn With {.ColumnName = "Column_a", .DataType = GetType(Decimal)})
        BegrTable.Columns.Add(New DataColumn With {.ColumnName = "Column_b", .DataType = GetType(Decimal)})
        BegrTable.Columns.Add(New DataColumn With {.ColumnName = "Total", .DataType = GetType(Decimal)})

        'Adding data in rows
        BegrTable.Rows.Add(New Object() {5, 7, 7})
        BegrTable.Rows.Add(New Object() {8, 3, 8})
        BegrTable.Rows.Add(New Object() {6, 4})
        BegrTable.Rows.Add(New Object() {7, 2})
        'Adding the table to the datatgridview
        DataGridView1.DataSource = BegrTable

        'Creating Datatable
        BegrTable2 = New DataTable
        'Creating Columns
        BegrTable2.Columns.Add(New DataColumn With {.ColumnName = "Column_a", .DataType = GetType(Decimal)})
        BegrTable2.Columns.Add(New DataColumn With {.ColumnName = "Column_b", .DataType = GetType(Decimal)})
        BegrTable2.Columns.Add(New DataColumn With {.ColumnName = "Total", .DataType = GetType(Decimal)})

        'Adding data in rows
        BegrTable2.Rows.Add(New Object() {5, 7})
        BegrTable2.Rows.Add(New Object() {8, 3, 8})
        BegrTable2.Rows.Add(New Object() {6, 4, 9})
        BegrTable2.Rows.Add(New Object() {7, 2})
        'Adding the table to the datatgridview
        DataGridView2.DataSource = BegrTable2


    End Sub

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

        Dim sum_Total As Decimal = BegrTable.Compute("SUM(Total)", BegrTable.DefaultView.RowFilter)
        RichTextBox1.AppendText("BegrTable  Sum(Total) =" & sum_Total & Environment.NewLine)
    End Sub

    Private Sub Button2_Click(sender As Object, e As EventArgs) Handles Button2.Click
        Dim sum_Total As Decimal = BegrTable2.Compute("SUM(Total)", BegrTable2.DefaultView.RowFilter)
        RichTextBox1.AppendText("BegrTable2 Sum(Total) =" & sum_Total & Environment.NewLine)
    End Sub

_{Monday, October 24, 2016 2:08 PM}

Hi Cor,

Thanks for the reply. I made a mistake in my code, BTW the values for the Total column in my DataTable where NULL values on some rows.

Dim sum_Total As Decimal = BegrTable.Compute("SUM(Total)", BegrTable.DefaultView.RowFilter)

My Rowfilter was wrong. It didn't pull out the NULL values.

Got this one from Karen:

Dim sum As Decimal = dt.Compute("Sum(C2)", "C2 IS NOT NULL")

Pulls out the NULL values and computes (SUMS) the right Total

Regards,

Mark

_{Monday, October 24, 2016 2:13 PM}

Hi Karen

This one is even better, I made a mistake with the ROWFILTER

Dim sum_Total As Decimal = BegrTable.Compute("SUM(Total)", BegrTable.DefaultView.RowFilter)

There was no ROWFILTER so the NULL values causes the error.

Regards,

Mark

_{Monday, October 24, 2016 2:17 PM}

Hi Cyrille,

Thanks for the reply.

I made a mistake in my code. The Total column in my table can contain NULL values on some rows. While calculating the column, I did not filter on a NULL value like this:

Dim sum As Decimal = dt.Compute("Sum(C2)", "C2 IS NOT NULL")

Regards,

Mark

_{Monday, October 24, 2016 4:15 PM}

Hi Karen,

I marked your solution as an answer to early. I still have a problem. Let's say I have a Datatable dt with one Column decimal(8,2) and the Column name is 'Number' and the table is 10 rows. ALL the rows have a NULL value. If I use this code:

Dim sum As Decimal = dt.Compute("Sum(Number)", "Number IS NOT NULL")

I get an error: Conversion from type 'DBNull' to type 'Decimal' is not valid.

But if ONE row contains a real value let's say 23, the code produces no error. So if one or more rows contains a value there is no problem, but if NONE of the rows contains a value so NULL, I'll get the error described above.

Regards.

Mark

_{Monday, October 24, 2016 4:35 PM}

Mark,

Be aware that using the word NULL to describe something in VB is very confusing.

NULL in VB can be:

The content of a column in a SQL table which is NULL's allowed (in vb is that DBNull.Value)
A strange use of an unassigned identifier, in VB that has the name Nothing
A strange kind of value type which serves almost nothing but was created for a kind of parity with C#.

The DataTable is originally a represention of the Table in a DataBase therefor "Null" was always the DBNull.Value

Success
Cor

_{Monday, October 24, 2016 4:55 PM}

Hi Cor,

I marked Karen's solution as an answer to early. I still have a problem. Let's say I have a DataTable BegrTable with one Column Decimal(8,2) and the Column name is 'Number' and the table is 10 rows. ALL the rows have a NULL value. If I use this code:

Dim sum As Decimal = BegrTable.Compute("Sum(Number)", "Number IS NOT NULL")

I get an error: Conversion from type 'DBNull' to type 'Decimal' is not valid.

I tried your and Karen's code, just in a new project, and indeed there is no problem at all. The only thing I did was implementing your examples in my project as shown above

Regards.

Mark

_{Monday, October 24, 2016 5:17 PM}

Hi Cor,

I marked Karen's solution as an answer to early. I still have a problem. Let's say I have a DataTable BegrTable with one Column Decimal(8,2) and the Column name is 'Number' and the table is 10 rows. ALL the rows have a NULL value. If I use this code:
Dim sum As Decimal = BegrTable.Compute("Sum(Number)", "Number IS NOT NULL")
I get an error: Conversion from type 'DBNull' to type 'Decimal' is not valid.

But if ONE row contains a real value let's say 23, the code produces no error. So if one or more rows contains a value there is no problem, but if NONE of the rows contains a value so NULL, I'll get the error described above.

I tried your and Karen's code, just in a new project, and indeed there is no problem at all. The only thing I did was implementing your examples in my project as shown above

Regards.

Mark

Mark,

So far as I see, it looks to me like you're using the wrong thing here.

If this were a class, you'd never had dealt with this issue at all. If it needs to be nullable, then set it up as a class with fields which are Nullable(Of Decimal) but otherwise, set the fields up as Decimal.

From there you'll need a collection of them and some methods to work with the collections of data -- like dealing with sum, count, minimum, maximum, and anything else you need to do.

If you absolutely must have a DataTable then let it be the result (of a function), not the basis of the data itself.

For what it's worth...

"Everybody in this country should learn how to program a computer... because it teaches you how to think." (Steve Jobs)

_{Monday, October 24, 2016 8:07 PM}

Hi Karen,

After extensive testing with a lot of columns and NULL values, your latest piece of code does the trick, even if all values of a particular column in a DataTable are NULL.

Many thanks for helping me out!

Regards,

Mark

שתף באמצעות

Question about DataTable.Compute and SUM

Question

All replies (14)

משאבים נוספים