更新表中的SQL在C＃中插入后 [英] Update a table after an insert in SQL in C#

问题描述

我有这样的code已下订单后插入数量金额和项目编号为从数据表会话订单详细信息。

I have this code which inserts qty amount and item id into order details from a datatable session after order has been placed.

    for (i = 0; i <= dt2.Rows.Count - 1; i++) {
        sql = "INSERT INTO details (ID, itemID, Qty) " + " VALUES " + " ('" + stroID + "','" + dt2.Rows[i]["itemID"] + "','" + dt2.Rows[i]["qty"] + "')";
sql = string.Format("UPDATE Items SET AvailQty = AvailQty - {0} WHERE itemID = {1}", dt2.Rows[i]["qty"],stroID);
        var _with1 = cmd;
        _with1.Connection = con;
        _with1.CommandText = sql;
        _with1.CommandType = CommandType.Text;
        _with1.ExecuteNonQuery();
    }

不过，我不确定如何subract从可用数量的数量。

However, I am unsure of how to subract the qty from available quantity.

我有其中有一个领域的另一个表称为项目 AvailQty ，我知道该怎么做了查询，

I have another table called Items which has a field AvailQty and I know how to do the query,

UPDATE Items
   SET AvailQty = AvailQty - Qty
 WHERE itemID = stroID

不过，我不知道该如何为我使用dt2.rows做，在这种情况下？

However I am unsure how to do that in this scenario as I am using dt2.rows?

谁能帮助我如何从数量更新AvailQty好吗？

Could anyone help how I could update the AvailQty from Qty please?

推荐答案

您的存储过程应该是这个样子。 （C＃中，我让你自己处理）：

Your stored procedure should look something like this. (the c# I let you handle yourself):

CREATE PROCEDURE OrderAnItem(
    @ID int, 
    @itemID int, 
    @Qty int
)
AS
BEGIN TRY
BEGIN TRANSACTION
INSERT INTO details (ID, itemID, Qty) VALUES (@ID, @ItemId, @Qty)

UPDATE Items SET AvailQty = AvailQty - @Qty  WHERE itemID = @itemID 

COMMIT TRANSACTION
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
    ROLLBACK TRANSACTION
END CATCH

注意直接写在这里code，可能包含错误

Note code written directly here, might contain mistakes

这篇关于更新表中的SQL在C＃中插入后的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！