Member Avatar for infoitmanoj

i need an query for update my record added by old value and new updated value \

here is my query :
conn.Execute ("Update Medicine_Details SET UnitsInStock='" & rs3.Fields.Item(3).Value & "' where ProductID='" & rs3.Fields.Item(2).Value & "'")

but i dont know how to use sum in function these query working updating record but i need updating and counting also existing record ...

Edited by infoitmanoj
  • 2 Contributors
  • 2 Replies
  • 313 Views
  • 3 Hours Discussion Span
  • Latest Post Latest Post by infoitmanoj

Dani AI

Generated

As showed, the right approach is to increment the existing stock instead of overwriting it. For clarity: treat UnitsInStock as a numeric column (no quotes around the value) and perform an arithmetic update so the old value is preserved and the new quantity is added.

Here is a safer pattern using ADO parameterization (validates numeric input, avoids concatenation and SQL injection):

' ADO Command with parameters (append parameters in order for "?" placeholders)
Dim cmd As ADODB.Command
Set cmd = New ADODB.Command
Set cmd.ActiveConnection = conn
cmd.CommandText = "UPDATE Medicine_Details SET UnitsInStock = COALESCE(UnitsInStock,0) + ? WHERE ProductID = ?"
cmd.CommandType = adCmdText
cmd.Parameters.Append cmd.CreateParameter(, adInteger, adParamInput, , CLng(rs3.Fields.Item(3).Value))
cmd.Parameters.Append cmd.CreateParameter(, adVarChar, adParamInput, 50, rs3.Fields.Item(2).Value)
cmd.Execute

If you also need to keep a record of old→new values, use an audit table or a trigger so changes are tracked automatically:

CREATE TABLE Medicine_Stock_Audit (
  id INT AUTO_INCREMENT PRIMARY KEY,
  ProductID VARCHAR(50),
  OldUnits INT,
  NewUnits INT,
  ChangedAt DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER trg_medicine_stock_audit
AFTER UPDATE ON Medicine_Details
FOR EACH ROW
BEGIN
  INSERT INTO Medicine_Stock_Audit(ProductID, OldUnits, NewUnits)
  VALUES (OLD.ProductID, OLD.UnitsInStock, NEW.UnitsInStock);
END;

Quick checklist: ensure UnitsInStock is numeric; validate rs3 values before use (IsNumeric/CLng); handle NULLs with COALESCE/IFNULL; escape or parameterize ProductID; use transactions or check RowsAffected to handle concurrency; and prevent negative or overflow values by validating the resulting total. This addresses the original problem from while building a safer, auditable update flow.

Recommended Answers

All 2 Replies

Member Avatar for DaveAmour

Is this what you need?

("Update Medicine_Details SET UnitsInStock = UnitsInStock + " & rs3.Fields.Item(3).Value & " where ProductID='" & rs3.Fields.Item(2).Value & "'")

Member Avatar for infoitmanoj

thank you dude DaveAmour

Edited by infoitmanoj
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.