ASP.NET, c#如何编写此sql命令

本文关键字：sql 命令 何编写 NET ASP | 更新日期: 2023-09-27 18:11:59

如何在asp.net c#代码中编写此代码?

我要做的是选择invoicetable中orderno等于当前session的所有行，并从符合其itemid的"可发票数量"中扣除inventorytable的库存。

SqlCommand cmd = 
    new SqlCommand("UPDATE inventorytable 
                    JOIN invoicetable ON inventorytable.ItemID = invoicetable.ItemID 
                    SET inventorytable.inventory = inventorytable.inventory-invoice.QTY 
                    WHERE invoicetable.No='" + Convert.ToInt32(Session["invoiceno"]) + "'"
                    , con);
InsertUpdateData(cmd);

ASP.NET, c#如何编写此sql命令

您的更新查询没有正确形成，您应该使用参数化SQL。试试这样写

var sqlQuery = 
    @"UPDATE inventorytable 
         SET inventorytable.inventory = inventorytable.inventory-invoice.QTY 
        FROM inventorytable
             INNER JOIN invoicetable ON inventorytable.ItemID = invoicetable.ItemID 
       WHERE invoicetable.No=@invNo";
using (var conn = new SqlConnection(CONN_STR))
{
   var sqlCmd = new SqlCommand(sqlQuery, conn);
   sqlCmd.Parameters.AddWithValue("@invNo", Session["invoiceno"].ToString());
   sqlCmd.ExecuteNonQuery();
}

我键入这个没有VS在我面前，所以让我知道如果有任何语法问题

    var n = Session["invoiceno"] != null ? Convert.ToInt32(Session["invoiceno"]) : 0;
using (var conn = new SqlConnection(CONN_STR))
{
    conn.Open();
    var sql = "SELECT * FROM invoicetable WHERE orderno = @n";
    var cmd = new SqlCommand(sql);
    cmd.Connection = conn ;
    cmd.Parameters.AddWithValue("@n", n);
    using(var dr = cmd.ExecuteReader())
    {
        while(dr.Read())
        {
            //loop through DataReader
        }
        dr.Close();
    }
}
相关文章：