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
jet-blackAsked:
Who is Participating?
 
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
 
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
 
jet-blackAuthor 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
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

 
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
 
jet-blackAuthor Commented:
Thanks a lot. :)
0
 
jet-blackAuthor Commented:
Thank you for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.