Getting the output parameter value as DBNULL when using C# transaction

Question

Stored procedure, inserting to the table, which has an identity column as ID

CREATE PROCEDURE InsertValue
    @Value varchar(7),
    @NewId int = 0 OUTPUT
AS
BEGIN
    IF(NOT EXISTS(SELECT 1 FROM [Table1] WHERE Detail = @Value))
    BEGIN         
      INSERT into [Table1] (Detail)
      VALUES (@Value)
      SET @NewId = SCOPE_IDENTITY();
    END
END

C# code:

int newId=0;

SqlTransaction SqlTrans = null;

SqlConnection con = new SqlConnection("connection string");
con.Open();

cmd = new SqlCommand("InsertValue", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@Value", "123"));

SqlParameter parId= new SqlParameter("@NewId",DbType.Int32);
parId.Value= 0;
parId.Direction = ParameterDirection.Output;
cmd.Parameters.Add(parId);

SqlTrans = con.BeginTransaction();
cmd.Transaction = SqlTrans;
try
{
    cmd.ExecuteNonQuery();
    // parId.Value is DBNULL
    SqlTrans.Commit();
    newId = Convert.ToInt32(parId.Value);
    // Exception : This SqlTransaction has completed; it is no longer usable
}
catch (Exception e)
{
    throw;
}

So can anyone help me with this? I want the transaction to be there in C# code, but need the value from out parameter.

If I access the value before committing the transaction, it is DBNull and if access after committing, getting exception while casting(the value is still DBNull)

This SqlTransaction has completed; it is no longer usable.

" If i access the value before committing the transaction" - erm? — Mitch Wheat, Jan 22 '16 at 08:03
@MitchWheat - Nitin stated that he's in a dilemma. If he tries to access the parameter after the commit is completed he gets an exception. So he had a look at trying to get it before the commit - where (for reasons obvious to me and you - and I suspect him/her as well) it is NULL. Cannot get it before and cannot get it after - "What to do?" - *That* is the question. — Jens Meinecke, Jan 22 '16 at 08:07
@Übercoder lol... @Nitin of course it's coming as `NULL`, because the transaction is uncommitted and nothing has been inserted in the table yet (physically). Getting an exception about transaction no more available can cause a thing, that you are calling a trans for commit, but no trans is there? Question unclear, what error do you get without a trans and what exactly is your code — krtek, Jan 22 '16 at 08:11
The transaction is used in C# code not in the procedure. Updated the error s well. — Nitin, Jan 22 '16 at 08:46
@Nitin `cmd.Transaction = SqlTrans;` you don't need to do this, `BeginTransaction()` sets the transation to the connection. — krtek, Jan 22 '16 at 08:58
@krtek Actually the command execution method is in another class, i have added it here for the clarity. When removing the transaction assignment to the command, getting the below error: **"The Transaction property of the command has not been initialized."** So it is required to be set. — Nitin, Jan 22 '16 at 09:19
@Nitin yes you're right, well I Am out of ideas, seems good to me :S — krtek, Jan 22 '16 at 09:30
@Nitin Try this: Call `con.BeginTransaction()` **before** you assign the `cmd = new SqlCommand(...)`. and don't assign the `cmd.Transaction = SqlTrans;` — Jens Meinecke, Jan 22 '16 at 11:51

score 0 · Answer 1 · edited May 23 '17 at 11:44

0

Take a look at the answers to these existing questions for This SqlTransaction has completed; it is no longer usable. Perhaps your stored procedure already commits the transaction, or the SQL Server is killing the transaction for some reason?

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

SqlTransaction has completed

edited May 23 '17 at 11:44

Community

1
1

answered Jan 22 '16 at 08:52

Owen Pauling

11,349
20
53
64

Transaction committing or rollback is not a problem. Problem is getting the out parameter value. – Nitin Jan 22 '16 at 09:05

score 0 · Accepted Answer · answered Jan 27 '16 at 05:44

Thanks everyone for the support and help. Actually the problem was with the output parameter's default value. I forgot to specify the condition in the question(now i have added it), which was preventing the value of the output parameter to be set each time. As i was passing the output parameter value as 0 each time, so it was supposed to be 0 when it is not getting set in the SP. But, the output parameter was getting as DBNull in case it is not set in the procedure. So i changed the procedure to set the value of the parameter each time, irrespective of the condition.

CREATE PROCEDURE InsertValue
@Value varchar(7),
@NewId int = 0 OUTPUT
AS
BEGIN
    SET @NewId = 0; -- Added to set it to 0
    IF(NOT EXISTS(SELECT 1 FROM [Table1] WHERE Detail = @Value))
    BEGIN         
      INSERT into [Table1] (Detail)
      VALUES (@Value)
      SET @NewId = SCOPE_IDENTITY();
    END
END

Getting the output parameter value as DBNULL when using C# transaction

2 Answers2