How to update 2 sqlserver tables using 2 query with having identity column in C#?

Question

By using this two queries separately for update tables,I could update only PO table.When i try to update PO_Cart table(which relates to datagridview) it said i cannot update identity column.What can i do now to solve this

public void UpdatePurchseOrderTable(int PO_No,int Supplier_ID, string Date, string RequiredDate, decimal GrandTotal)
    {
        DynamicConnection con = new DynamicConnection();
        con.mysqlconnection();
        string query = "UPDATE TBL_PO "
            + " SET Supplier_ID = @Supplier_ID,Date = @Date,"
            + "RequiredDate = @RequiredDate,GrandTotal=@GrandTotal"
             + " WHERE PO_No = @PO_No";
        con.sqlquery(query);
        con.cmd.Parameters.Add(new SqlParameter("@PO_No", SqlDbType.Int));
        con.cmd.Parameters["@PO_No"].Value = PO_No;
        con.cmd.Parameters.Add(new SqlParameter("@Supplier_ID", SqlDbType.Int));
        con.cmd.Parameters["@Supplier_ID"].Value = Supplier_ID;
        con.cmd.Parameters.Add(new SqlParameter("@Date", SqlDbType.Date));
        con.cmd.Parameters["@Date"].Value = Date;
        con.cmd.Parameters.Add(new SqlParameter("@RequiredDate", SqlDbType.Date));
        con.cmd.Parameters["@RequiredDate"].Value = RequiredDate;
        con.cmd.Parameters.Add(new SqlParameter("@GrandTotal", SqlDbType.Money));
        con.cmd.Parameters["@GrandTotal"].Value = GrandTotal;
        con.nonquery();
    }

public void UpdatePOCartTable(int PO_No,string ISBN_No, decimal UnitPrice, int OrderQuantity, decimal Total)
    {
        DynamicConnection con = new DynamicConnection();
        con.mysqlconnection();
        string query = "UPDATE TBL_PO_Cart"
            + " SET ISBN_No=@ISBN_No,UnitPrice=@UnitPrice,"
            + "OrderQuantity=@OrderQuantity,Total=@Total"
            + "WHERE PO_No = @PO_No";
        con.sqlquery(query);
        con.cmd.Parameters.Add(new SqlParameter("@PO_No", SqlDbType.Int));
        con.cmd.Parameters["@PO_No"].Value = PO_No;
        con.cmd.Parameters.Add(new SqlParameter("@ISBN_No", SqlDbType.NVarChar));
        con.cmd.Parameters["@ISBN_No"].Value = ISBN_No;
        con.cmd.Parameters.Add(new SqlParameter("@UnitPrice", SqlDbType.Date));
        con.cmd.Parameters["@UnitPrice"].Value = UnitPrice;
        con.cmd.Parameters.Add(new SqlParameter("@OrderQuantity", SqlDbType.NVarChar));
        con.cmd.Parameters["@OrderQuantity"].Value = OrderQuantity;
        con.cmd.Parameters.Add(new SqlParameter("@Total", SqlDbType.Money));
        con.cmd.Parameters["@Total"].Value = Total;
        con.nonquery();
    }

https://stackoverflow.com/questions/2044467/how-to-update-two-tables-in-one-statement-in-sql-server-2005 — Rajat Jaiswal, May 31 '18 at 06:29

Shakeer Mirza · Answer 1 · 2018-05-31T08:44:46.697

Your syntax of updating from Join is similar to MySQL. However it is different in SQL Server

UPDATE TBL_PO
SET TBL_PO.PO_No=@PO_No,TBL_PO.Supplier_ID = @Supplier_ID,TBL_PO.Date = @Date,
TBL_PO.RequiredDate = @RequiredDate,TBL_PO.GrandTotal=@GrandTotal,
FROM TBL_PO 
INNER JOIN TBL_PO_Cart ON TBL_PO_Cart.PO_No = TBL_PO.PO_No
WHERE TBL_PO.PO_No = @PO_No

UPDATE TBL_PO_Cart
SET TBL_PO_Cart.ISBN_No=@ISBN_No,TBL_PO_Cart.UnitPrice=@UnitPrice
,TBL_PO_Cart.OrderQuantity=@OrderQuantity,TBL_PO_Cart.Total=@Total
FROM TBL_PO 
INNER JOIN TBL_PO_Cart ON TBL_PO_Cart.PO_No = TBL_PO.PO_No
WHERE TBL_PO.PO_No = @PO_No

And if you are asking to update 2 tables with 1 statement, it is not possible. No RDBMS will allow it. Instead you can have Single transaction which have 2 update statements, so that you can commit/rollback.

Edit as per changed Question:

As the error described, you can't update column which is imposed with Identity property. We are not suppose to touch identity column in general.

And for the purpose of filling gaps we are provided with temporary setting

Identity_Insert

But as explained in below answer, you can delete the record and reinsert with the above setting ON. You can try this in your case.

How to update Identity column in SQL Server

Look at the table prefixes in the `SET` clause (which, BTW, aren't valid in SQL Server anyway). They *are* trying to update multiple tables. — Damien_The_Unbeliever, May 31 '18 at 07:02
I changed my question.So please help me.Thanks for your valuable comments — Kith, May 31 '18 at 08:15

How to update 2 sqlserver tables using 2 query with having identity column in C#?

1 Answers1