Execute requires the command to have a transaction object when the connection assigned to the comman

[Victor]

Hi there,

I have this code to select and insert data into the database. It all
works fine if i don't use a transaction but if i do i get the message:
Execute requires the command to have a transaction object when the
connection assigned to the command is in a pending local transaction.

What a i doing wrong here, please help!

Dim insertCmd As New SqlCommand
Dim myTransaction As SqlTransaction
Dim selectAll As New SqlDataAdapter
insertCmd.Connection = New
SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
insertCmd.Connection.Open()
myTransaction = insertCmd.Connection.BeginTransaction("L1")
insertCmd.Transaction = myTransaction

Dim t As Integer
sqlstring = "Select * from TAanvraagTekening where aanvraagid = " &
ds.Tables(0).Rows(i).Item("AanvraagId") & ""
selectAll = New SqlDataAdapter(sqlstring, insertCmd.Connection)
selectAll.Fill(dsAll) <-----HERE THE CODE FAILS!

For t = 0 To (dsAll.Tables(0).Rows.Count - 1)

insertCmd.CommandText = "INSERT INTO
TAanvraagTekening(AanvraagId,TekeningId,TekeningUitgave,TekeningDatum"
& _
") Values(" & tmpAanvraagId & ", " &
dsAll.Tables(0).Rows(t).Item("TekeningId") & ", '" &
dsAll.Tables(0).Rows(t).Item("TekeningUitgave") & "'" & _
",'" &
IsoDate(dsAll.Tables(0).Rows(t).Item("TekeningDatum")) & "')"

insertCmd.ExecuteNonQuery()
Next

........ more code......



myTransaction.Commit()

Thnx in advance!

Victor

 

[Frans Bouma [C# MVP]]

Hi there,

I have this code to select and insert data into the database. It all
works fine if i don't use a transaction but if i do i get the message:
Execute requires the command to have a transaction object when the
connection assigned to the command is in a pending local transaction.

What a i doing wrong here, please help!

Dim insertCmd As New SqlCommand
Dim myTransaction As SqlTransaction
Dim selectAll As New SqlDataAdapter
insertCmd.Connection = New
SqlConnection(ConfigurationSettings.AppSettings("ConnectionString"))
insertCmd.Connection.Open()
myTransaction = insertCmd.Connection.BeginTransaction("L1")
insertCmd.Transaction = myTransaction

Dim t As Integer
sqlstring = "Select * from TAanvraagTekening where aanvraagid = " &
ds.Tables(0).Rows(i).Item("AanvraagId") & ""
selectAll = New SqlDataAdapter(sqlstring, insertCmd.Connection)
selectAll.Fill(dsAll) <-----HERE THE CODE FAILS!

The SqlDataAdapter constructor you use creates internally a new SqlCommand
object. This is not wired with the SqlTransaction object you created. So you
have to use the same setup as you used with the insert command: create a
separate SqlCommand object, assign the transaction object and pass that to
the SqlDataAdapter constructor.

Frans.

 

[Chris Botha]

Frans is correct, though the following will work as well:
MyAdapter.SelectCommand.Transaction = myTransaction

 

[Victor]

Frans, Chris thank you! I should have known better

 

[Victor]

Frans, Chris thank you! I should have know better