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

  • Thread starter Thread starter Victor
  • Start date Start date
V

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
 
Victor said:
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!
Click to expand...

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.
 
Frans is correct, though the following will work as well:
MyAdapter.SelectCommand.Transaction = myTransaction
 
Back
Top