I have it all set up for my satock control, I have the primary keys and foreign keys too, so in SQL how is it to update my stock control when I sell item so I could have products deleted from stock and inserted to sold_product tables please. tbl_Stock ( stockID Primary Key) tbl_product (productID PK stockID Foreign key) tblProduct_sold ( a result of m:m relationship between tbl_sales and tbl_Product) tbl_sales (saleID PK)
You need to use a transaction to ensure the successful completion of both tasks. Below is an example that assumes you have a connection object Cn already set up. You don't detail your full data structure but here is an example on error resume next cn.BeginTrans cn.Execute("UPDATE tbl_Stock SET StockLevel = StockLevel - 1 WHERE ProductId = 123") cn.Execute("INSERT INTO tblProduct_sold (SaleId,ProductId) VALUES (321,123) ") if cn.Errors.Count = 0 then cn.CommitTrans else cn.RollbackTrans end if this code ensures that only if both the stock level decrement and sales order insert are sucecssful do any changes occur.
I agree with Dan that that's the basic idea of what you want to do. But, in general, unless it's a small, unimportant site, you should probably put those commands into a stored procedure. In principle, it's generally a bad idea to run arbitrary SQL from code. (Running SQL that contains any user input is a worse idea, but that's probably a different thread).
Assuming it is SQL Server then a stored proc may have some advantages however parameterised queries would be as safe but you would have more round trips unless you wrote it as one command text string in which case the difference is negligable. You should certainly use transactions to ensure the process is all or nothing regardless of whether you use a stored procedure or SQL.
Okay, I got a gist of it, but cn.Execute("UPDATE tbl_Stock SET StockLevel = StockLevel - 1 WHERE ProductId = 123") cn.Execute("INSERT INTO tblProduct_sold (SaleId,ProductId) VALUES (321,123) ") do I have to insert all the productID ? in the script? you have also 321, second line, I assume it's saleID, then do I have to insert them all ? and again, is there any need for SQL in this case? I am a bit..if not new to asp, could you please elaborate more when you say about connection..ect, I would like to know where I could fit the script. thanks
Okay, I got a gist of it, but cn.Execute("UPDATE tbl_Stock SET StockLevel = StockLevel - 1 WHERE ProductId = 123") cn.Execute("INSERT INTO tblProduct_sold (SaleId,ProductId) VALUES (321,123) ") do I have to insert all the productID ? in the script? you have also 321, second line, I assume it's saleID, then do I have to insert them all ? and again, is there any need for SQL in this case? I am a bit..if not new to asp, could you please elaborate more when you say about connection..ect, I would like to know where I could fit the script. thanks
Okay, I got a gist of it, but cn.Execute("UPDATE tbl_Stock SET StockLevel = StockLevel - 1 WHERE ProductId = 123") cn.Execute("INSERT INTO tblProduct_sold (SaleId,ProductId) VALUES (321,123) ") do I have to insert all the productID ? in the script? you have also 321, second line, I assume it's saleID, then do I have to insert them all ? and again, is there any need for SQL in this case? I am a bit..if not new to asp, could you please elaborate more when you say about connection..ect, I would like to know where I could fit the script. thanks
What the script is doing is deducting 1 from your inventory which is just a numeric field that keeps track of how many you have in stock then adding a sale transaction to your product sold table. Product = 123 is a made up product ID and 321 is a made up sale ID.