If you need manual access to a transaction, you will need to create the transaction and create the SqlCommand objects manually and execute them like this:

Try
    '-- Start the transaction
    DataBasics.DataSources("").TransactionBegin("MyTrans", Data.IsolationLevel.ReadCommitted)

    '-- Create the command object
    Dim command As New SqlCommand("STI30PostTempTagsToInventory")
    command.CommandType = Data.CommandType.StoredProcedure
    command.Transaction = CType(DataBasics.DataSources("").Transactions("MyTrans"), SqlTransaction)

    '-- Execute the command
    DataBasics.DataSources("").ExecuteScalar(command)

    '-- Commit the transaction
    DataBasics.DataSources("").TransactionCommit("MyTrans")

Catch ex As Exception

    DataBasics.DataSources("").TransactionRollback("MyTrans")

End Try

I'm trying to use a transaction with two stored procedures and its not working correctly. Here is the error message I get and the code below I am trying to execute. I've bolded the line of code that it stops on.



The transaction is either not associated with the current connection of has been completed.

'-- Start the transaction

DataBasics.DataSources("").TransactionBegin("MyTrans", Data.IsolationLevel.ReadCommitted)



'-- Create the command object

Dim cmd As New SqlCommand("STI30PostTempDetailsToPOOrderDetails")

cmd.CommandType = Data.CommandType.StoredProcedure

cmd.Transaction = CType(DataBasics.DataSources("").Transactions("MyTrans"), SqlTransaction)

cmd.Parameters.AddRange(lodefaultParams.ToArray)



'-- Execute the command

DataBasics.DataSources("").ExecuteScalar(cmd)



'-- Create the command object

cmd = New SqlCommand("STI30DeleteRecordsFromSTItmpPODetails")

cmd.CommandType = Data.CommandType.StoredProcedure

cmd.Transaction = CType(DataBasics.DataSources("").Transactions("MyTrans"), SqlTransaction)

cmd.Parameters.AddRange(lodefaultParams.ToArray)



'-- Execute the command

DataBasics.DataSources("").ExecuteScalar(cmd)



'-- Commit the transaction

DataBasics.DataSources("").TransactionCommit("MyTrans")

I will have to set this up and look at it closer.  It will more than likely not be until Monday until I can look at this in more detail with a sample.  Thanks.

Ok, thanks for the heads up.

Whenever you use the .DataSource("") to execute a command, it creates a new SqlConnection object to execute your command on.  However, when you attach a transaction to a command, you have to use the same connection as the transaction, so, you won't be able to use the ExecuteScalar() or ExecuteStoredProcedure() methods of the DbDataSourceItem when you are using a transaction, you will have to execute the SqlCommand objects directly.

'-- Start the transaction
DataBasics.DataSources("").TransactionBegin("MyTrans", Data.IsolationLevel.ReadCommitted)

'-- Create the command object
Dim cmd As New SqlCommand("STI30PostTempDetailsToPOOrderDetails")
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.Transaction = CType(DataBasics.DataSources("").Transactions("MyTrans"), SqlTransaction)
cmd.Connection = CType(DataBasics.DataSources("").Transactions("MyTrans").Connection, SqlConnection)
cmd.Parameters.AddRange(lodefaultParams.ToArray)

'-- Execute the command
Dim returnVal As Object = cmd.ExecuteScalar()

'-- Create the command object
cmd = New SqlCommand("STI30DeleteRecordsFromSTItmpPODetails")
cmd.CommandType = Data.CommandType.StoredProcedure
cmd.Transaction = CType(DataBasics.DataSources("").Transactions("MyTrans"), SqlTransaction)
cmd.Connection = CType(DataBasics.DataSources("").Transactions("MyTrans").Connection, SqlConnection)
cmd.Parameters.AddRange(lodefaultParams.ToArray)

'-- Execute the command
returnVal = cmd.ExecuteScalar()

'-- Commit the transaction
DataBasics.DataSources("").TransactionCommit("MyTrans")

Thanks Ben!

I'm glad that worked for you

Could you also do it like this?



DataBasics.DataSources("").ExecuteScalar(cmd)

Yes, you can do this.  Really the only difference is that is sets the connection for you.  But yes, you can this this also.

When using the DataBasics.DataSources("")... method of executing stored procedures... how does it handle errors that might occur? Does it throw and exception?

Also... Do you think I need any error handling in my stored procedures or user-defined functions to pass back info to my app or will the info get passed back without them?