ADO Transactions

[carlblanchard]

Hey all i got a small problem (IM DUMB) no thats not the problem,

I am trying to do a ado transaction with more than 2 stored procs

this is my current code (snippet)
---------------------------------------------------------------------------
Dim myCommand As New SqlCommand("insTrustedUsers", myConnection)

myConnection.Open()

' Start a local transaction.
Dim myTrans As SqlTransaction = myConnection.BeginTransaction()

' Enlist the command in the current transaction.
'Dim myCommand As SqlCommand = myConnection.CreateCommand()
myCommand.Transaction = myTrans

Try
myCommand.CommandType = CommandType.StoredProcedure
myCommand.Parameters.Add("@firstname", "Carl")
myCommand.Parameters.Add("@surname", "blanchard")
myCommand.ExecuteNonQuery()

myTrans.Commit()
Console.WriteLine("Both records are written to database.")
Catch er As Exception
-----------------------------------------------------------------------------
this was adapted from msdn lib article
ms-help://MS.VSCC.2003/MS.MSDNQTR.2003FEB.1033/cpguide/html/cpconperformingtransactionusingadonet.htm

as u can see in the article it dosnt actully use a stored procedure

SO QUESTION IS how would u code it to call two stored procedures ? in the msdn lib example it shows to inserts statements string after each other i guess you do the same but how because the stored proc has been defined outside of the try and also cant define within the try because of the code structure

PLEASE HELP

[SaraL]

Hello!

What do you mean with "the stored proc has been defined outside of the try"? It is usually no special trick to use stored procedures in a transaction, you just execute them one after another.

Try
  myCommand.CommandType = CommandType.StoredProcedure
 
  myCommand.CommandText = "StoredProcedure1"
  myCommand.Parameters.Add("@firstname", "Carl") 
  myCommand.Parameters.Add("@surname", "Blanchard")
  myCommand.ExecuteNonQuery()
 
  myCommand.CommandText = "StoredProcedure2"
  myCommand.Parameters.Clear()
  myCommand.Parameters.Add("@parameter2", parameter2)
  myCommand.ExecuteNonQuery()
 
  myTrans.Commit()
 
Catch e as exception
  myTrans.Rollback()

/Sara

[carlblanchard]

its ok now sorted it

.connection = myconnection

cheers anyway