Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2011
    Posts
    63

    Unanswered: Transaction error

    Getting the error:

    "Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1."

    After i try to execute the SP:

    Code:
    USE [dbTalesOfEpic]
    GO
    /****** Object:  StoredProcedure [dbo].[createAccount]    Script Date: 07/11/2011 13:19:09 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER PROCEDURE [dbo].[createAccount]
        
    	@username char(20), 
    	@email char(40), 
    	@password char(90), 
    	@verificationCode char(8),
    	
    	@class tinyint,
    	@gender char(6),
    	@charName char (20),
    	@strength tinyint,
    	@agility tinyint,
    	@magicalPower tinyint,
    	@hp integer,
    	@mana integer,
    	@initiative integer
    
    AS
    
    BEGIN TRY
    
    BEGIN TRANSACTION
    
    DECLARE @charID integer
    
    INSERT INTO Accounts ([username], [email], [password], [verified?], [enabled?], [verificationCode])
    VALUES (@username, @email, @password, 0, 1, @verificationCode)
    
    SET @charID = SCOPE_IDENTITY()
    
    INSERT INTO Characters([charId], [charName], [strength], [agility], [magicalPower], [hp], [mana], [initiative], [doingSomething?], [class], [gender])
    VALUES (@charID, @charName, @strength, @agility, @magicalPower, @hp, @mana, @initiative, 0, @class, @gender)
    
    INSERT INTO CharAdventure([charId])
    VALUES (@charID)
    
    INSERT INTO Equipment([charID])
    VALUES (@charID)
    
    RETURN 1
    
    COMMIT
    
    END TRY
    BEGIN CATCH
    
    RETURN 0
    
    IF @@TRANCOUNT>0
    	ROLLBACK
    
    END CATCH
    I have never used transactions before so i dont know if my code is correct.

    Thanks

  2. #2
    Join Date
    Mar 2007
    Location
    Holmestrand, Norway
    Posts
    332
    You get this error message because your code never reach the commit/rollback statement. You should either commit or rollback your changes before you return from the procedure.
    Ole Kristian Velstadbråten Bangås - Virinco - MSSQL.no - Facebook - Twitter

  3. #3
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    In other words, return is the end of the execution path. Anything after return is ignored.

  4. #4
    Join Date
    Jun 2011
    Posts
    63
    Got it to work by making it like this:

    Code:
    USE [dbTalesOfEpic]
    GO
    /****** Object:  StoredProcedure [dbo].[createAccount]    Script Date: 07/11/2011 15:35:55 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO
    
    ALTER PROCEDURE [dbo].[createAccount]
        
    	@username char(20), 
    	@email char(40), 
    	@password char(90), 
    	@verificationCode char(8),
    	
    	@class tinyint,
    	@gender char(6),
    	@charName char (20),
    	@strength tinyint,
    	@agility tinyint,
    	@magicalPower tinyint,
    	@hp integer,
    	@mana integer,
    	@initiative integer
    
    AS
    
    BEGIN TRY
    
    BEGIN TRANSACTION
    
    DECLARE @charID integer,
            @status tinyint
    
    INSERT INTO Accounts ([username], [email], [password], [verified?], [enabled?], [verificationCode])
    VALUES (@username, @email, @password, 0, 1, @verificationCode)
    
    SET @charID = SCOPE_IDENTITY()
    
    INSERT INTO Characters([charId], [charName], [strength], [agility], [magicalPower], [hp], [mana], [initiative], [doingSomething?], [class], [gender])
    VALUES (@charID, @charName, @strength, @agility, @magicalPower, @hp, @mana, @initiative, 0, @class, @gender)
    
    INSERT INTO CharAdventure([charId])
    VALUES (@charID)
    
    INSERT INTO Equipment([charID])
    VALUES (@charID)
    
    SET @status = 1
    
    COMMIT
    
    END TRY
    BEGIN CATCH
    
    SET @status = 0
    
    IF @@TRANCOUNT>0
    	ROLLBACK
    
    END CATCH
    
    RETURN @status
    Thanks!
    Last edited by TheGateKeeper; 07-11-11 at 11:35.

  5. #5
    Join Date
    Jun 2011
    Posts
    63
    Fixed Nevrmind
    Last edited by TheGateKeeper; 07-11-11 at 15:10.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •