# What is the best way to go when You need to create sum totals when building Data Table

• ### Question

• Hi Good People

I am restarting my project from the begging, And I'm wondering when Building a Data table and using Data Types for summing up columns in the Database Data Table. The columns I am on about is to deal with Money.

But for the Totalling of a column , Should I use Data Type Decimal or Numeric or small money, or Money. or even an Integer.

Kind Regards

Gary

Gary Simpson

Wednesday, June 6, 2018 6:08 PM

• Decimal (Money does not exist in .Net)

Success
Cor

• Marked as answer by Thursday, June 7, 2018 10:40 AM
Wednesday, June 6, 2018 6:51 PM
• Hi

It is suggested that the Decimal data type is best for financial calculations, avoiding some of the inherent  rounding errors that can occur using the Double data type. (when multiplying/dividing)

Here is some code that sets 3 DataTable columns to Decimal, Double and Integer, and Sums each column. The Sum function shows the same exact totals for both the Decimal and Double columns - there are no rounding errors involved.

So, to answer your question. Decimal is likely the best choice for data type. Remember, you can display the value(s) either in a DataGridView or a String (TextBox / Label etc) in any way to suit your needs. I used "\$0.00" in this example.

Form1 with DataGridView1, Label2, Label3 and Label4

Dim dt As New DataTable("Freddy")
Dim r As New Random
With dt

For i As Integer = 0 To 99
Dim samenum As Double = r.NextDouble() * r.Next(5, 9)
Next
End With
DataGridView1.DataSource = dt
Dim s1 As Decimal = CDec(dt.Compute("Sum(Decimal)", Nothing))
Dim s2 As Double = CDbl(dt.Compute("Sum(Double)", Nothing))
Dim s3 As Integer = CInt(dt.Compute("Sum(Integer)", Nothing))

Label2.Text = Format(s1, "\$0.00")
Label3.Text = Format(s2, "\$0.00")
Label4.Text = Format(s3, "\$0.00")
End Sub

Regards Les, Livingston, Scotland

• Marked as answer by Wednesday, June 6, 2018 7:04 PM
Wednesday, June 6, 2018 6:52 PM
• Even though you have a solution but wanted to present this in case it might have value as it will revise totals if cell values change during the course of the app running. This is done via several events. It may be overkill for you but can't hurt to present this alternate solution.

https://code.msdn.microsoft.com/DataGridView-calculating-b38045fa?redir=0

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

• Marked as answer by Thursday, June 7, 2018 10:48 AM
Wednesday, June 6, 2018 11:02 PM

### All replies

• Decimal (Money does not exist in .Net)

Success
Cor

• Marked as answer by Thursday, June 7, 2018 10:40 AM
Wednesday, June 6, 2018 6:51 PM
• Hi

It is suggested that the Decimal data type is best for financial calculations, avoiding some of the inherent  rounding errors that can occur using the Double data type. (when multiplying/dividing)

Here is some code that sets 3 DataTable columns to Decimal, Double and Integer, and Sums each column. The Sum function shows the same exact totals for both the Decimal and Double columns - there are no rounding errors involved.

So, to answer your question. Decimal is likely the best choice for data type. Remember, you can display the value(s) either in a DataGridView or a String (TextBox / Label etc) in any way to suit your needs. I used "\$0.00" in this example.

Form1 with DataGridView1, Label2, Label3 and Label4

Dim dt As New DataTable("Freddy")
Dim r As New Random
With dt

For i As Integer = 0 To 99
Dim samenum As Double = r.NextDouble() * r.Next(5, 9)
Next
End With
DataGridView1.DataSource = dt
Dim s1 As Decimal = CDec(dt.Compute("Sum(Decimal)", Nothing))
Dim s2 As Double = CDbl(dt.Compute("Sum(Double)", Nothing))
Dim s3 As Integer = CInt(dt.Compute("Sum(Integer)", Nothing))

Label2.Text = Format(s1, "\$0.00")
Label3.Text = Format(s2, "\$0.00")
Label4.Text = Format(s3, "\$0.00")
End Sub

Regards Les, Livingston, Scotland

