Calculate DataGridView Column

Posted on 2011-04-27
Last Modified: 2012-05-11
How would I calculate a value in a datacolumn with the below example:

User enters the value "Quantity" in the datagridview column 1, then enters "Cost" in the datagridview column 2....I would then want column 3 "Total Cost" to calculate as "Quantity x Cost".

I've tried several things with no success.....
Question by:tobin46
    LVL 10

    Expert Comment

    by:Asim Nazir

    You need to handle CellValueChanged event of Grid. Hre is working code for your reference:

    Private Sub BindGrid() 'Called from form load
            Dim dt As New DataTable
            Dim dc As New DataColumn("C1")
            dc = New DataColumn("C2")
            dc = New DataColumn("C3")
            Dim dr As DataRow = dt.NewRow()
            dr("C1") = 1
            dr("C2") = 2
            dr("C3") = 3
            DataGridView1.DataSource = dt
        End Sub
        Private Sub DataGridView1_CellValueChanged(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellValueChanged
            If (e.ColumnIndex = 0 Or e.ColumnIndex = 1) Then
                Dim dvr As DataGridViewRow = DataGridView1.Rows(e.RowIndex)
                dvr.Cells("C3").Value = dvr.Cells("C2").Value * dvr.Cells("C1").Value
            End If
        End Sub

    Open in new window

    I hope it helps.
    LVL 11

    Accepted Solution

    LVL 1

    Author Comment

    @srikanthreddyn143 - Thanks, I already reviewed and this is geared more towards interating through the rows.  I'd like a more "real-time" approach.

    @asimnazir123 - I have a few extra steps.  I Bind the DataTable tot the DGV, but then I want it to display it in the DGV a certain way so I have a sub to Build the DGV.  Then I created a CellValueChanged Sub.  Here is the code for all 3.  I'm getting an the error:
    "Index was out of range. Must be non-negative and less than the size of the collection.
    Parameter name: index"} - on the row in bold and italic below in the cellvaluchangedsub on form load.

    I want the "Material Quantity" column times the "Unit Cost" column to equal the "Total Cost" column in the DGV.  

    BIND SUB....
    Private Sub Bind_Material_Data_Table()

            Dim dt_MaterialInfo As DataTable = New DataTable("Material")





            'Datagrid element 0

            'Datagrid element 1

            'Datagrid element 2

            'Datagrid element 3
            dt_MaterialInfo.Columns("Material_Unit_Cost").DataType = System.Type.GetType("System.Decimal")

            'Datagrid element 4
            dt_MaterialInfo.Columns("Material_Total_Cost").DataType = System.Type.GetType("System.Decimal")

            If DS_Material.Tables.Count < 1 Then
            End If

            Me.DG_Material_Binding.DataSource = Me.DS_Material.Tables("Material")
            Me.DG_Material.DataSource = Me.DG_Material_Binding

        End Sub

    BUILD DGV.......
    Private Sub Build_Material_DataGridView()

            With Me.DG_Material

                .Columns(0).Visible = False

                .Columns(1).Visible = False

                .Columns(2).Visible = False

                .Columns(3).Visible = False

                .Columns(4).Width = 100
                .Columns(4).DisplayIndex = 0
                .Columns(4).HeaderText = "Material Quantity"

                .Columns(5).Width = 250
                .Columns(5).DisplayIndex = 1
                .Columns(5).HeaderText = "Material Description"

                .Columns(6).Width = 75
                .Columns(6).DisplayIndex = 2
                .Columns(6).HeaderText = "Per"

                .Columns(7).Width = 75
                .Columns(7).DisplayIndex = 3
                .Columns(7).HeaderText = "Unit Cost"
                .Columns(7).DefaultCellStyle.Format = "c"

                .Columns(8).Width = 100
                .Columns(8).DisplayIndex = 4
                .Columns(8).HeaderText = "Total Cost"
                .Columns(8).DefaultCellStyle.Format = "c"

            End With
        End Sub

    CELL CHANGED......
    Private Sub DG_Material_CellValueChanged(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DG_Equipment.CellValueChanged
            If Me.DWS_Mode = 1 Then

                If (e.ColumnIndex = 4 Or e.ColumnIndex = 7) Then
                    Dim dvr As DataGridViewRow = Me.DG_Material.Rows(e.RowIndex)                    dvr.Cells("Material_Total_Cost").Value = dvr.Cells("Material_Item_Quantity").Value * dvr.Cells("Material_Unit_Cost").Value
                End If

            End If
        End Sub
    LVL 11

    Expert Comment

    Check the row index value by debugging. And 1 more thing your event is handling equipment gridview and not material gridview.
    LVL 1

    Author Comment

    @Sri...that was a typo....I corrected.  Thanks.  I will check the row index through degub.  
    LVL 1

    Author Comment

    The RowIndex is -1.
    LVL 1

    Author Comment

    @srikanthreddyn143 - I reviewed the link and spent some time understanding what was going on and your link helped me solve.  Here is the code I used and it works.


    Private Sub DG_Material_CellValueChanged(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DG_Material.CellValueChanged
            If Me.DWS_Mode = 1 Then

                Dim Quantity As Integer = 0
                Dim Unit_Cost As Decimal = 0

                If e.RowIndex >= 0 Then

                    Dim dvr As DataGridViewRow = Me.DG_Material.Rows(e.RowIndex)

                    If Not dvr.Cells("Material_Item_Quantity").Value Is Nothing Then
                        If Not dvr.Cells("Material_Item_Quantity").Value.ToString().Length = 0 Then
                            Quantity = Integer.Parse(dvr.Cells("Material_Item_Quantity").Value.ToString())
                        End If
                    End If

                    If Not dvr.Cells("Material_Unit_Cost").Value Is Nothing Then
                        If Not dvr.Cells("Material_Unit_Cost").Value.ToString().Length = 0 Then
                            Unit_Cost = Decimal.Parse(dvr.Cells("Material_Unit_Cost").Value.ToString())
                        End If
                    End If

                    If (e.ColumnIndex = 4 Or e.ColumnIndex = 7) Then
                        dvr.Cells("Material_Total_Cost").Value = Quantity * Unit_Cost
                    End If
                End If
            End If
        End Sub
    LVL 1

    Author Closing Comment

    Link provided helped me solve.  Thanks!

    Featured Post

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
    If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
    In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
    Here's a very brief overview of the methods PRTG Network Monitor ( offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…

    754 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    24 Experts available now in Live!

    Get 1:1 Help Now