Can somebody show me the way to do that ??? update value and reflect the update on other column values

Question

look ,

lets assume that i have 2 tables

first one called Products ( ProductID,ProductQuantity)

second one called OrdersIN (OrderID,ProductID,QuantityIN)

Products Have those rows

ProductID      ProductQuantity

100                0

okay and then we make 2 orders to have those 2 rows like that

OrderID    ProductID  QuantityIN
10          100           50
11          100           20

then the Products Table looklike become like that

ProductID     ProductQuantity
100                70

 

all of that done with small windows form and some buttons and textboxs okay

what i wanna do is ,,, how can i make change to the OrderID in QuantityIN and replace the value that added to the ProductQuantity with the new value to have a right Value in ProductQuantity

thats all ,,, i hope somebody help me what i have to search or look for to accomplish that

Answer 1

Hi,

you can use compute method of datatable to get a result like sum,count,max.

Then all you have to do is update your first table.

Here is a sample.

            DataTable dtProducts = new DataTable();
            dtProducts.Columns.Add("ProductID", typeof(int));
            dtProducts.Columns.Add("ProductQuantity", typeof(int));
            dtProducts.Rows.Add(100, 0);

            DataTable dtOrders = new DataTable();
            dtOrders.Columns.Add("OrderId", typeof(int));
            dtOrders.Columns.Add("ProductId", typeof(int));
            dtOrders.Columns.Add("QuantityN", typeof(int));

            dtOrders.Rows.Add(10, 100, 50);
            dtOrders.Rows.Add(11, 100, 20);

            object result=dtOrders.Compute("SUM(QuantityN)", "ProductId=100");
            dtProducts.Select("ProductId=100")[0]["ProductQuantity"] = result;

Answer 2

aha ,,, i will check that and back to you ,,, thank you Tamer

Answer 3

It seems to me, with all these questions the issue boils down to the same thing.

You want to modify the contents of two tables in a single transaction.

When you manipulate the records in the sales table, you want sales quantity to reflect the quantity on hand in the products table.

The safest way to automatically reflect this type of behavior is to use a series of triggers on your sales table that update the corresponding record in the product table.

Alternatively, you could do this with a transaction and several SQL statements from your application or from a stored procedure.

Answer 4

Let's say you have two tables:

Product

Id Int PK

QuantityOnHand Int

Sale

Id Int PK

ProductId Int

Quantity

One might then create a series of trigger on the SALE table so that manipulation of rows in SALE resulted in management of the corresponding records in PRODUCT.  One should do some validation but this might get you started:

CREATE TRIGGER [dbo].[NewSale]
   ON  [dbo].[Sale]
   AFTER INSERT
AS 
BEGIN
    SET NOCOUNT ON;
	
    Declare @ProductId int, @Quantity int

    Select
	@ProductId = ProductId,
	@Quantity = Quantity
    From Inserted;
    
    Update Product
    Set Product.QuantityOnHand = Product.QuantityOnHand - @Quantity
    Where Product.Id = @ProductId

END

and

CREATE TRIGGER [dbo].[ChangeSale]
   ON  [dbo].[Sale]
   AFTER UPDATE
AS 
BEGIN
    SET NOCOUNT ON;
	
    Declare
        @ProductId int,
        @OldQuantity int,
        @NewQuantity int

    Select
        @OldQuantity = Quantity
    From Deleted;

    Select
        @ProductId = ProductId,
        @NewQuantity = Quantity
    From Inserted;
    
    Update Product
    Set Product.QuantityOnHand = Product.QuantityOnHand - @NewQuantity + @OldQuantity
    Where Product.Id = @ProductId

END

You probably would want a corresponding AFTER DELETE trigger as well.

Alternatively, you could achieve the same thing in your application if you wanted.

Answer 5

really that was so good ,,, i will understand that thank you so much jgalley and thank you too tamer

Answer 6

your triger is good but so long

i got idea to Use Inner join  to select Sum of QuantityIN from OrdersIN

thats all ,,, but triger is great and i will study it well because its will help me anyway

thanks for all of you