• Marked as answer by Wednesday, June 6, 2018 7:04 PM
Wednesday, June 6, 2018 6:52 PM
• Decimal (Money does not exist in .Net)

Success
Cor

Hi Cor

Thank you for putting me at ease I shall use Decimal.

All the best

Gary

Gary Simpson

• Marked as answer by Wednesday, June 6, 2018 7:04 PM
• Unmarked as answer by Thursday, June 7, 2018 10:40 AM
Wednesday, June 6, 2018 7:01 PM
• Hi

It is suggested that the Decimal data type is best for financial calculations, avoiding some of the inherent  rounding errors that can occur using the Double data type. (when multiplying/dividing)

Here is some code that sets 3 DataTable columns to Decimal, Double and Integer, and Sums each column. The Sum function shows the same exact totals for both the Decimal and Double columns - there are no rounding errors involved.

So, to answer your question. Decimal is likely the best choice for data type. Remember, you can display the value(s) either in a DataGridView or a String (TextBox / Label etc) in any way to suit your needs. I used "\$0.00" in this example.

Form1 with DataGridView1, Label2, Label3 and Label4

Dim dt As New DataTable("Freddy")
Dim r As New Random
With dt

For i As Integer = 0 To 99
Dim samenum As Double = r.NextDouble() * r.Next(5, 9)
Next
End With
DataGridView1.DataSource = dt
Dim s1 As Decimal = CDec(dt.Compute("Sum(Decimal)", Nothing))
Dim s2 As Double = CDbl(dt.Compute("Sum(Double)", Nothing))
Dim s3 As Integer = CInt(dt.Compute("Sum(Integer)", Nothing))

Label2.Text = Format(s1, "\$0.00")
Label3.Text = Format(s2, "\$0.00")
Label4.Text = Format(s3, "\$0.00")
End Sub

Regards Les, Livingston, Scotland

Hi Les

Thank you for getting back to me, As Cor Mentioned As well as You I will Use Decimal. And Thank you for your Code.

All the Best

Gary

Gary Simpson

• Marked as answer by Wednesday, June 6, 2018 7:12 PM
• Unmarked as answer by Wednesday, June 6, 2018 7:12 PM
Wednesday, June 6, 2018 7:03 PM
• Hi Cor

Thank you for putting me at ease I shall use Decimal.

All the best

Gary

Gary Simpson

Gary,

You should mark Cor's post as the answer (as you did Leshay) instead of your response to Cor. That way Cor's post gets the credit. You can mark your own posts if you have also provided an answer(s).

:)

Wednesday, June 6, 2018 7:17 PM
• Even though you have a solution but wanted to present this in case it might have value as it will revise totals if cell values change during the course of the app running. This is done via several events. It may be overkill for you but can't hurt to present this alternate solution.

https://code.msdn.microsoft.com/DataGridView-calculating-b38045fa?redir=0

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

• Marked as answer by Thursday, June 7, 2018 10:48 AM
Wednesday, June 6, 2018 11:02 PM
• Hi Cor

Best Regards

Gary

Gary Simpson

Thursday, June 7, 2018 10:44 AM
• Hi Karen,

Thank you for getting Back to me, With your DataGridView Calculating

Much Appreciated

Kind Regards

Gary

Gary Simpson

Thursday, June 7, 2018 10:48 AM
• Hi Cor

Best Regards

Gary

Gary Simpson

Gary,

No problem, I knew that you would correct it, and if not, it was not an answer that took me much time :-)

Success
Cor

Thursday, June 7, 2018 10:55 AM
• Hi Cor

Thank you for putting me at ease I shall use Decimal.

All the best

Gary

Gary Simpson

Gary,

You should mark Cor's post as the answer (as you did Leshay) instead of your response to Cor. That way Cor's post gets the credit. You can mark your own posts if you have also provided an answer(s).

:)

Hi TommyTwoTrain,

Thank you for putting right About Marking as Answered, I have now rectified the fault. I hope Cor forgives me for my Error. Please continue to point out any faults that are caused by me, It was not intentional it was a genuine mistake on my part.

All the Best

Gary

Gary Simpson

Thursday, June 7, 2018 10:57 AM