"This SqlTransaction has completed; it is no longer usable."

Question

"This SqlTransaction has completed; it is no longer usable."

amol.parikh

Old Hand

Points: 388
More actions
January 24, 2011 at 1:50 pm

#230833

Hi,
I am trying to implement transactions in c# code. I get this error: "This SqlTransaction has completed; it is no longer usable." Can you tell me what can be the reason?
this is my code:
IDbTransaction t= appConnection.BeginTransaction();
t.Connection is populated before execution of
Database.ExecProcedure(appConnection, "usp_APPL_InsUpdEntity", locFieldList.ToArray(), locFieldValue.ToArray(), locFieldType.ToArray())
after this step, t.Connection shows null.
Can anyone please help me?
Thanks,
Mavericky

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Derrick Smith SSChampion Points: 10833 More actions · Answer 1

Can you post the code for the proc? From my limited C# knowledge it sounds like there's an internal transaction in the SP and no results are being returned. What happens when you run it manually?

amol.parikh Old Hand Points: 388 More actions · Answer 2

Here is the stored proc being called:

ALTER PROCEDURE [dbo].[usp_APPL_InsUpdEntity]

@ID INT=0, @Name VARCHAR (100)='', @ShortName VARCHAR (20)='', @Comments TEXT=''

AS

DECLARE @RID AS INT

-- =============================================

-- Description: Inserts/Updates Entity table

-- Changes:

-- 2010-12-07 / #119597 / AP / Created

-- =============================================

IF EXISTS (SELECT * FROM [dbo].[VW_CR_OP_Entity] WHERE ID=@ID)

BEGIN

SET @RID = @ID

UPDATE [CR_OP].[cr_operational].[dbo].[Entity]

SET

Name = @Name,

ShortName = @ShortName,

Comments = @Comments

WHERE ID =@RID

END

ELSE

BEGIN

EXEC [CR_OP].[cr_operational].[dbo].GetNextIndex 'PK_Entity',@RID OUTPUT,1

INSERT INTO [CR_OP].[cr_operational].[dbo].[Entity] (ID, Name, ShortName, Comments)

VALUES (@RID,@Name,@ShortName,@Comments)

END

BEGIN

SELECT @RID ID

END

amol.parikh Old Hand Points: 388 More actions · Answer 3

Also, it gives me the same error if I execute in this manner:

Database.ReadResultSet(appConnection, sqlEntityEntry);

where sqlEntityEntry = string.Format("exec usp_{0}_InsUpdEntity @ID={1}, @Name='{2}', @ShortName='{3}', @Comments='{4}'",AppDbPrefix, entityEntry.ID, entityEntry.Name.Replace(@"'", @"''"), entityEntry.ShortName.Replace(@"'", @"''"), entityEntry.Comments.Replace(@"'", @"''"));

Derrick Smith SSChampion Points: 10833 More actions · Answer 4

What if you execute the SP manually from SSMS passing the same variables?

Start at the very bottom and work your way up.

amol.parikh Old Hand Points: 388 More actions · Answer 5

As you can see, the SP has insert and update scenarios. Both the scenarios work fine if I execute in manually from SQL Server management studio.

Thanks,

Mavericky