Click here to Skip to main content
15,742,120 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
MySqlConnection con = new MySqlConnection(connection...);
MySqlCommand command = con.CreateCommand();
command.CommandText = "INSERT INTO inventoryout(Product,Model,Stock,Date,Price,TotalPrice) VALUES (@Product,@Model,@Stock,@Date,@Price,@Total)";
command.Parameters.AddWithValue("@Product", cbProductOut.SelectedItem.ToString());
command.Parameters.AddWithValue("@Model", txtModelOut.Text);
command.Parameters.AddWithValue("@Stock", txtStockOut.Text);
command.Parameters.AddWithValue("@Date", DateTime.Now);
command.Parameters.AddWithValue("Price", txtPriceOut.Text);
command.Parameters.AddWithValue("@Total", tp.ToString());
command.CommandText = "update inventory left join inventoryout on inventory.Product = inventoryout.Product set inventory.Stock = (inventory.Stock - inventoryout.Stock) where inventory.Product = @Products";
command.Parameters.AddWithValue("@Products", cbProductOut.SelectedItem.ToString());
Updated 22-Feb-15 1:24am
_bluRe_ 22-Feb-15 7:17am    
this dosen't update the main inventory stock..

1 solution

Even if it did work, it wouldn't do what you want.
It looks like you are storing each stock transaction in one table (inventory_out)
Date        Product       Sold
2015-01-01  Brown Table   2
2015-01-02  White Table   1
2015-01-15  Brown Table   3
And then updating the total stock left in another table (inventory).

The problem with your query is that is will revise inventory with all the matching values of inventory_out each time you run the update: so when your Product is "Brown Table" on 15th Jan, it will remove both the 15th and 1st stock movements on the 15th, having already updated the transaction from the 1st on the 1st when you last did it.

The way I would do it is to create a stored procedure to change stock: it would insert the transaction in the inventory_out table, and update the inventory table within the same transaction, but as two separate SQL commands - using the number of items and product passed in as parameters.
Much cleaner solution.
Share this answer
_bluRe_ 22-Feb-15 7:43am    
you are right. do i need another database that stores inventory.stock - inventoryout.stock?
OriginalGriff 22-Feb-15 7:57am    
No, your two tables are fine - just don't run the update against every row in the "transactions" table: inventory_out. Run it on inventory using the same info that you used to insert the row into the transactions table.
_bluRe_ 22-Feb-15 8:02am    
so do u mean a bind method that updates the inventory?
_bluRe_ 22-Feb-15 8:19am    
thanks men! u gave me that idea :)
OriginalGriff 22-Feb-15 8:22am    
You're welcome!

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900