Transaction & disconnected mode

[Code Geek]

Hello everybody

I have problem with disconnected mode and transaction


it's give to me ERROR message

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

da.InsertCommand = cb.GetInsertCommand 

my code is:

            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 

[gep13]

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

[mendhak]

Hello everybody

I have problem with disconnected mode and transaction


it's give to me ERROR message


in this line

da.InsertCommand = cb.GetInsertCommand 

my code is:

            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)?

[Code Geek]

Gary

thanks for your reply

and your link

[Code Geek]

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)

command builder is with da (dataAdapter) only


I want use transaction with (cmd) and (da)
that is my idea

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

[Code Geek]

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.

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) ???

[mendhak]

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.