Duplicated row when inserting data into Datagridview using SQL store procedure and C# error

Question

I want to update Datagridview insert new row if not existing record and update if existing record by using SQL procedure as below: Edited

ALTER PROCEDURE [dbo].[proc_TBL_PROC_PMS_Update]
        @INDEXNO nvarchar(50),
        @_PROCESS_INST_NO nvarchar(50),
        @_Order_No nvarchar(50),
        @_GOODS_CD  nvarchar(50),
        @_GOODS_NAME nvarchar(50),
        @_LINE_NO       int,
        @_UNIT nvarchar(50),
        @_QTY int       
    AS  
        UPDATE [ENVNDIVDB].[dbo].[TBL_PROC_PMS] SET _GOODS_CD=@_GOODS_CD,_GOODS_NAME=@_GOODS_NAME,  _UNIT = @_UNIT,_QTY=@_QTY,
                [_UPDATE_DATE]=(select LEFT(CONVERT(VARCHAR, GETDATE(), 111), 10))                      
                 WHERE _PROCESS_INST_NO=@_PROCESS_INST_NO AND  _LINE_NO = @_LINE_NO 

     IF (@@ROWCOUNT = 0) 
        BEGIN
             INSERT INTO [TBL_PROC_PMS]([INDEXNO],[_PROCESS_INST_NO],[_Order_No],[_LINE_NO],[_GOODS_CD],[_GOODS_NAME],[_UNIT],[_QTY])   
                SELECT @INDEXNO, @_PROCESS_INST_NO,@_Order_No,@_LINE_NO,@_GOODS_CD,@_GOODS_NAME,@_UNIT,@_QTY  FROM [ENVNDIVDB].[dbo].[TBL_PROC_PMS] 
              WHERE @_LINE_NO NOT IN(SELECT _LINE_NO FROM [ENVNDIVDB].[dbo].[TBL_PROC_PMS] WHERE _PROCESS_INST_NO=@_PROCESS_INST_NO) AND _PROCESS_INST_NO=@_PROCESS_INST_NO
        END

And here is C# button Save click:

 var cmd2 = new SqlCommand("proc_TBL_PROC_PMS_Update", sqlConn);
                cmd2.CommandType = CommandType.StoredProcedure;

                foreach (DataGridViewRow item in grdMaterial.Rows)
                {                     
                    if (!item.IsNewRow)
                    {                           
                        cmd2.Parameters.Clear();
                        int rowindex = item.Index;
                        var _GOODS_CD = item.Cells[4].Value;
                        var _GOODS_NAME = item.Cells[5].Value;
                        var _UNIT = item.Cells[6].Value;
                        var _QTY = item.Cells[7].Value;
                        cmd2.Parameters.AddWithValue("@INDEXNO", lblINDEX.Text);
                        cmd2.Parameters.AddWithValue("@_PROCESS_INST_NO", txtInstNo.Text);
                        cmd2.Parameters.AddWithValue("@_Order_No", txtPONo.Text);
                        cmd2.Parameters.AddWithValue("@_LINE_NO", rowindex);
                        cmd2.Parameters.AddWithValue("@_GOODS_CD", _GOODS_CD);
                        cmd2.Parameters.AddWithValue("@_GOODS_NAME", _GOODS_NAME);
                        cmd2.Parameters.AddWithValue("@_UNIT",_UNIT);
                        cmd2.Parameters.AddWithValue("@_QTY", _QTY);
                        cmd2.ExecuteNonQuery();
                    }
                }

Before insert 1 new row:

Insert only 1 row :

But after saving, it duplicates 1 more row :

Answer 1

I solved my problem by modifiyng this procedure:

 --UPDATE EXISTING RECORDS      
                    UPDATE [ENVNDIVDB].[dbo].[TBL_PROC_PMS] SET _GOODS_CD=@_GOODS_CD,_GOODS_NAME=@_GOODS_NAME,  _UNIT = @_UNIT,_QTY=@_QTY,
                    [_UPDATE_DATE]=(select LEFT(CONVERT(VARCHAR, GETDATE(), 111), 10))                      
                     WHERE _PROCESS_INST_NO=@_PROCESS_INST_NO AND  _LINE_NO = @_LINE_NO 
        --INSERT NON-EXISTING RECORDS
         IF (@@ROWCOUNT = 0) 
            BEGIN
                 INSERT INTO [TBL_PROC_PMS]([INDEXNO],[_PROCESS_INST_NO],[_Order_No],[_LINE_NO],[_GOODS_CD],[_GOODS_NAME],[_UNIT],[_QTY],[_UPDATE_DATE])    
                 VALUES(@INDEXNO,@_PROCESS_INST_NO,@_Order_No,@_LINE_NO,@_GOODS_CD,@_GOODS_NAME,@_UNIT,@_QTY,(select LEFT(CONVERT(VARCHAR, GETDATE(), 111), 10)))

            END

Answer 2

Rather than doing both an UPDATE and an INSERT like your existing stored proc does (which will guarantee at least one extra row is added), you should try one of two options.

• Only run the INSERT if the UPDATE had no effect (by checking @@ROWCOUNT = 0)
• Use a MERGE