We help IT Professionals succeed at work.

DataGridView sum of columns

dave_sky
dave_sky asked
on
1,252 Views
Last Modified: 2008-01-09
Hi Experts,

Software:
VB.Net 2005
SQL Server 2005

Bound DataGridView

I need sample code to calculate hours worked.

Example:
Column 'Hours' * Column 'Rate' = Column 'Total'
       1.5                     10.00               15.00

Thanks,

Dave
Comment
Watch Question

Commented:
Hi dave_sky
Try the following code:

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim sum As Double = GetColSum("Hours") * GetColSum("Rate")
        MsgBox(sum)
    End Sub

    Private Function GetColSum(ByVal ColName As String) As Double
        Dim i As Integer
        Dim sum As Double

        If DataGridView1.RowCount < 1 Then Return 0

        If DataGridView1.Columns.Contains(ColName) = False Then Return -1

        For i = 0 To DataGridView1.RowCount - 1
            sum += Val(DataGridView1.Rows(i).Cells(ColName).Value)

        Next

        Return sum
    End Function

Author

Commented:
Hi iHadi,

I'm getting some errors on this line.
> sum += Val(DataGridView1.Rows(i).Cells(ColName).Value)

Other than changing the DataGridView name, am I correct in assuming that I change 'ColName' to 'Total' ?

Dave

Commented:
Could you post the error message you're getting

Author

Commented:
OK

Error      1      Overload resolution failed because no accessible 'Val' accepts this number of arguments.

Error      2      End of statement expected.

Dave

Commented:
Try this.  One form, one datagridview - called dgv - two buttons.  This code

Public Class Form1

    Private dt As New DataTable("TestTable")

    Private Sub fillTable()
        Dim dc1 As New DataColumn("Hours", GetType(Decimal))
        dt.Columns.Add(dc1)
        Dim dc2 As New DataColumn("Rate", GetType(Decimal))
        dt.Columns.Add(dc2)
        For i As Integer = 1 To 6
            Dim dr As DataRow = dt.NewRow
            dr(0) = i + 0.5
            dr(1) = i * 0.5
            dt.Rows.Add(dr)
        Next
    End Sub

    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        fillTable()  'for demo, equivalent to using dataadapter.fill
        dgv.DataSource = dt
        Button1.Text = "Row Totals"
        Button2.Text = "Column Totals"
    End Sub

    Private Sub DoRowTotals()
        If dt.Columns.Count > 2 Then Exit Sub 'already done
        Dim dc As New DataColumn("Total", GetType(Decimal))
        dc.Expression = "Hours * Rate"
        dt.Columns.Add(dc)
    End Sub

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        DoRowTotals()
    End Sub

    Private Sub doColumnTotals()
        Dim msg As String = ""
        For Each dc As DataColumn In dt.Columns
            Dim thisTotal As Decimal = dt.Compute("Sum(" & dc.ColumnName & ")", "")
            msg &= dc.ColumnName & " Total is " & thisTotal.ToString & vbCrLf
        Next
        MsgBox(msg)
    End Sub

    Private Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
        doColumnTotals()
    End Sub
End Class

Roger

Commented:
And this question looks essentially the same as

https://www.experts-exchange.com/Programming/Programming_Languages/Dot_Net/VB_DOT_NET/Q_22138380.html

I wonder if you should delete that?

Roger

Commented:
try the following instead of the previous line you're getting the error on
sum += Convert.ToDouble(DataGridView1.Rows(i).Cells(ColName).Value)

Author

Commented:
Roger,

Your code seems to work great, but i'm having trouble updating the row totals to the database.
If you come back with some sample code, keep in mind that i'm not totaling the columns.

iHadi,

I tried your code in several different ways. Your last suggestion did take care of the errors, but the column 'Hours' code rounds off the decimal.
 (See my example '1.5' above)

Thanks,

Dave

Commented:
You will not be able to save an expression column (or, more strictly, the data from an expression column) to a database.  This is because a column is either a data column or it is an expression column.  If it is a data column it has a value in it, and that can be transferred to and from a database.  If it is an expression column it contains a formula, not a value, and you cannot (by any normal means) transfer a formula to and from a database.

If you actually want to save any calculated total to a database (and bring back over, from the database, any calculated totals that you have previously saved) you will need to use a datacolumn, of the same (or equivalent) datatypes at both ends.  That means you will need code to calculate the value that you put in your total column, rather than just putting the formula in the column itself.  The basic code, provided the datatypes for the three columns are appropriate, is as simple as

   thisRow("total") = thisRow("hours") * thisRow("rate")

Where you put that code is up to you, although it will affect how you define the "thisRow" object that that example line refers to.  If you want (e.g. in a DataGridView's CellEndEdit event code) to refer to the current row of the DataGridView you would need to precede that line with

   Dim thisRow As DataGridRowView = myDataGridView.CurrentRow

whereas if you wanted to do the calculation in the datatable itself (e.g. in RowChanged event code) it would be

   Dim thisRow As DataRow = e.Row

But I wonder why you want to transfer totals backwards and forwards between your app and your database?  The elements that contribute to that total are being saved and transferred backwards and forwards.  The same formula will produce the same results on those elements whether the calculation is done in your app and/or in the database.  So, once you've saved the elements the total is always derivable without having to "store" it and add to the amount of data that has to be transferred between database and app.

Roger

Commented:
Sorry, if you use the DataGridRowView approach, the code would have to be

        Dim thisRow As DataGridRowView = myDataGridView.CurrentRow
        thisRow.Cells("total").Value = thisRow.Cells("hours").Value * thisRow.Cells("rate").Value

I was forgetting that it has no default property that can be indexed.

Roger

Author

Commented:
Roger,

First off, I have to be more precise in phasing my questions. I was trying to keep it simple and ended up causing you a lot of extra work.

Starting over:

I have the following code in my 'FormLoad'.

 'Compute(Total)
      dc.Expression = "(Hours*Rate)"
      dc.ReadOnly = True
      dc.ColumnName = "Total"
      dc.DataType = System.Type.GetType("System.Double")
      MYDataSet.Tables("MyTable").Columns.Add(dc)

The code works fine except I need to be able to use percentages in the 'Hours' column.
Now when I key in the number of hours, such as 1.5 hours, it gets rounded off to 2 hours. I need it to stay at 1.5 hours.
Hopefully, this will be a simple fix.

Thanks,

Dave

Commented:
I do not think that anything in the code you have shown would cause hours, when keyed in as 1.5, to change to 2.  What is the datatype of the field you are keying 1.5 into as the number of hours?

Roger

Author

Commented:
Roger,

I have tried with the data type being 'int' and also decimal(18,0).

Dave
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
Roger,

That did the trick.

Thank you!

Dave
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.