I'm creating a sales invoice order form and whenever a purchase is made, the quantity of a certain product ordered is deducted from the product database. how do i do that? I am using this sql query to get the product details input from vb to the database.

SELECT Invoice_Detail.[InvoiceNo], Invoice_Detail.Product_Code, Items.Description, Invoice_Detail.Qty, Invoice_Detail.Price, Invoice_Detail.Discount_Per, Items.[Sales_Price], ([Qty]*([Price]-[Price]*[DISCOUNT_PER]/100)) AS inv_amount
FROM Items INNER JOIN Invoice_detail1 AS invoice_detail ON Items.[Product_Code] = Invoice_detail.Product_Code;

is there a way to add an extra syntax in there to update the products table to reflect the new quantity? thanks!

13 Years
Discussion Span
Last Post by Paladine

Well what I would suggest, to make things easier, SELECT the values from the DB and have them stored in a variable on the VB side, then do your calculations, and post the results back to the DB.

Or even better, create a Stored Procedure which you pass the variables into, and have the DB do the calculations and run the UPDATE sql statement to update the data.

Hope this helps..

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.