Click here to Skip to main content
15,867,835 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
Hi My Stored procedure is getting errors when executing from VB " Transaction count after Execute indicates a mismatching of Begin and Commit statments, previouse count =0, current count =1,

What I have tried:

ALTER PROCEDURE [dbo].[SMlineUpdate3]
(
	@id [int],
	@Payroll_Id [int],
	@ProductCode  nvarchar(255),
	@Description nvarchar (255),
	@Qty  nvarchar(255)
)
AS
IF EXISTS(SELECT Id from Smline where @id = Id)
		DECLARE @User INT
BEGIN TRANSACTION

Update dbo.SmLine
	Set [Payroll_Id]= @Payroll_Id
,ProductCode= @ProductCode
,Description = @Description
,Qty = @Qty
	
 Where [Id]=@Id 
IF @@ERROR <> 0
BEGIN      
    ROLLBACK     
    RETURN 
END 
ELSE

BEGIN TRANSACTION 
DECLARE @SId INT

INSERT INTO SmLine ([Payroll_Id],[ProductCode],[Description],[Qty]) VALUES (@Payroll_Id,@ProductCode ,@Description,@Qty)

SET @SId= SCOPE_IDENTITY() 
IF @@ERROR <> 0
BEGIN      
    ROLLBACK     
    RETURN 
END   

COMMIT
Posted
Updated 29-Mar-19 6:20am
Comments
RedDk 29-Mar-19 15:00pm    
You BEGIN a TRANSACTION which you never END ... this is very basic and easy to spot. Use the help BOL which is accessible under the {?} of the version of SSMSE you are using.

1 solution

You have a BEGIN at the start of the sp and a BEGIN at the start of the ELSE and only one COMMIT at the bottom. Either COMMIT the one inside the ELSE or remove the BEGIN for the one in the ELSE, whichever suits your requirements.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900