Cannot roll back INSERT. No transaction or savepoint ... ASP.Net using SQL

Hi, I am recieving the following error in my web app '
Cannot roll back INSERT. No transaction or savepoint of that name was found.
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2.
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2.'

This error is caputred  in my catch statement 'catch (SqlException ex)', the stored procedure works fine if I exectue it manually. I have attached the code, can anyone spot whats wrong ?

The MoveToClose is kicked off by pressing a form button, which then fires the stored procedure off.
ASP.Net code : 
    protected void MoveToClose(string claim_Ref, string TP_Vehicle_Reg, string TP_Name, string IncidentMonth)
    {
          string spUpdate = "usp_CH_Claim_MoveToClose";
 
        // Definition Variables 
        string userName = string.Empty;
        int success = int.MinValue;
 
        try
        {
            userName = User.Identity.Name.Remove(0, 4).ToUpper();
        }
        catch { }
 
        SqlConnection conn = new SqlConnection(NUSecurity.CHConnection_String());
 
        conn.Open();
 
        SqlTransaction trans = conn.BeginTransaction();
 
        try
        {
            SqlCommand cmdUpdateDeal = new SqlCommand(spUpdate, conn);
 
            cmdUpdateDeal.Transaction = trans;
            cmdUpdateDeal.CommandType = CommandType.StoredProcedure;
 
            SqlParameter[] param = {                                         
                    new SqlParameter("@Claim_Ref", claim_Ref),
                    new SqlParameter("@TP_Vehicle_Reg", TP_Vehicle_Reg),
                    new SqlParameter("@Third_Party_Name", TP_Name ),
                    new SqlParameter("@Incident_Month", lblIncidentMonth.Text),
                                    };
 
            foreach (SqlParameter pram in param)
                cmdUpdateDeal.Parameters.Add(pram);
 
            success = Convert.ToInt32(cmdUpdateDeal.ExecuteScalar());
 
            trans.Commit();
        }
        catch (SqlException ex)
        {
            trans.Rollback();
        }
        finally
        {
            conn.Close();
        }
    }
 
Stored Procedure:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
 
ALTER PROCEDURE [dbo].[usp_CH_Claim_MoveToClose]
 
@Claim_Ref varchar(30),
@TP_Vehicle_Reg varchar(10),
@Third_Party_Name varchar(50),
@Incident_Month varchar(2)
 
AS
BEGIN
SET NOCOUNT ON;
 
-- This section inserts Claims row into closed claim tbl 
EXEC('	BEGIN TRY 
BEGIN TRANSACTION [INSERT]; 
INSERT INTO dbo.tbl_CH_ClosedClaims'+ @Incident_Month +'
 
Select *
FROM dbo.tbl_CH_Claims
WHERE   [Claim_Reference] = ''' + @Claim_Ref + ''' 
AND [Third_Party_Name] = ''' + @Third_Party_Name + ''' 
AND [TP_Vehicle_Reg] = ''' + @TP_Vehicle_Reg + ''' 
 
COMMIT TRANSACTION [INSERT]; 
 
SELECT 1
END TRY 
 
BEGIN CATCH 
ROLLBACK TRANSACTION [INSERT]; 
SELECT  0
END CATCH 
')
 
-- This section inserts Claim progress row into closed claim progress tbl 
 
BEGIN TRY 
BEGIN TRANSACTION [INSERT2]; 
INSERT INTO [dbo].[tbl_CH_ClosedClaims_Progress]
 
Select *
FROM [dbo].[tbl_CH_Claims_Progress]
 
WHERE   [Claim_Reference] = @Claim_Ref
AND [Third_Party_Name] = @Third_Party_Name 
AND [TP_Vehicle_Reg] = @TP_Vehicle_Reg
 
COMMIT TRANSACTION [INSERT2]; 
 
SELECT 1
END TRY 
 
BEGIN CATCH 
ROLLBACK TRANSACTION [INSERT2]; 
SELECT  0
END CATCH 
 
-- This section deletes the claim from the claim tbl
 
BEGIN TRY 
BEGIN TRANSACTION [DELETE]; 
DELETE FROM [dbo].[tbl_CH_Claims]
 
WHERE
Claim_Reference = @Claim_Ref
AND Third_Party_Name = @Third_Party_Name
AND TP_Vehicle_Reg = @TP_Vehicle_Reg
 
COMMIT TRANSACTION [DELETE]; 
 
SELECT 1
END TRY 
 
BEGIN CATCH 
ROLLBACK TRANSACTION [DELETE]; 
SELECT  0
END CATCH 
 
-- This section deletes the claim from the claim progess tbl
 
BEGIN TRY 
BEGIN TRANSACTION [DELETE2]; 	
DELETE FROM [dbo].[tbl_CH_Claims_Progress]
 
WHERE
Claim_Reference = @Claim_Ref
AND Third_Party_Name = @Third_Party_Name
AND TP_Vehicle_Reg = @TP_Vehicle_Reg
 
COMMIT TRANSACTION [DELETE2]; 
 
SELECT 1
END TRY 
BEGIN CATCH 
ROLLBACK TRANSACTION [DELETE2]; 
SELECT  0
END CATCH 
 
END

Open in new window

Mark_WalkAsked:
Who is Participating?
 
Mark_WalkConnect With a Mentor Author Commented:
I have resloved now by removing the EXEC statement and using a series of IF statements instead in my SP. Code is much longer but it works. :-)
0
 
