How Will I Be Able To Manage The Inventory Stock In Whenever The User Inputs Inventory Stock Out?

Question

0.00/5 (No votes)

See more:

C#

MySqlConnection con = new MySqlConnection(connection...);
con.Open();
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());
command.Prepare();
command.ExecuteNonQuery();
BindGridInventoryOut();
con.Close();
MessageBox.Show("Saved!");

Posted 22-Feb-15 1:16am

_bluRe_

Updated 22-Feb-15 1:24am

Wendelius

v3

Add a Solution

Comments

_bluRe_ 22-Feb-15 7:17am

this dosen't update the main inventory stock..

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Accepted Answer · 2015-02-22T01:30:00

Solution 1

Um.
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.

Posted 22-Feb-15 1:30am

OriginalGriff

Comments

_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!

_bluRe_ 22-Feb-15 8:41am

command.CommandText = "update inventory left join inventoryout on inventory.Product = inventoryout.Product set inventory.Stock = (inventory.Stock - inventoryout.Stock) where inventory.Product = @Products";
command.CommandText = "update inventory left join inventoryout on inventory.Product = inventoryout.Product set inventory.TotalPrice = (inventory.TotalPrice - inventoryout.TotalPrice) where inventory.Product = @Products";

is this ok?

OriginalGriff 22-Feb-15 9:28am

That probably has the same problem - and why are you doing it as two unrelated commands? If the second fails, the first will have completed...

Exactly what are you trying to do? And how are your tables arranged?

_bluRe_ 22-Feb-15 9:37am

im good to go :) thanks!