Transaction & disconnected mode
Hello everybody
I have problem with disconnected mode and transaction
it's give to me ERROR message
Quote:
ExecuteReader requires the command to have a transaction when the connection assigned to the command is in a pending local transaction. The Transaction property of the command has not been initialized
in this line
PHP Code:
da.InsertCommand = cb.GetInsertCommand
:ehh:
my code is:
PHP Code:
Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("DBCon").ConnectionString)
Dim cmd As New SqlCommand("insert into Customer (customer_id, customer_name, note) values (@cid, @cname, @note)", con)
Dim da As New SqlDataAdapter("select * from CustomerPhone", con)
Dim dt As New DataTable
Dim trans As SqlTransaction
Dim cb As New SqlCommandBuilder(da)
dt = CType(ViewState("dt"), DataTable)
cmd.Parameters.AddWithValue("@cid", txtID.Text)
cmd.Parameters.AddWithValue("@cname", txtName.Text)
cmd.Parameters.AddWithValue("@note", txtNote.Text)
Try
con.Open()
trans = con.BeginTransaction(IsolationLevel.ReadCommitted)
cmd.Transaction = trans
da.InsertCommand = cb.GetInsertCommand 'ERROR message
da.InsertCommand.Transaction = trans
cmd.ExecuteNonQuery() 'insert into customer table
da.Update(dt)
trans.Commit()
Catch ex As Exception
trans.Rollback()
Finally
con.Close()
End Try
:(
Re: Transaction & disconnected mode
Hey,
I can honestly say that I have never used the SqlTransaction class, so I can't offer any direct help on the error that you are having. However, a quick look at the MSDN documentation for this class:
http://msdn.microsoft.com/en-us/libr...ansaction.aspx
Suggests that you are doing some things out of order, which may well be causing your error.
Have a look at that example, fitting in your queries, and see if that works.
Gary
Re: Transaction & disconnected mode
Quote:
Originally Posted by
Code Geek
Hello everybody
I have problem with disconnected mode and transaction
it's give to me ERROR message
in this line
PHP Code:
da.InsertCommand = cb.GetInsertCommand
:ehh:
my code is:
PHP Code:
Dim con As New SqlConnection(ConfigurationManager.ConnectionStrings("DBCon").ConnectionString)
Dim cmd As New SqlCommand("insert into Customer (customer_id, customer_name, note) values (@cid, @cname, @note)", con)
Dim da As New SqlDataAdapter("select * from CustomerPhone", con)
Dim dt As New DataTable
Dim trans As SqlTransaction
Dim cb As New SqlCommandBuilder(da)
dt = CType(ViewState("dt"), DataTable)
cmd.Parameters.AddWithValue("@cid", txtID.Text)
cmd.Parameters.AddWithValue("@cname", txtName.Text)
cmd.Parameters.AddWithValue("@note", txtNote.Text)
Try
con.Open()
trans = con.BeginTransaction(IsolationLevel.ReadCommitted)
cmd.Transaction = trans
da.InsertCommand = cb.GetInsertCommand 'ERROR message
da.InsertCommand.Transaction = trans
cmd.ExecuteNonQuery() 'insert into customer table
da.Update(dt)
trans.Commit()
Catch ex As Exception
trans.Rollback()
Finally
con.Close()
End Try
:(
You have an awful lot of things going on there.
I see a command object which inserts something, then I see a command builder which also inserts something which you're calling with a data adapter. Why the two different methods - can't the phone numbers be updated via the same Command Builder, or can't the customers be updated via the command (so stick to one method rather than two different methods)?
Re: Transaction & disconnected mode
Quote:
Originally Posted by
gep13
Gary
thanks for your reply
and your link :)
Re: Transaction & disconnected mode
Quote:
Originally Posted by
mendhak
You have an awful lot of things going on there.
I see a command object which inserts something, then I see a command builder which also inserts something which you're calling with a data adapter. Why the two different methods - can't the phone numbers be updated via the same Command Builder, or can't the customers be updated via the command (so stick to one method rather than two different methods)?
I have 2 tables (customer, customerPhone)
the first object (cmd) is a sqlCommand type
it's insert one row to "customer" table
the other one is da
da is a sqlDataAdapter
it's to update "customerPhone" table from dataTable (dt)
dt has unknown numbers of new rows (may be 0 or 10000000 rows) :D
command builder is with da (dataAdapter) only
I want use transaction with (cmd) and (da)
that is my idea
:)
Re: Transaction & disconnected mode
If they're new rows, then loop through each row of the datatable and run a SqlCommand against each row that you want inserted. The da and the command builder are doing nothing for you here.
Re: Transaction & disconnected mode
Quote:
Originally Posted by
mendhak
If they're new rows, then loop through each row of the datatable and run a SqlCommand against each row that you want inserted. The da and the command builder are doing nothing for you here.
:eek::eek::eek::eek::eek:
are you sure ??????
in the previous code you can insert any new row exist in dataTable (dt)
:)
well, I want to know one thing
How to use Transaction with disconnected mode (dataAdapter) ??? :)
Re: Transaction & disconnected mode
By that I meant that it's causing more problems than it's worth - stick to one way of doing things. The dataadapter is likely using a reader internally and that isn't sitting well with your transaction.
I'm saying, create a SqlCommand for each row in the datatable and make an INSERT call to your database.