Execute requires the command to have a transaction object when the connection assigned to the command is in a pending local transaction

TheMSsForum.com: The Microsoft Software Forum

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
Top

Re: Execute requires the command to have a transaction object when the connection assigned to the command is in a pending local transaction by Frans

Frans
Mon Apr 26 12:06:33 CDT 2004

Victor wrote:

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


--
Get LLBLGen Pro, the new O/R mapper for .NET: http://www.llblgen.com
My .NET Blog: http://weblogs.asp.net/fbouma
Microsoft C# MVP
Top

Re: Execute requires the command to have a transaction object when the connection assigned to the command is in a pending local transaction by Chris

Chris
Mon Apr 26 20:39:06 CDT 2004

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

"Victor" <victor@vanhillo.net> wrote in message
news:d4b0e293.0404260824.24b8b0ee@posting.google.com...
> 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


Top

Re: Execute requires the command to have a transaction object when the connection assigned to the command is in a pending local transaction by victor

victor
Tue Apr 27 03:01:08 CDT 2004

Frans, Chris thank you! I should have known better :-)

"Chris Botha" <chris_s_botha@AT_h.o.t.m.a.i.l.com> wrote in message news:<eGn8hg$KEHA.3664@TK2MSFTNGP10.phx.gbl>...
> Frans is correct, though the following will work as well:
> MyAdapter.SelectCommand.Transaction = myTransaction
>
> "Victor" <victor@vanhillo.net> wrote in message
> news:d4b0e293.0404260824.24b8b0ee@posting.google.com...
> > 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
Top

Re: Execute requires the command to have a transaction object when the connection assigned to the command is in a pending local transaction by victor

victor
Tue Apr 27 03:01:27 CDT 2004

Frans, Chris thank you! I should have know better :-)

"Chris Botha" <chris_s_botha@AT_h.o.t.m.a.i.l.com> wrote in message news:<eGn8hg$KEHA.3664@TK2MSFTNGP10.phx.gbl>...
> Frans is correct, though the following will work as well:
> MyAdapter.SelectCommand.Transaction = myTransaction
>
> "Victor" <victor@vanhillo.net> wrote in message
> news:d4b0e293.0404260824.24b8b0ee@posting.google.com...
> > 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
Top