i want to subtract a value from a table while adding those value in another table

Question

I am creating a GRN application using winforms. I have 2 tables.

Table 1: In hand stock
Table 2: Sales

How can I subtract in hand stock from Quantity after a sale. This is my code for data base inserting. While adding these I want to subtract.

String SQL = "insert into Items_Sold values('" 
            + myobj.Invoiceno + "','"
            + myobj.Itemcode + "','"
            + myobj.Category + "','"
            + myobj.Itemname + "','" 
            + myobj.Qty + "','" 
            + myobj.Unitprice + "',' " 
            + myobj.Subtotal + "')";

        DBConnection mycon = new DBConnection();
        mycon.insertValues(SQL);

I tried

 {
        string SQL = "Update Stock Set Invoiceno='"
            + textBox_InvoiceNo.Text.Trim() + "', "
            + "Quantity='" + textBox_Qty.Text.Trim() + "',"

        DBConnection database = new DBConnection();
        int rslt = database.updatetValues(SQL);

        if (rslt > 0)
        {
            MessageBox.Show("Updated");
        }
        else
        {
            MessageBox.Show("Faild");
        }
    }

This is updating my database with new data.

Parameterize your sql queries. SQL injection attacks are bad. — P.Brian.Mackey, Nov 15 '13 at 19:28
http://stackoverflow.com/questions/6547986/how-to-prevent-a-sql-injection-escaping-strings this will help to prevent SQL injection — Sriram Sakthivel, Nov 15 '13 at 19:34

Gary Walker · Answer 1 · 2013-11-15T19:46:12.337

2

You have a couple of problems.

SQL Injection (i.e., use parameters)

No transaction -- you absolutely should be actions like this in a transaction so that the you don't end up with a debit but not a credit.

You really should use this update code similar to this

update Stock set Quantity = Quantity - @delta and Quantity >= @delta
where ...

This avoid problems that avoid some possible concurrency related issues

MISTAKE should have been

update Stock set Quantity = Quantity - @delta 
where ... and Quantity >= @delta

edited Nov 15 '13 at 19:46

answered Nov 15 '13 at 19:35

Gary Walker

8,831
3
19
41

Gary Walker i need some more explanation about updating. – Moz Nov 15 '13 at 19:43

score 1 · Answer 2 · answered Nov 15 '13 at 19:31

1

You probably want to add a WHERE-condition to the UPDATE to only update some (or one) record and not all. And you probably want to use something like

SET quantity = quantity - difference_value

where difference_value would be what you want to subtract. I am not sure about the details, as you did not state how you can e. g. identify the record to update when subtracting.

answered Nov 15 '13 at 19:31

FrankPl

13,205
2
14
40

i am making a invoice form so in that i if i make a sale it should be subtracted in my stock table while adding that same quantity in sold table finally i want to c remaining stock – Moz Nov 15 '13 at 19:37

i want to subtract a value from a table while adding those value in another table

2 Answers2