This should be a fairly simple question. It's based on this error message:
"Transaction count after EXECUTEindicates that a COMMIT or ROLLBACK TRANSACTION statement is missing.Previous count = 1, current count = 0."
I get this when executing a stored procedure upon processing a form. This error happens when I intentionally provide input to the stored procedure that I know should cause it to error out. I catch the exception, and it contains the error message, but it also contains the above message added on to it, which I don't want.
I won't post the entire stored procedure. But I'll list a digest of it (Just those lines that are significant). Assume that what's included is what happens when I provide bad input:
BEGIN
BEGIN TRY
BEGIN TRANSACTION
RAISERROR('The item selected does not exist in the database.', 16, 1);
COMMIT -- This won't execute when the RAISERROR breaks out to the CATCH block
END TRY
BEGIN CATCH
ROLLBACK
DECLARE @.ErrorSeverity INT, @.ErrorMessage NVARCHAR(4000)
SET @.ErrorSeverity = ERROR_SEVERITY()
SET @.ErrorMessage = ERROR_MESSAGE()
RAISERROR(@.ErrorMessage, @.ErrorSeverity, 1)
END CATCH
END
Okay, so that works fine. The problem is when I execute this with an SqlCommand object, on which I've opened a transaction. I won't include the entire setup of the data (with the parameters, since those seem fine), but I'll give my code that opens the connection and executes the query:
con.Open();
SqlTransaction transaction = con.BeginTransaction();
command.Transaction = transaction;
try
{
command.ExecuteNonQuery();
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
}
finally
{
con.Close();
}
I'm calling the stored procedure listed above (which has its own transaction), using a SqlCommand object on which I've opened a transaction. When there is no error it works fine. But when I give the stored procedure bad data, it gives me that message about the transaction count.
Is there something I need to do in either my SQL or my C# to handle this? The entire message found in the Exception's Message is a concatenation of the message in my RAISERROR, along with the transaction count message I quoted at the beginning.
Thanks,
-Dan
In playing around with it, I've come to learn that the Exception.Message is the concatenation of any messages the exception holds. So, my CATCH block could contain this instead:
Label.Text = String.Format("Error: {0}", ex.Errors[0].Message);
That only gives the first error, which is the one I want (the one specified in RAISERROR). I just don't know if this is what I should do. Is that transaction count message something I should be concerned about, or will that always happen? Should I just use the first message in the exception?
Thanks.
No comments:
Post a Comment