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 


---

:(

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

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

Quote:

---

Originally Posted by gep13

Gary

---

thanks for your reply

and your link :)

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

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.

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

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.