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

[BobRoyAce]

I have an application that performs a series of DB actions, using
OLEDB to SQL Server. I use code as shown below to create a DB
connection, and transaction. When the code that attempts to Commit the
transaction (i.e. m_cnnTransaction.Commit()) executes, I get an error
message that says "The transaction operation cannot be performed
because there are pending requests working on this transaction". I
can't figure out why this is happening to cause this. This has worked
for months, and recently "broke." I must have changed something, but
can't find what was changed that could cause this (using Visual
Sourcesafe).

Any ideas as to what could be causing this?

--- CODE BEGINS ---

m_cnn = New
SqlClient.SqlConnection(My.Settings.GrpDBConnectionString)
m_cnn.Open()
m_cnnTransaction = m_cnn.BeginTransaction()

Try
MerchantProcessorsControl1.PostData(m_cnn, m_cnnTransaction)

_CurrentApp.PostData(m_cnn, m_cnnTransaction)

m_cnnTransaction.Commit()

Return eRecordSavedResult.RecordSaved
Catch ex As Exception
ShowWarningMessage("Error occurred trying to save Application
data." & vbCrLf & ex.Message & vbCrLf & ex.StackTrace)

m_cnnTransaction.Rollback()

Return eRecordSavedResult.RecordNotSaved
Finally
m_cnn = Nothing
m_cnnTransaction = Nothing
End Try

--- CODE ENDS ---

[diego]

what is MerchantProcessorsControl1.PostData(m_cnn, m_cnnTransaction)? Does it run asynchronously?

[BobRoyAce]

what is  MerchantProcessorsControl1.PostData(m_cnn, m_cnnTransaction)? Does it run asynchronously?

That routine looks like follows...

Public Sub PostData( _
ByRef cnn As SqlClient.SqlConnection, _
ByRef trx As SqlClient.SqlTransaction)
If (_CurrentMerchant IsNot Nothing) _
AndAlso (_CurrentMerchant.Processors IsNot Nothing) Then
_CurrentMerchant.Processors.PostData(cnn, trx)
End If
End Sub

That calls...

Public Sub PostData( _
ByRef cnn As SqlClient.SqlConnection, _
ByRef trx As SqlClient.SqlTransaction)
For Each oMerchantProcessor As MerchantProcessor In
_MerchantProcessors
If (oMerchantProcessor.HasChanged) OrElse
(oMerchantProcessor.IsNew) Then
oMerchantProcessor.PostData(cnn, trx)
End If
Next
End Sub

However, when this code is called, there are no MerchantProcessors to
save, so the oMerchantProcessor.PostData method is never called.

_CurrentApp.PostData does actually get called, which calls code
like...

Dim SQLParam(34) As SqlClient.SqlParameter
'... populates all SqlParameters...
ExecuteStoredProcedureInTransaction("usp_MerchantApps_Insert",
cnn, trx, SQLParam)

ExecuteStoredProcedureInTransaction looks like follows:

Public Function ExecuteStoredProcedureInTransaction(ByVal
sProcedureName As String, _
ByRef cnn As SqlClient.SqlConnection, _
ByRef trx As SqlClient.SqlTransaction, _
ByVal ParamArray Params As SqlClient.SqlParameter()) As Boolean
Dim cmd As New SqlClient.SqlCommand()
Dim iNumOfRecsAffected As Integer

If (Not cnn.State = ConnectionState.Open) Then
cmd.Connection.Open()
End If
cmd.Connection = cnn
cmd.Transaction = trx
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = sProcedureName

Try
cmd.Parameters.AddRange(Params)

iNumOfRecsAffected = cmd.ExecuteNonQuery()

Return True
Catch ex As Exception
MessageBox.Show("Error occurred trying to execute stored
procedure: " & sProcedureName & vbCrLf & ex.Message & ex.StackTrace,
"ERROR", MessageBoxButtons.OK, MessageBoxIcon.Warning)

Return False
End Try
End Function ' ExecuteStoredProcedureInTransaction

Then, later other routines are called that do the same thing, but with
different stored procedures. After that, I try to Commit with the
aforementioned error popping up.

Just FYI, before calling the above code, a few queries are run that
look like follows:

Dim sSQL As String
Dim sResult As String = String.Empty

sSQL = "SELECT AppSettingValue FROM AppSettings WHERE
(AppSettingName = '" & sSettingName & "')"

Dim rdr As SqlClient.SqlDataReader = GetDataReaderForSQL(sSQL)
Try
If rdr.HasRows Then
rdr.Read()
If Not IsDBNull(rdr(0)) Then
sResult = rdr(0)
End If
End If

Return sResult
Catch ex As Exception
MessageBox.Show("Error trying to determine App Setting: " &
sSettingName & ".", _
APP_NAME, MessageBoxButtons.OK, MessageBoxIcon.Warning)

If (rdr IsNot Nothing) AndAlso (Not rdr.IsClosed) Then
rdr.Close()
End If
rdr = Nothing

'TODO: Deal with this exception in better way?!
Finally
If (rdr IsNot Nothing) AndAlso (Not rdr.IsClosed) Then
rdr.Close()
End If
rdr = Nothing
End Try

and GetDataReaderForSQL looks like follows:

Public Function GetDataReaderForSQL(ByVal sSQL As String) As
SqlClient.SqlDataReader
Dim cnn As New
SqlClient.SqlConnection(My.Settings.GrpDBConnectionString.ToString)
cnn.Open()

Dim cmd As New SqlClient.SqlCommand(sSQL, cnn)

Dim rdr As SqlClient.SqlDataReader = Nothing

Try
rdr =
cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection)
Catch ex As Exception
MessageBox.Show("GetDataReaderForSQL" & vbCrLf & vbCrLf &
ex.Message & vbCrLf & ex.StackTrace, "ERROR", _
MessageBoxButtons.OK, MessageBoxIcon.Warning)

'If the data reader exists and is not closed, then close it
If (rdr IsNot Nothing) AndAlso (rdr.IsClosed = False) Then
rdr.Close()
End If

'If the connection exists and is not closed, then close it
If (cnn IsNot Nothing) AndAlso (cnn.State <>
ConnectionState.Closed) Then
cnn.Close()
End If
End Try

Return rdr
End Function ' GetDataReaderForSQL

NOTE: This function is called several times BEFORE the connection and
transaction are created.

Any ideas?

[diego]

If (Not cnn.State = ConnectionState.Open) Then
cmd.Connection.Open()
End If
cmd.Connection = cnn
cmd.Transaction = trx
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = sProcedureName

Try this:

If (Not cnn.State = ConnectionState.Open) Then
cnn.Open()
End If
cmd = cnn.CreateCommand()
cmd.Transaction = trx
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = sProcedureName

you were trying to open a connection thru a cmd which doesn't have a defined connection object.

[BobRoyAce]

Thanks...your suggested changes make sense to me. However, after
making the changes, I still have the same problem. Any other ideas?