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

  • 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

    Wednesday, June 6, 2018 6:08 PM

Answers

  • Decimal (Money does not exist in .Net)

    Success
    Cor

    • Marked as answer by Gary Simpson 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
      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
    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
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

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

All replies

  • Decimal (Money does not exist in .Net)

    Success
    Cor

    • Marked as answer by Gary Simpson 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
      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
    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 Gary Simpson Wednesday, June 6, 2018 7:04 PM
    • Unmarked as answer by Gary Simpson 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
      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
    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,

    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).

    :)

    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
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

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

    Sorry I marked the wrong Answer as Answered, instead of yours, So Sorry please forgive me.

    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

    Sorry I marked the wrong Answer as Answered, instead of yours, So Sorry please forgive me.

    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,

    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

    Thursday, June 7, 2018 10:57 AM