I have a databound datagridview with3 columns. ProductID, AvailableQty, Qty. I want to write some code in VB.net to take the sum of all of the Qty in the grid with a given product ID, add it to the Availableqty (pulled from database using SPROC) and return it in a new 4th column. Here's an example of my desired results:

ProductID, AvailableQty, Qty, NewColumn
1, 6, 2, 13
2, 3, 1, 4
1, 6, 5, 13
3, 5, 1, 6

I think if I attach this logic to the datagridview.row.endedit subroutine, it should update correctly everytime a change is made

Hi MethodMas,

Based on my understanding of your code. I have made a little change.
Private Sub DataGridView1_CellEndEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellEndEdit
Dim dgv As DataGridView = DirectCast(sender, DataGridView)
If dgv.Columns(e.ColumnIndex).Name = "Qty" Then
Dim productID As Integer = Convert.ToInt32(dgv(0, e.RowIndex).Value)

Dim total As Integer

For Each row As DataGridViewRow In dgv.Rows
If row.IsNewRow Then Continue For
If row.Cells("ProductID").Value = productID Then
total += row.Cells("Qty").Value
End If
Next
For Each row As DataGridViewRow In DataGridView1.Rows
If row.IsNewRow Then Continue For
If row.Cells("ProductID").Value = productID Then
row.Cells("NewColumn").Value = row.Cells("AvailableQty").Value - total
End If
Next
End If
End Sub

Does this what you mean?

Bye the way, based on your sample table, I think the logical is NewColumn = AvailableQty + Qty, why you code "row.Cells("NewColumn").Value = row.Cells("AvailableQty").Value - total"?

Sincerely,
Kira Qian

---

Please mark the replies as answers if they help and unmark if they don't.

Okay, so I did some more work on this and I'm 90% there. Using the code below, I get the desired result for the hard coded productID of "1". I just need to modify this now so it works for every ProductID at once...

Basically the Available Column is the quantity available based on all other orders in the system. The new column is going to take that and deduct the quantities of that item on this current order (in this datagridview) and give us a new proposed available quantity.

Hi MethodMas,

Based on my understanding of your code. I have made a little change.
Private Sub DataGridView1_CellEndEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles DataGridView1.CellEndEdit
Dim dgv As DataGridView = DirectCast(sender, DataGridView)
If dgv.Columns(e.ColumnIndex).Name = "Qty" Then
Dim productID As Integer = Convert.ToInt32(dgv(0, e.RowIndex).Value)

Dim total As Integer

For Each row As DataGridViewRow In dgv.Rows
If row.IsNewRow Then Continue For
If row.Cells("ProductID").Value = productID Then
total += row.Cells("Qty").Value
End If
Next
For Each row As DataGridViewRow In DataGridView1.Rows
If row.IsNewRow Then Continue For
If row.Cells("ProductID").Value = productID Then
row.Cells("NewColumn").Value = row.Cells("AvailableQty").Value - total
End If
Next
End If
End Sub

Does this what you mean?

Bye the way, based on your sample table, I think the logical is NewColumn = AvailableQty + Qty, why you code "row.Cells("NewColumn").Value = row.Cells("AvailableQty").Value - total"?

Sincerely,
Kira Qian

---

Please mark the replies as answers if they help and unmark if they don't.

This is exaclty it. Thank you!

To answer your question, I realized after my initial post that my logic was incorrect. If you know the number of units previously available, and then you start adding new units to the table, you are deducting them from the total number available, not adding them. therefore the logic should be "row.Cells("NewColumn").Value = row.Cells("AvailableQty").Value - total", not NewColumn = AvailableQty + Qty as I originally had suggested.

Thanks again!