Member Avatar for reyhan12

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!

  • 2 Contributors
  • 1 Reply
  • 126 Views
  • 12 Hours Discussion Span
  • Latest Post Latest Post by Paladine
Member Avatar for 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..

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.