SELECT Command Paramaters with Transaction

Hi.

I have a transaction which includes several insert/update commands. (also select commands)

When I use
command.parameters.addwithvalue, in commands with SELECT clause
it throws following error:

The transaction operation cannot be performed because there are pending requests working on this transaction.

If I set it like the below, it works well. (I simplified the code)
What is wrong with this usage and how can I correct it?

By the way, the MARS is enabled in my connection.

Thanks.


///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
Dim cnDatabase As New System.Data.SqlClient.SqlConnection(cnString)
cnDatabase.Open()

Dim commandRecords As New System.Data.SqlClient.SqlCommand
commandRecords.Connection = cnDatabase
commandRecords.CommandType = CommandType.Text

Dim trans1 As SqlClient.SqlTransaction
trans1 = cnDatabase.BeginTransaction

commandRecords.Transaction = trans1
commandRecords.CommandText = "SELECT * FROM records WHERE record_id = @recordId" 'if I set this like: "SELECT * FROM records WHERE record_id = " & variable_record_id   and comment out the next line, it doesn't throws error
commandRecords.Parameters.AddWithValue("@recordId", variable_record_id)

Dim dataReaderRecords As System.Data.SqlClient.SqlDataReader
dataReaderRecords = commandRecords.ExecuteReader

While dataReaderRecords.Read
'here there are several commands related to the transaction
End While

trans1.Commit() 'this line gives the error: "The transaction operation cannot be performed because there are pending requests working on this transaction." if I add parameter with addwithvalue(),       It works well if I dont.
cnDatabase.Close()
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
LVL 12
Ovunc TukenmezSoftware DeveloperAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alfred A.Commented:
Hi,

Have you tried delaying the setting of the transaction by reversing the sequence such as,

commandRecords.CommandText = "SELECT * FROM records WHERE record_id = @recordId" 'if I set this like: "SELECT * FROM records WHERE record_id = " & variable_record_id   and comment out the next line, it doesn't throws error
commandRecords.Parameters.AddWithValue("@recordId", variable_record_id)
commandRecords.Transaction = trans1
0
Alfred A.Commented:
Oh, I think I found the cause of your problem.  You should close your dataReaderRecords

dataReaderRecords.Close()

before doing a trans1.Commit()
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ovunc TukenmezSoftware DeveloperAuthor Commented:
@Alfred1
Yes, you're right.
I didn't noticed that.
Thanks.

Do you know why the transaction doesn't throws error if I don't use the parameters?
0
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

Alfred A.Commented:
Hi,

Good question.  I encountered this error a few years back and as I recall, I found the solution by accident then.  My understanding on this is that there might be an implicit transaction happening within the backend DB when a parameter is used within a datareader and if this implicit transaction is still pending, then you get the error.

Closing the data reader, commits the implicit transaction and trans1.Commit finally completes.

Just my two cents of it. :-)
0
Ovunc TukenmezSoftware DeveloperAuthor Commented:
Thanks a lot. :)
0
Ovunc TukenmezSoftware DeveloperAuthor Commented:
Thank you for your help.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.