locked
Error - This SqlTransaction has completed; it is no longer usable. RRS feed

  • Question

  • User-1499457942 posted

    Hi

      I am using begintransaction but still Data gets in 1 table saved even if there is error in 1 Transaction.

    Secondly Update Dtatement in Stored Procedure is giving above error.

    SqlTransaction trans;
    
                        trans = con.BeginTransaction("SampleTransaction");
                        {
                            try
                            {
                                using (cmd = new SqlCommand("Sp_ADDGSTIN", con, trans))
                                {
                                    cmd.CommandType = CommandType.StoredProcedure;
                                    cmd.Parameters.AddWithValue("@CustomerNo", Request.QueryString["cu"].ToString());
                                    cmd.Parameters.AddWithValue("@CustomerName", ltlrname.Text);
    Gstin);
    
                                    Id = Convert.ToInt32(cmd.ExecuteScalar());
                                    
                                }
    
                                string sql = "UPDATE [Test] SET [TMP GSTIN]=@GSTIN where [No_]=@Customer";
                                using (cmd = new SqlCommand(sql, con, trans))
                                {
                                    cmd.CommandType = CommandType.Text;
                                    cmd.Parameters.AddWithValue("@Customer", Request.QueryString["cu"].ToString());
                                    cmd.Parameters.AddWithValue("@GSTIN", txtGSTIN.Text.Trim());
                                    cmd.ExecuteNonQuery();
                                }
                                Gstin = Id.ToString() + "_" + "G" + Request.QueryString["cu"].ToString() + "_" + fppan.FileName.ToString();
                                fppan.SaveAs(Server.MapPath("~") + "/GSTIN/" + Gstin);
    
                                trans.Commit();
                                
                                string message = "Information Uploaded Successfully !";
                                
                                ClientScript.RegisterStartupScript(this.GetType(), "Popup", "ShowPopup('" + message + "');", true);
                            }
                            catch (Exception ex)
                            {
                                trans.Rollback();
                                ClientScript.RegisterStartupScript(this.GetType(), "alert", "alert(\"" + ex.Message.ToString() + "\");", true);
                            }
                            finally
                            {
                                con.Close();
                            }
                        }
    
    USE [xyz]
    GO
    /****** Object:  StoredProcedure [dbo].[Sp_AddGSTIN]    Script Date: 03/07/2018 01:05:36 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    -- =============================================
    -- Author:		<Author,,Name>
    -- Create date: <Create Date,,>
    -- Description:	<Description,,>
    -- =============================================
    ALTER PROCEDURE [dbo].[Sp_AddGSTIN] 
    	-- Add the parameters for the stored procedure here
    	@CustomerNo nvarchar(15),
    	@CustomerName nvarchar(50)
    AS
    BEGIN
    	--	@id int output
    	Declare @Id as Int;
    	-- SET NOCOUNT ON added to prevent extra result sets from
    	-- interfering with SELECT statements.
    	SET NOCOUNT ON;
    
        -- Insert statements for procedure here
    	Insert into [tbl_IN] ([CustomerNo],[CustomerName]) values (@CustomerNo,@CustomerName)
    	SELECT SCOPE_IDENTITY();
    	SET @id=SCOPE_IDENTITY()
    	Update [tbl_IN] set [GSTINLink] = @id + '_' + [GSTINLINK] where [ENo] = @id
        --RETURN  @id
    END
    

    Thanks

    Tuesday, July 3, 2018 10:34 AM

All replies

  • User475983607 posted

    The transaction wraps the entire try/catch block where the finally block closes the connection and transaction.  Place the transaction as close to the SQL code as possible plus use a using block.

    The docs cover transaction quite well.

    https://msdn.microsoft.com/en-us/library/ms971557.aspx

    https://docs.microsoft.com/en-us/dotnet/framework/data/adonet/local-transactions

    https://msdn.microsoft.com/en-us/library/86773566(v=vs.110).aspx

    The C# programming guide covers using blocks.

    https://docs.microsoft.com/en-us/dotnet/csharp/language-reference/keywords/using-statement

    I suggest that you try to learn the technology rather than guessing.

    Edit: Plus your stored procedure is questionable.  These lines of code have no purpose.

    	SELECT SCOPE_IDENTITY();
    	SET @id=SCOPE_IDENTITY()
    	Update [tbl_IN] set [GSTINLink] = @id + '_' + [GSTINLINK] where [ENo] = @id

    ENo and GSTINLink exist in the record so there is no reason to concatenate the values in another column.  It only complicates the code and potential data error later if either column is updated.

    Tuesday, July 3, 2018 10:56 AM
  • User-1499457942 posted

    Hi

     What is wrong in the below code

    SET @id=SCOPE_IDENTITY()
    	Update [tbl_IN] set [GSTINLink] = @id + '_' + [GSTINLINK] where [ENo] = @id

    Thanks
    Tuesday, July 3, 2018 11:29 AM
  • User475983607 posted

    JagjitSingh

    Hi

     What is wrong in the below code

    SET @id=SCOPE_IDENTITY()
    	Update [tbl_IN] set [GSTINLink] = @id + '_' + [GSTINLINK] where [ENo] = @id
    

    Both values already exist in the record.  There is no reason to create another column value with essentially the same values.  Your approach goes against standard DB normalization rules and can cause overly complicated code.  If you need to concatenate the values just do so in a SELECT query and concatenate the values in the SELECT.

    Tuesday, July 3, 2018 11:33 AM
  • User-1499457942 posted

    Hi

       I am not creating new column . I just want to Update GSTINLINK value with existing value + ID value in the beginning

    New value of GSTINLINK will be - 

    @id + '_' + [GSTINLINK]

    Thanks

    Tuesday, July 3, 2018 11:41 AM
  • User475983607 posted

    JagjitSingh

    Hi

       I am not creating new column . I just want to Update GSTINLINK value with existing value + ID value in the beginning

    New value of GSTINLINK will be - 

    @id + '_' + [GSTINLINK]

    This is your code and you can write it anyway you like.  I simply explaining that the approach goes against DB normalization rules.  The Id field and GSTINLink columns already exists.  There is no reason to concatenate the values.  If you need a compound key then craft a compound key or multiple joins.

    IMHO, this code would fail many code reviews - it is a code smell.  Again, it's up to you but I would refactor.

    Tuesday, July 3, 2018 11:51 AM