CODEWITHSUNDEEP
c#t-sqltransactions
خيال النملة
خيال النملة

Reputation: 85

Error: The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

I have stored procedure and I have a transaction in it.

When I execute it with SQL Server management studio there are no errors, but when I execute it with my C# code the following error occurs :

The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

The code is:

CREATE PROCEDURE [dbo].[AddHardDisk]
    @name nvarchar(50), @brand nvarchar(50), @serial nvarchar(50), @cost float,  @seller nvarchar(50),@note nvarchar(1000),@buyDate Date,
    @size int ,@moneyType nvarchar(50)

AS

    DECLARE  @addedDeviceID int

 BEGIN TRY
    BEGIN  TRAN   

        insert into Product(Name,Brand,Serial,Cost,Seller,Note,MoneyType,BuyDate)
        values(@name,@brand,@serial,@cost,@seller,@note,@moneyType,@buyDate)

        select @addedDeviceID =SCOPE_IDENTITY()

        insert into HardDisk(ID,size)
        values(@addedDeviceID,@size) 

        exec DecreaseMoneyFromFund @moneyType,@cost         

    COMMIT           
END TRY
BEGIN CATCH

    IF @@TRANCOUNT > 0
        ROLLBACK
END CATCH

Upvotes: 2

Views: 9603

Answers (2)

wruckie
wruckie

Reputation: 1806

Here is the emply shell that i use for my Sp's. I think you might be missing the 'Commit Tran'

 BEGIN TRY ;
  BEGIN TRAN;
        BEGIN          

 --Insert SQL Here
        END
IF @@TRANCOUNT > 0  
COMMIT TRAN ;     
END TRY

BEGIN CATCH ;
  DECLARE @ERROR_MESSAGE  VARCHAR(200) ; 
  DECLARE @ERROR_SEVERITY  INT ;
  DECLARE @ERROR_STATE  INT ;

  SET @ERROR_MESSAGE = ERROR_MESSAGE() ;
  SET @ERROR_SEVERITY = ERROR_SEVERITY() ;
  SET @ERROR_STATE = ERROR_STATE() ;

  RAISERROR ( @ERROR_MESSAGE, -- Message text.
                    @ERROR_SEVERITY , -- Severity.
                    @ERROR_STATE -- State.
                 );

Select @ERROR_MESSAGE

IF @@TRANCOUNT > 0
        ROLLBACK TRAN ;

END CATCH ;

Upvotes: 0

Haney
Haney

Reputation: 34742

The scope of the TRAN is your TRY block, so in the catch where you roll back it does not exist. Switch it: BEGIN TRAN then BEGIN TRY.

The rollback must happen within the scope of the transaction, not before or after it. Revised code:

CREATE PROCEDURE [dbo].[AddHardDisk]
    @name nvarchar(50), @brand nvarchar(50), @serial nvarchar(50), @cost float,  @seller nvarchar(50),@note nvarchar(1000),@buyDate Date,
    @size int ,@moneyType nvarchar(50)

AS

    DECLARE  @addedDeviceID int

 BEGIN TRAN
    BEGIN  TRY   

        insert into Product(Name,Brand,Serial,Cost,Seller,Note,MoneyType,BuyDate)
        values(@name,@brand,@serial,@cost,@seller,@note,@moneyType,@buyDate)

        select @addedDeviceID =SCOPE_IDENTITY()

        insert into HardDisk(ID,size)
        values(@addedDeviceID,@size) 

        exec DecreaseMoneyFromFund @moneyType,@cost         

        COMMIT           
    END TRY
    BEGIN CATCH
        IF @@TRANCOUNT > 0
            ROLLBACK
    END CATCH
END TRAN

Upvotes: 2

Related Questions