Answered by:
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.
Could You please advise me the best way to go.
Kind Regards
Gary
Gary Simpson
Answers

Decimal (Money does not exist in .Net)
Success
Cor Marked as answer by Gary Simpson Thursday, June 7, 2018 10:40 AM

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 Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load With dt .Columns.Add("Decimal", GetType(Decimal)) .Columns.Add("Double", GetType(Double)) .Columns.Add("Integer", GetType(Integer)) For i As Integer = 0 To 99 Dim samenum As Double = r.NextDouble() * r.Next(5, 9) .Rows.Add(CDec(samenum), samenum, r.Next(5, 99)) 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 Gary Simpson Wednesday, June 6, 2018 7:04 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/DataGridViewcalculatingb38045fa?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 Gary Simpson Thursday, June 7, 2018 10:48 AM
All replies

Decimal (Money does not exist in .Net)
Success
Cor Marked as answer by Gary Simpson Thursday, June 7, 2018 10:40 AM

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 Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load With dt .Columns.Add("Decimal", GetType(Decimal)) .Columns.Add("Double", GetType(Double)) .Columns.Add("Integer", GetType(Integer)) For i As Integer = 0 To 99 Dim samenum As Double = r.NextDouble() * r.Next(5, 9) .Rows.Add(CDec(samenum), samenum, r.Next(5, 99)) 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 Gary Simpson Wednesday, June 6, 2018 7:04 PM

Decimal (Money does not exist in .Net)
Success
CorHi Cor
Thank you for putting me at ease I shall use Decimal.
All the best
Gary
Gary Simpson
 Marked as answer by Gary Simpson Wednesday, June 6, 2018 7:04 PM
 Unmarked as answer by Gary Simpson Thursday, June 7, 2018 10:40 AM

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 Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load With dt .Columns.Add("Decimal", GetType(Decimal)) .Columns.Add("Double", GetType(Double)) .Columns.Add("Integer", GetType(Integer)) For i As Integer = 0 To 99 Dim samenum As Double = r.NextDouble() * r.Next(5, 9) .Rows.Add(CDec(samenum), samenum, r.Next(5, 99)) 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 Gary Simpson Wednesday, June 6, 2018 7:12 PM
 Unmarked as answer by Gary Simpson Wednesday, June 6, 2018 7:12 PM

Hi Cor
Thank you for putting me at ease I shall use Decimal.
All the best
Gary
Gary Simpson
Gary,
Thanks for marking the answers.
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).
:)

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/DataGridViewcalculatingb38045fa?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 Gary Simpson Thursday, June 7, 2018 10:48 AM




Hi Cor
Thank you for putting me at ease I shall use Decimal.
All the best
Gary
Gary Simpson
Gary,
Thanks for marking the answers.
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