reb73Connect With a Mentor Commented:
BEGIN TRANSACTION...COMMIT/ROLLBACK is typically used to process several insert/update/delete statements as a single batch, so I don't really see the point in having three different BEGIN TRAN..COMMIT for three separate INSERT/DELETE statements..

Change the SP to have a single TRY..CATCH and perform all insert/deletes within a single transaction
0
 
Mark_WalkAuthor Commented:
Hi, I have modified the SP to have a single TRY -- CATCH, but I am now recieving an error on the following C# command 'trans.Commit();'  --> 'This SqlTransaction has completed; it is no longer usable.'
I haven't come across this before.  Mark




set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
 
 
ALTER PROCEDURE [dbo].[usp_CH_Claim_MoveToClose]
 
@Claim_Ref varchar(30),
@TP_Vehicle_Reg varchar(10),
@Third_Party_Name varchar(50),
@Incident_Month varchar(2)
 
--DECLARE @Incident_Month varchar(2)
--DECLARE @Claim_Ref varchar(30)
--DECLARE @Third_Party_Name varchar(50)
--DECLARE @TP_Vehicle_Reg varchar(10)
--DECLARE @Last_Update_User varchar(10)
--
--SET @Incident_Month = '10'
--SET @Claim_Ref = '002807229'
--SET @Third_Party_Name = 'BODEY R'
--SET @TP_Vehicle_Reg = 'L80DEY'
--SET @Last_Update_User = 'WALKEM6'
 
AS
BEGIN
SET NOCOUNT ON;
 
-- This section inserts Claims row into closed claim tbl 
 
