Problem with SQLCeDataReader and transaction

[Janez]

Hi,

I have problem starting transaction with sql server ce 2.0 after I
return datareader from a function.
I have created sample application with three buttons: first one opens
connection, second iterates through datareader returned from a
function and the third starts and then commits or rollbacks a
transaction. Here is my source:

Private sqlconn As SqlCeConnection

'create connection to the database
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button1.Click
sqlconn = New SqlCeConnection
sqlconn.ConnectionString = "data source=...;SSCEatabase
Password=.."
sqlconn.Open()
end sub

'return reader and diplay data
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button2.Click
Dim sqlReader As SqlCeDataReader
sqlReader = getData()
While sqlReader.Read
.....
End While
sqlReader.Close()
End Sub

'called function
Private Function getdata() As SqlCeDataReader
Dim sqlCmd As SqlCeCommand
sqlCmd = New SqlCeCommand
Try
With sqlCmd
.Connection = sqlconn
.CommandType = Data.CommandType.Text
.CommandText = "select * from sometable"
Return .ExecuteReader
End With
Finally
If Not IsNothing(sqlCmd) Then
sqlCmd.Dispose()
End If
End Try
End Function

'now create transaction to insert some data
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button3.Click
Dim sqlCmd As SqlCeCommand
sqlCmd = New SqlCeCommand
Try
With sqlCmd
.Connection = sqlconn
.CommandType = Data.CommandType.Text
.Transaction = sqlconn.BeginTransaction
........
.Transaction.Commit()
End With
Catch ex As SqlCeException
sqlCmd.Transaction.Rollback()
Finally
If Not IsNothing(sqlCmd) Then
sqlCmd.Dispose()
End If
End Try
End Sub

If I first click Button1, then Button2 and Button3 the exception is
thrown at the line .Transaction = sqlconn.BeginTransaction when I
click Button3.
The exception returns: "BeginTransaction requires an open and
available Connection. The connection's current state is Open,
Fetching."
The only workaround I have found is to close connection and open it
again prior to BeginTransaction.

The problem does not show up if I execute reader in button2 click, so
the reader is not returned from the function.

Is this expected behavior or am I missing something?

Regards
Janez

 

[Alex Feinman [MVP]]

The problem in your code stems from your calling Dispose on a command prior
to accessing the associated DataReader.
Change the code in Button2_Click to call cmd.Dispose after the call to
DataReader.Dispose

 

[Janez]

Thanks Alex,

it's working now.

Janez