Member Avatar for nadiam

Hi. so this is actually a continuation from another question of mineHere but i was advised to start a new thread as the original question was already answered.

This is the result of previous question answered :

code for the listbox - datagridview interaction

At the top of the code so its public

Dim dt As New DataTable

in private sub form load

grd_order.DataSource = dt

in private sub for product list

Private Sub product_list(ByVal pid As String)
    Dim getproduct As String = "SELECT FLD_PRODUCT_ID,FLD_PRICE FROM TBL_PRODUCTS_A154287 WHERE FLD_PRODUCT_ID='" & pid & "'"
    Dim reader As New OleDb.OleDbDataAdapter(getproduct, myconnection)
    Dim getcolumns As String = "SELECT FLD_QTY,FLD_SUBTOTAL FROM TBL_ORDERITEM_A154287 WHERE FLD_PRODUCT_ID='" & pid & "'"
    Dim reader2 As New OleDb.OleDbDataAdapter(getcolumns, myconnection)
    reader.Fill(dt)
    reader2.Fill(dt)
End Sub

http://imgur.com/a/TKu0K

Now i want to know how to calculate the subtotal? because user can key in the quantity of the product they want. like in the picture : CK005 | 850 | , quantity and subtotal cells are empty. how would i get the value from cell Price and cell Quantity? to calculate the subtotal : Subtotal = valueFromQtyCell * valueFromPriceCell and put it into cell Subtotal for each its own row. Then get each value from column subtotal of each row and add them up as the Total Price; TotalPrice = valueFromSubtotalCellOfEachRow this im assuming needs a loop?

  • 3 Contributors
  • 3 Replies
  • 639 Views
  • 4 Days Discussion Span
  • Latest Post Latest Post by nadiam

Dani AI

Generated

A common root cause in the thread is mixing two separate fills into the same DataTable: that appends rows rather than producing Price+Qty on the same row. Either pull Price and Qty together using a single query (JOIN) or build a DataTable with explicit columns for ProductID, Price, Qty and Subtotal, then bind that table to the grid. Both server-side (SQL) and client-side (grid/DataTable) calculations are valid; choose server-side when totals must be authoritative on load, or client-side when the user edits Qty interactively.

Two practical, robust options:

  • Computed DataColumn (no manual loop). After the DataTable has numeric Price and Qty columns, add a computed column so Subtotal updates automatically:

    ' DataTable dt already contains FLD_PRICE (Decimal) and FLD_QTY (Decimal)
Dim col As New DataColumn("FLD_SUBTOTAL", GetType(Decimal))
col.Expression = "FLD_PRICE * FLD_QTY"
dt.Columns.Add(col)
' bind dt to DataGridView; subtotal will update when FLD_QTY changes

  • Event-based recalculation (if a visual update or custom formatting is required). Handle CellEndEdit (or CellValueChanged after committing the edit), parse Price/Qty safely with Decimal.TryParse, write Subtotal into that row, then sum all subtotals to update the grand total:

    Private Sub grd_order_CellEndEdit(sender As Object, e As DataGridViewCellEventArgs) Handles grd_order.CellEndEdit
  Dim r As DataGridViewRow = grd_order.Rows(e.RowIndex)
  Dim p As Decimal = 0D, q As Decimal = 0D
  If r.Cells("FLD_PRICE").Value IsNot Nothing Then Decimal.TryParse(r.Cells("FLD_PRICE").Value.ToString(), p)
  If r.Cells("FLD_QTY").Value IsNot Nothing Then Decimal.TryParse(r.Cells("FLD_QTY").Value.ToString(), q)
  r.Cells("FLD_SUBTOTAL").Value = p * q

  Dim total As Decimal = 0D
  For Each row As DataGridViewRow In grd_order.Rows
      If Not row.IsNewRow Then
          Dim s As Decimal = 0D
          If row.Cells("FLD_SUBTOTAL").Value IsNot Nothing Then Decimal.TryParse(row.Cells("FLD_SUBTOTAL").Value.ToString(), s)
          total += s
      End If
  Next
  lblTotal.Text = total.ToString("F2")
End Sub

Troubleshooting notes and cautions: use parameterized queries rather than string concatenation to avoid SQL injection; ensure Price/Qty columns are numeric types (DataColumn.Expression requires numeric types); make Price and Subtotal ReadOnly in the grid to avoid accidental edits; call CommitEdit/EndEdit before reading values if using bound controls; for large datasets prefer DataTable.Compute("SUM(FLD_SUBTOTAL)", "") or a SQL SUM for performance. This ties back to 's cell-value approach and 's server-side suggestion — both are correct, with DataColumn.Expression often the simplest for automatic per-row subtotals.

Recommended Answers

All 3 Replies

Member Avatar for tinstaafl

I found this article for you which should help explain it. Basically you need to use the SELECT statement to create the data for the other columns.

Edited by tinstaafl
Member Avatar for ddanbe

You could also work with DGV.Rows(r).Cells(c).Value in some loops. Remember to cast the Value to the proper type.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.