BEGIN TRY 
BEGIN TRANSACTION 
EXEC('
INSERT INTO dbo.tbl_CH_ClosedClaims'+ @Incident_Month +'
 
Select *
FROM dbo.tbl_CH_Claims
WHERE   [Claim_Reference] = ''' + @Claim_Ref + ''' 
AND [Third_Party_Name] = ''' + @Third_Party_Name + ''' 
AND [TP_Vehicle_Reg] = ''' + @TP_Vehicle_Reg + ''' 			
')
 
---- This section inserts Claim progress row into closed claim progress tbl 
 
INSERT INTO [dbo].[tbl_CH_ClosedClaims_Progress]
 
Select *
FROM [dbo].[tbl_CH_Claims_Progress]
 
WHERE   [Claim_Reference] = @Claim_Ref
AND [Third_Party_Name] = @Third_Party_Name 
AND [TP_Vehicle_Reg] = @TP_Vehicle_Reg
 
-- This section deletes the claim from the claim tbl
 
print 'Started delete'
DELETE FROM [dbo].[tbl_CH_Claims]
 
WHERE
Claim_Reference = @Claim_Ref
AND Third_Party_Name = @Third_Party_Name
AND TP_Vehicle_Reg = @TP_Vehicle_Reg
 
-- This section deletes the claim from the claim progess tbl
 
print 'Started delete2'	
DELETE FROM [dbo].[tbl_CH_Claims_Progress]
 
WHERE
Claim_Reference = @Claim_Ref
AND Third_Party_Name = @Third_Party_Name
AND TP_Vehicle_Reg = @TP_Vehicle_Reg
 
COMMIT TRANSACTION
SELECT 1
print 'Commited Transaction'
END TRY 
 
BEGIN CATCH 
print 'Started Rollback'
ROLLBACK TRANSACTION 
SELECT  0
END CATCH 
 
END

Open in new window

0
 
reb73Connect With a Mentor Commented:
Can you try just executing the stored procedure directly from SQL Query Analyzer? Does it succeed??

I think EXEC statement is forcing an implicit commit of the transaction leading to problems when you try to bundle it with the non EXEC code..

Since you are using the .net code to start and commit a transaction, try dispensing with the BEGIN TRAN..COMMIT TRAN in your stored procedure or the other way around..
0
 
Mark_WalkAuthor Commented:
I have run the SP in the query analyser, and it moves the data as required & returns 1. I have also moved all the code inside the EXEC statements as shown below and it also works in the query analyser.

When I removed the BEGIN TRAN..COMMIT TRAN from the SP, I get no error from the .net code but it does not move the data. I then tried it the other way round and I still get the 'This SqlTransaction has completed; it is no longer usable.' error.

I think it back to basics just adding one INSERT/DELETE block at a time and testing. Its fustrating as I use this framework all the time!!! Mark

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
 
 
ALTER PROCEDURE [dbo].[usp_CH_Claim_MoveToClose]
 
@Claim_Ref varchar(30),
@TP_Vehicle_Reg varchar(10),
@Third_Party_Name varchar(50),
@Incident_Month varchar(2)
 
--DECLARE @Incident_Month varchar(2)
--DECLARE @Claim_Ref varchar(30)
--DECLARE @Third_Party_Name varchar(50)
--DECLARE @TP_Vehicle_Reg varchar(10)
--DECLARE @Last_Update_User varchar(10)
--
--SET @Incident_Month = '9'
--SET @Claim_Ref = '002807525'
--SET @Third_Party_Name = 'THOMPSON H'
--SET @TP_Vehicle_Reg = 'OW56FMY'
--SET @Last_Update_User = 'WALKEM6'
 
AS
BEGIN
SET NOCOUNT ON;
 
-- This section inserts Claims row into closed claim tbl		
 
EXEC('
BEGIN TRY 
 
INSERT INTO dbo.tbl_CH_ClosedClaims'+ @Incident_Month +'	
Select *
FROM dbo.tbl_CH_Claims
WHERE   [Claim_Reference] = ''' + @Claim_Ref + ''' 
	AND [Third_Party_Name] = ''' + @Third_Party_Name + ''' 
	AND [TP_Vehicle_Reg] = ''' + @TP_Vehicle_Reg + ''' 			
 
INSERT INTO [dbo].[tbl_CH_ClosedClaims_Progress]
Select *
FROM [dbo].[tbl_CH_Claims_Progress]
WHERE   [Claim_Reference] = ''' + @Claim_Ref + ''' 
	AND [Third_Party_Name] = ''' + @Third_Party_Name + ''' 
	AND [TP_Vehicle_Reg] = ''' + @TP_Vehicle_Reg + ''' 	
 
DELETE FROM [dbo].[tbl_CH_Claims]
WHERE   [Claim_Reference] = ''' + @Claim_Ref + ''' 
	AND [Third_Party_Name] = ''' + @Third_Party_Name + ''' 
	AND [TP_Vehicle_Reg] = ''' + @TP_Vehicle_Reg + ''' 	
 
DELETE FROM [dbo].[tbl_CH_Claims_Progress]
WHERE   [Claim_Reference] = ''' + @Claim_Ref + ''' 
	AND [Third_Party_Name] = ''' + @Third_Party_Name + ''' 
	AND [TP_Vehicle_Reg] = ''' + @TP_Vehicle_Reg + ''' 	
 
SELECT 1
END TRY
 
BEGIN CATCH 
 
SELECT  0
END CATCH 
 
')
 
END

Open in new window

0
All Courses

From novice to tech pro — start learning today.