How to update existing record and insert new record if not exist using c# Windows Form

Question

First of all I am using Windows Form C# and SQL Server 2014

I have a update form see the image below

This is the existing record in database and in datagridview if i add new record i want to update the existing record and insert the new one in database the following is just updating the existing record it doesn't insert new record if i add it in datagridview

See This Image What I Want

My C# Code

try
        {
            SqlConnection con = new SqlConnection(str);
            con.Open();

            for (int i = 0; i < dgv.Rows.Count; i++)
            {
                SqlCommand sc = new SqlCommand("Update_Purchase", con);
                sc.CommandType = CommandType.StoredProcedure;
                sc.Parameters.AddWithValue("@Invoice_no", Invoice_No.Text);
                sc.Parameters.AddWithValue("@Date_of_Purchase", Date_of_Purchase.Value.ToString("yyyy/MM/dd"));
                sc.Parameters.AddWithValue("@Item_Code", dgv.Rows[i].Cells["Column1"].Value);
                sc.Parameters.AddWithValue("@Item_Name", dgv.Rows[i].Cells["Column2"].Value);
                sc.Parameters.AddWithValue("@Descriptin", dgv.Rows[i].Cells["Column7"].Value);
                sc.Parameters.AddWithValue("@Supplier_Name", Supplier_Name.Text);
                sc.Parameters.AddWithValue("@Company_Name", Company_Name.Text);
                sc.Parameters.AddWithValue("@Quantity", dgv.Rows[i].Cells["Column3"].Value);
                sc.Parameters.AddWithValue("@Unit_Price", dgv.Rows[i].Cells["Column4"].Value);
                sc.Parameters.AddWithValue("@Total_Price", dgv.Rows[i].Cells["Column5"].Value);
                sc.Parameters.AddWithValue("@Selling_Price", dgv.Rows[i].Cells["Column6"].Value);
                sc.Parameters.AddWithValue("@Discount", Discount.Text);
                sc.Parameters.AddWithValue("@Paid_Amount", Paid_Amount.Text);
                sc.Parameters.AddWithValue("@Remaining", lbl_Remaining.Text);
                sc.Parameters.AddWithValue("@Sub_Total", lbl_Subtotal.Text);
                sc.Parameters.AddWithValue("@Total", lbl_Total.Text);
                sc.Parameters.AddWithValue("@Updated_Date", Updated_Date.Value.ToString("yyyy/MM/dd"));

                sc.ExecuteNonQuery();
            }
            con.Close();
            SuccessBox sb = new SuccessBox();
            sb.label1.Text = "Updated Successfully";
            sb.ShowDialog();
        }
        catch (Exception ex)
        {
            MessageBox.Show(ex.Message);
        }

My Stored Procedure:

ALTER PROCEDURE [dbo].[Update_Purchase]
(
@Invoice_no int,
@Date_of_Purchase date,
@Item_Code int,
@Item_Name Nvarchar(100),
@Descriptin Nvarchar(MAX),
@Supplier_Name Nvarchar(100),
@Company_Name Nvarchar(100),
@Quantity int,
@Unit_Price int,
@Total_Price int,
@Selling_Price int,
@Discount int,
@Paid_Amount int,
@Remaining int,
@Sub_Total int,
@Total int,
@Updated_Date date
)

AS
BEGIN

UPDATE Purchase_Order_Log SET Invoice_no = @Invoice_no, Date_of_Purchase = 
@Date_of_Purchase, Item_Code = @Item_Code, Item_Name = @Item_Name, 
Descriptin = @Descriptin, Supplier_Name = @Supplier_Name, Company_Name = 
@Company_Name, Quantity = @Quantity, Unit_Price = @Unit_Price, Total_Price = 
@Total_Price, Selling_Price = @Selling_Price, Discount = @Discount, 
Paid_Amount = @Paid_Amount, Remaining = @Remaining, Sub_Total = @Sub_Total, 
Total = @Total, Updated_Date = @Updated_Date 
WHERE Invoice_no = @Invoice_no AND Item_Code = @Item_Code
END

Possible duplicate of [Solutions for INSERT OR UPDATE on SQL Server](https://stackoverflow.com/questions/108403/solutions-for-insert-or-update-on-sql-server) — spodger, Nov 06 '17 at 10:07
You could also consider using a merge statement https://learn.microsoft.com/en-us/sql/t-sql/statements/merge-transact-sql — pmcilreavy, Nov 06 '17 at 10:15
Proper data binding will handle this for you regardless of whether or not you use separate insert and update procedures or a merge procedure. You would not have to loop over rows, you would just call Update() on a `SqlDataAdapter`. — Crowcoder, Nov 06 '17 at 10:20

Kaval Patel · Accepted Answer · 2017-11-06T10:54:32.710

Modify you store procedure like

ALTER PROCEDURE [dbo].[Update_Purchase]
(
@Invoice_no int,
@Date_of_Purchase date,
@Item_Code int,
@Item_Name Nvarchar(100),
@Descriptin Nvarchar(MAX),
@Supplier_Name Nvarchar(100),
@Company_Name Nvarchar(100),
@Quantity int,
@Unit_Price int,
@Total_Price int,
@Selling_Price int,
@Discount int,
@Paid_Amount int,
@Remaining int,
@Sub_Total int,
@Total int,
@Updated_Date date
)

AS
BEGIN



    IF NOT EXISTS (SELECT 1 FROM Purchase_Order_Log WHERE Invoice_no = @Invoice_no AND Item_Code = @Item_Code) 
    BEGIN 
    INSERT INTO Purchase_Order_Log (Invoice_no , Date_of_Purchase, Item_Code, Item_Name, Descriptin, Supplier_Name, Company_Name,
    Quantity, Unit_Price, Total_Price, Selling_Price, Discount, Paid_Amount, Remaining, Sub_Total, Total, Updated_Date) 
    VALUES 
    (@Invoice_no, @Date_of_Purchase, @Item_Code, @Item_Name, @Descriptin, @Supplier_Name, @Company_Name,
     @Quantity, @Unit_Price, @Total_Price, @Selling_Price, @Discount, @Paid_Amount, @Remaining, @Sub_Total,Total, @Updated_Date)
    END
    ELSE 
    UPDATE Purchase_Order_Log SET Invoice_no = @Invoice_no, Date_of_Purchase = 
    @Date_of_Purchase, Item_Code = @Item_Code, Item_Name = @Item_Name, 
    Descriptin = @Descriptin, Supplier_Name = @Supplier_Name, Company_Name = 
    @Company_Name, Quantity = @Quantity, Unit_Price = @Unit_Price, Total_Price = 
    @Total_Price, Selling_Price = @Selling_Price, Discount = @Discount, 
    Paid_Amount = @Paid_Amount, Remaining = @Remaining, Sub_Total = @Sub_Total, 
    Total = @Total, Updated_Date = @Updated_Date 
    WHERE Invoice_no = @Invoice_no AND Item_Code = @Item_Code

Modify your IF NOT EXISTS condition according to your requirement

score 0 · Answer 2 · answered Nov 06 '17 at 11:02

You can use the SQL Server Merge for accomplishing this using a single statement.Like This

MERGE dbo.Test WITH (SERIALIZABLE) AS T
USING (VALUES (3012, 'john')) AS U (id, name)
    ON U.id = T.id
WHEN MATCHED THEN 
    UPDATE SET T.name = U.name
WHEN NOT MATCHED THEN
    INSERT (id, name) 
    VALUES (U.id, U.name);

here it checks for value 3012 for Column ID on Table Test and updates the record if a match is found else insert the same.

How to update existing record and insert new record if not exist using c# Windows Form

2 Answers2