Change values of other columns in same row upon change of value on one column

Question

I'm writing a desktop application in VB.Net using an ObjectListView . I have a requirement to recalculate the values of other columns in the same row if any column value in the row changes.

Below is an example on how the ObjectListView displays rows currently:

Unit Name	Length	Height	Qty	Rate	Amount
MM	10	15	150	10.00	1500
CMS	15	5	75	8.00	600
IN	12	5	60	24.00	1440
MTR	11	6	66	18.00	1188

The Qty column is calculated as Length * Height. When the user enters 10 in the Length column and 15 in the Height column, the Qty column will show the value as 150 (10*15).

Similarly, Amount is calculated as Rate * Qty. When the user enters 10.00 Rate, the Amount column will show the value as 1500 (150*10.00).

My difficulty is I'm unable to find a suitable ObjectListView event to use for calculating cell values when a row changes.

I've done multiple attempts on various events, but the latest one is below. I tried getting an entire row object for the cell where the user is entering value, but failed. I then tried casting e.RowObject to my class ClientTask, hoping the e.RowObject would return the entire row, but the cast threw an exception. I also tried working on e.Column.AspectName but I'm not sure how to commit the new values back to the model.

How can I do this? I appreciate any help you can give.

Private Sub dlvEstimateTemplate_CellEditFinishing(sender As Object, e As CellEditEventArgs) Handles dlvEstimateTemplate.CellEditFinishing
        Dim lngth As Single = 0     'Length
        Dim ht As Single = 0        'Height
        Dim qty As Single = 0       'Quantity (Length * Height = Qty)
        Dim rt As Single = 0
        Dim amt As Single = 0
        Dim myObj As Entities.ClientTask
        Dim myTsk As Entities.ClientTask


        'tmpVal = DirectCast(sender, BrightIdeasSoftware.DataListView).HotRowIndex
        'DirectCast(sender, BrightIdeasSoftware.DataListView).GetItemAt(e.x

        'myObj = e.RowObject
        If e.Column.AspectName = "Length" Then
            myTsk = New Entities.ClientTask
            myTsk.Qty = e.NewValue * myTsk.Height
            myTsk.Amt = myTsk.Qty * myTsk.Rate
            'lngth = e.NewValue
        ElseIf e.Column.AspectName = "Height" Then
            myTsk = New Entities.ClientTask
            myTsk.Qty = e.NewValue * myTsk.Length
            myTsk.Amt = myTsk.Qty * myTsk.Rate
        End If
        DirectCast(sender, BrightIdeasSoftware.DataListView).BuildList()
        'myObj = TryCast(e.RowObject, Entities.ClientTask)
    End Sub

Answer 1

I finally managed to find a solution, though I feel it can be optimized further as it's using multiuple for-loops which I feel is not an ideal solution. If anyone can assist refining further, especially without for-loops, it would really help:

Private Sub dlvEstimateTemplate_CellEditFinishing(sender As Object, e As CellEditEventArgs) Handles dlvEstimateTemplate.CellEditFinishing
    UpdateRowCalc03(sender, e)
End Sub

Private Sub UpdateRowCalc03(ByVal mySender As Object, ByVal myE As CellEditEventArgs)
    Dim rowId As Integer
    Dim lngth As Single = 0     'Length
    Dim ht As Single = 0        'Height
    Dim qty As Single = 0       'Quantity (Length * Height = Qty)
    Dim rt As Single = 0        'Rate
    Dim amt As Single = 0       'Amount (Qty * Rate)

    rowId = myE.ListViewItem.SubItems(0).Text
    For Each itm As ListViewItem In dlvEstimateTemplate.Items
        If Convert.ToInt32(itm.SubItems(0).Text) = rowId Then
            If myE.Column.AspectName = olvColLength.AspectName Then             'Length
                lngth = myE.NewValue
                ht = Convert.ToSingle(myE.ListViewItem.SubItems(5).Text)        'Height
                qty = lngth * ht                                                'Qty
                rt = Convert.ToSingle(myE.ListViewItem.SubItems(7).Text)        'Rate
                CommitToDataTable(rowId, rt, qty)
            ElseIf myE.Column.AspectName = olvColHeight.AspectName Then         'Height
                ht = myE.NewValue                                               'Height
                lngth = Convert.ToSingle(myE.ListViewItem.SubItems(4).Text)     'Length
                qty = lngth * ht                                                'Qty
                rt = Convert.ToSingle(myE.ListViewItem.SubItems(7).Text)        'Rate
                CommitToDataTable(rowId, rt, qty)
            ElseIf myE.Column.AspectName = olvColRate.AspectName Then           'Rate
                lngth = Convert.ToSingle(myE.ListViewItem.SubItems(4).Text)
                ht = Convert.ToSingle(myE.ListViewItem.SubItems(5).Text)        'Height
                qty = lngth * ht                                                'Qty
                rt = myE.NewValue                                               'Rate
                CommitToDataTable(rowId, rt, qty)
            End If
        End If
    Next
End Sub

Private Sub CommitToDataTable(ByVal rowId As Integer, ByVal myRt As Single, ByVal myQty As Single)
    For Each row As DataRow In dtTable.Rows         'dtTable is a form scoped data table object
        For Each myRow As DataRow In dtTable.Rows
            If myRow("UniqueId") = rowId Then
                myRow("Qty") = myQty
                myRow("Amt") = myRt * myQty
            End If
        Next myRow
    Next row
End Sub