SqlTransaction

[Swami Muthuvelu]

Hi,
I using command builder to generate my insert, update and
delete statements..som ething like,

data_adapter = New SqlClient.SqlDa taAdapter(SQL,
ActiveConnectio n)

command_builder = New SqlClient.SqlCo mmandBuilder
(data_adapter)

After filling a data grid and making changes to the data,
I want to call the adapter.Update method in a database
transaction, so that I can rollback the transaction, if
the update fails..
This is my code, for that..
*************** *************** *********
Dim trans As SqlClient.SqlTr ansaction
trans = adp.SelectComma nd.Connection.B eginTransaction
adp.Update(ds)
***************
In the adp.Update(ds) line, I get the exception,
"Execute requires the command to have a transaction object
when the connection assigned to the command is in a
pending local transaction. The Transaction property of
the command has not been initialized."

What am I doing wrong..?
Please help with a code snippet.

Regards,
Swami

[Yan-Hong Huang[MSFT]]

RE: SqlTransaction

Hello Swami,

When you are using a CommandBuilder and you want to use transactions, you must associate the transaction with the
SelectCommand of the DataAdapter before calling Update. Then, the CommandBuilder will automatically enlist all
generated commands in the transaction when you call Update.

For an example:
data_adapterSel ectcommand.Tran saction = trans;
data_adapter.In sertCommand.Tra nsaction = trans;
data_adapter.Up dateCommand.Tra nsaction = trans;
data_adapter.De leteCommand.Tra nsaction = trans;

Hope it helps.

Best regards,
Yanhong Huang
Microsoft Online Partner Support

Get Secure! - www.microsoft.com/security
This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
!Content-Class: urn:content-classes:message
!From: "Swami Muthuvelu" <swami@mclsyste ms.com>
!Sender: "Swami Muthuvelu" <swami@mclsyste ms.com>
!Subject: SqlTransaction
!Date: Mon, 28 Jul 2003 16:51:22 -0700
!Lines: 38
!Message-ID: <04dd01c35563$2 5d846c0$a401280 a@phx.gbl>
!MIME-Version: 1.0
!Content-Type: text/plain;
! charset="iso-8859-1"
!Content-Transfer-Encoding: 7bit
!X-Newsreader: Microsoft CDO for Windows 2000
!X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
!Thread-Index: AcNVYyXY+PcCw/X4Qo2zrO1QD6xvr A==
!Newsgroups: microsoft.publi c.dotnet.genera l
!Path: cpmsftngxa06.ph x.gbl
!Xref: cpmsftngxa06.ph x.gbl microsoft.publi c.dotnet.genera l:102645
!NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
!X-Tomcat-NG: microsoft.publi c.dotnet.genera l
!
!Hi,
!I using command builder to generate my insert, update and
!delete statements..som ething like,
!
!data_adapter = New SqlClient.SqlDa taAdapter(SQL,
!ActiveConnecti on)
!
!command_builde r = New SqlClient.SqlCo mmandBuilder
!(data_adapter)
!
!After filling a data grid and making changes to the data,
!I want to call the adapter.Update method in a database
!transaction, so that I can rollback the transaction, if
!the update fails..
!This is my code, for that..
!************** *************** **********
!Dim trans As SqlClient.SqlTr ansaction
!trans = adp.SelectComma nd.Connection.B eginTransaction
!adp.Update(ds)
!************** *
!In the adp.Update(ds) line, I get the exception,
!"Execute requires the command to have a transaction object
!when the connection assigned to the command is in a
!pending local transaction. The Transaction property of
!the command has not been initialized."
!
!What am I doing wrong..?
!Please help with a code snippet.
!
!Regards,
!Swami
!
!
!
!
!
!
!
!

[Swami Muthuvelu]

RE: SqlTransaction

Yanhong,
Thanks for your reply.
Can you please also tell me how to define the transaction
object?. Should it be like this?

Dim trans As SqlClient.SqlTr ansaction
trans = adp.SelectComma nd.Connection.B eginTransaction

and then,

data_adapterSel ectcommand.Tran saction = trans
data_adapter.In sertCommand.Tra nsaction = trans
data_adapter.Up dateCommand.Tra nsaction = trans
data_adapter.De leteCommand.Tra nsaction = trans
?.

Please help me on this.
Regards,
Swami
[color=blue]
>-----Original Message-----
>Hello Swami,
>
>When you are using a CommandBuilder and you want to use[/color]
transactions, you must associate the transaction with the[color=blue]
>SelectComman d of the DataAdapter before calling Update.[/color]
Then, the CommandBuilder will automatically enlist all[color=blue]
>generated commands in the transaction when you call[/color]
Update.[color=blue]
>
>For an example:
>data_adapterSe lectcommand.Tra nsaction = trans;
>data_adapter.I nsertCommand.Tr ansaction = trans;
>data_adapter.U pdateCommand.Tr ansaction = trans;
>data_adapter.D eleteCommand.Tr ansaction = trans;
>
>Hope it helps.
>
>Best regards,
>Yanhong Huang
>Microsoft Online Partner Support
>
>Get Secure! - www.microsoft.com/security
>This posting is provided "AS IS" with no warranties, and[/color]
confers no rights.[color=blue]
>
>--------------------
>!Content-Class: urn:content-classes:message
>!From: "Swami Muthuvelu" <swami@mclsyste ms.com>
>!Sender: "Swami Muthuvelu" <swami@mclsyste ms.com>
>!Subject: SqlTransaction
>!Date: Mon, 28 Jul 2003 16:51:22 -0700
>!Lines: 38
>!Message-ID: <04dd01c35563$2 5d846c0$a401280 a@phx.gbl>
>!MIME-Version: 1.0
>!Content-Type: text/plain;
>! charset="iso-8859-1"
>!Content-Transfer-Encoding: 7bit
>!X-Newsreader: Microsoft CDO for Windows 2000
>!X-MIMEOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
>!Thread-Index: AcNVYyXY+PcCw/X4Qo2zrO1QD6xvr A==
>!Newsgroups: microsoft.publi c.dotnet.genera l
>!Path: cpmsftngxa06.ph x.gbl
>!Xref: cpmsftngxa06.ph x.gbl[/color]
microsoft.publi c.dotnet.genera l:102645[color=blue]
>!NNTP-Posting-Host: TK2MSFTNGXA12 10.40.1.164
>!X-Tomcat-NG: microsoft.publi c.dotnet.genera l
>!
>!Hi,
>!I using command builder to generate my insert, update[/color]
and[color=blue]
>!delete statements..som ething like,
>!
>!data_adapte r = New SqlClient.SqlDa taAdapter(SQL,
>!ActiveConnect ion)
>!
>!command_build er = New SqlClient.SqlCo mmandBuilder
>!(data_adapter )
>!
>!After filling a data grid and making changes to the[/color]
data,[color=blue]
>!I want to call the adapter.Update method in a database
>!transaction , so that I can rollback the transaction, if
>!the update fails..
>!This is my code, for that..
>!************* *************** ***********
>!Dim trans As SqlClient.SqlTr ansaction
>!trans = adp.SelectComma nd.Connection.B eginTransaction
>!adp.Update(ds )
>!************* **
>!In the adp.Update(ds) line, I get the exception,
>!"Execute requires the command to have a transaction[/color]
object[color=blue]
>!when the connection assigned to the command is in a
>!pending local transaction. The Transaction property of
>!the command has not been initialized."
>!
>!What am I doing wrong..?
>!Please help with a code snippet.
>!
>!Regards,
>!Swami
>!
>!
>!
>!
>!
>!
>!
>!
>
>
>.
>[/color]