Transactions

[fscsantos]

Hi
I want to update two datasets with in the same transaction.

conn.Open()
Dim cbcomando As New SqlCommandBuilder(adDoc)
Dim cbLinhas As New SqlCommandBuilder(adLinhasDoc2)

myTrans = conn.BeginTransaction()
adDoc.SelectCommand.Transaction = myTrans
adLinhasDoc2.SelectCommand.Transaction = myTrans

Try
    adDoc.Update(dsDoc, "Documentos")                
    adLinhasDoc2.Update(dsLinhasDoc2, "LDocumentos")
    myTrans.Commit()
Catch ex As Exception
    MsgBox(ex.Message)
    myTrans.Rollback()
End Try

But, unfortunately i get the following error

Error: "Execute requires the command to have a tranasaction object when the connection assigned to the command is in a pending local transaction. The transaction property of the command has not been initialized"

Please help me, give me any suggestions

 

[TechGnome]

You used the SelectCommand not the UpdateCommand. Try setting the Transaction of the UpdateCommand instead.

-tg

 

[fscsantos]

TechGnome,
also already I tried to use updatecommand but it gives the following error :

"Object reference not set to an instance of an object".


Still I am new in this therefore I still have some difficulties

 

[jmcilhinney]

You can't use transactions with a CommandBuilder like that because the commands are generated on-the-fly, so there is no command to assign the transaction to until you call Update, at which point it's too late. This is the very reason that I stopped using CommandBuilders in the first place. Any situation complex enough to require transactions really should be done using explicitly coded Delete, Insert and Update commands.

The CommandBuilder has methods to allow you to retrieve the actual commands that will be generated, so you may be able to call those and then specifically assign the commands returned to the appropriate properties of the DataAdapter, and then assign the transaction, but I've never tried that so I'm not sure if it would work. I'd still recommend coding your own SQL statements. You can create a DataAdapter in the design window and set all the properties in the Properties window.