J
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
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