Member Avatar for micca.jhai

greetings everyone!. I'm making Clinic system for my project. I just want to ask how to get the total quantity of the medicine and display it on textbox. I'm using using vb6 and adodc. Any help will be greatly appreciated.Thank you...

Table name inventory
fields: Medicinename
StockQuantity

  • 3 Contributors
  • 2 Replies
  • 211 Views
  • 4 Weeks Discussion Span
  • Latest Post Latest Post by Ferdielux

Dani AI

Generated

Good clarification and two workable directions already: correctly points to doing the aggregation in SQL (best for performance) and demonstrates a client-side sum loop (works for tiny tables but has a bug: no MoveNext, which creates an infinite loop). Below is a compact, robust pattern to follow in VB6/ADODB that avoids SQL injection, handles NULL results, and closes resources reliably.

Make sure the project references "Microsoft ActiveX Data Objects x.x Library" before using ADODB objects.

Private Sub UpdateMedicineTotal(ByVal medName As String, ByVal conn As ADODB.Connection)
    Dim cmd As ADODB.Command
    Dim rs  As ADODB.Recordset

    Set cmd = New ADODB.Command
    With cmd
        .ActiveConnection = conn
        .CommandText = "SELECT SUM(StockQuantity) AS TotalQty FROM inventory WHERE MedicineName = ?"
        .CommandType = adCmdText
        .Parameters.Append .CreateParameter("pName", adVarChar, adParamInput, 255, medName)
        Set rs = .Execute
    End With

    If rs.EOF Or IsNull(rs!TotalQty) Then
        txtTotal.Text = "0"
    Else
        txtTotal.Text = CStr(rs!TotalQty)
    End If

    rs.Close
    Set rs = Nothing
    Set cmd = Nothing
End Sub

If a client-side loop is preferred (as in ), use an accumulator and remember to advance the record pointer and check for NULLs:

Dim total As Long
total = 0
Set rs = conn.Execute("SELECT StockQuantity FROM inventory WHERE MedicineName = '...'")
Do While Not rs.EOF
    If Not IsNull(rs!StockQuantity) Then total = total + CLng(rs!StockQuantity)
    rs.MoveNext
Loop
txtTotal.Text = CStr(total)
rs.Close

Extra notes: ensure StockQuantity is numeric in the DB, index MedicineName for large tables, call the update routine after any stock change or on medicine-selection events, and prefer server-side SUM for large datasets.

Recommended Answers

All 2 Replies

Member Avatar for GhostWolf 
cmd = "select sum(StockQuantity) as Qty from inventory where MedicineName='" & selectionVariable & "'"
open recordset with cmd
with recordset
  txtField.text = CStr(!Qty)
  .close
end with
Edited by pritaeas because: Added markdown
Member Avatar for Ferdielux 
rs="Select StockQuantity from inventory",connection,3,3
txtbox1.text=""

while not rs.eof
    txtbox1.text=val(txtbox1.text)+ rs!StockQuantity
wend

Hope it help tanx

Edited by pritaeas because: Added markdown.
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.