Problem using ado transaction

[Esfandyar Yousefi]

Hi
I want use ado.net begin transaction for useing some sp with parameter the
code is like this :

Dim myConnection As New SqlConnection("persist security info=false
;database=pubs ; server=mainxpf ;user = sa ")
myConnection.Open()
Dim myCommand As SqlCommand = myConnection.CreateCommand()
Dim myTrans As SqlTransaction
myTrans =
myConnection.BeginTransaction(IsolationLevel.ReadCommitted, "Trans1")
'
Try
With myCommand
.CommandType = CommandType.StoredProcedure
SqlClient.SqlCommandBuilder.DeriveParameters(myCommand)
.Transaction = myTrans
.CommandText = "byroyalty"
.Parameters.Item("@percentage").Value = "50"
.Connection = myConnection
.ExecuteNonQuery()
End With
myTrans.Commit()
Console.WriteLine("Ok !.")
Catch e As Exception
MsgBox(e.Message)
myTrans.Rollback("Trans1")
End Try
myConnection.Close()

error message : "Execute requires the command to have a transaction object
when connection assigned to the commanad is in a pending locat transaction
..The property of the command has not been initialized ."

changing connetion assignment can't help me !

 

[Miha Markic]

Hi Esfandyar,

You should assign the current transaction to command object, too (like you
assign current connection).
Add the following line before ExecuteNonQuery():
..Transaction = myTrans
and it should work just fine.

 

[Esfandyar Yousefi]

Hi Miha
Thanks for your note , but I do that in my code see it again please .