• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1446
  • Last Modified:

Calculate DataGridView Column

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.....
0
tobin46
Asked:
tobin46
  • 5
  • 2
1 Solution
 
Asim NazirCommented:
Hi,

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")
        dt.Columns.Add(dc)

        dc = New DataColumn("C2")
        dt.Columns.Add(dc)

        dc = New DataColumn("C3")
        dt.Columns.Add(dc)

        Dim dr As DataRow = dt.NewRow()
        dr("C1") = 1
        dr("C2") = 2
        dr("C3") = 3

        dt.Rows.Add(dr)
        dt.AcceptChanges()

        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.
Asim
0
 
tobin46Author Commented:
@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")

        dt_MaterialInfo.Columns.Add("Row_ID")

        dt_MaterialInfo.Columns.Add("Slip_Number")

        dt_MaterialInfo.Columns.Add("Job_Number")

        dt_MaterialInfo.Columns.Add("SWO_ID")

        'Datagrid element 0
        dt_MaterialInfo.Columns.Add("Material_Item_Quantity")

        'Datagrid element 1
        dt_MaterialInfo.Columns.Add("Material_Description")

        'Datagrid element 2
        dt_MaterialInfo.Columns.Add("Material_Per")

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

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


        If DS_Material.Tables.Count < 1 Then
            DS_Material.Tables.Add(dt_MaterialInfo)
        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
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
srikanthreddyn143Commented:
Check the row index value by debugging. And 1 more thing your event is handling equipment gridview and not material gridview.
0
 
tobin46Author Commented:
@Sri...that was a typo....I corrected.  Thanks.  I will check the row index through degub.  
0
 
tobin46Author Commented:
The RowIndex is -1.
0
 
tobin46Author Commented:
@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.

THANKS!!!!!

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
0
 
tobin46Author Commented:
Link provided helped me solve.  Thanks!
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 5
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now