Why can't it find the BeginTransaction line????

[Mamcx]

Easy, you start the transaction INSIDE the try, and the OUTSIDE catch (the
try portion is like a sub-function and have diferent scope, that is the
reason) don't can see that, simply change to:

StartTransaction
try
TheCode
Commit
catch
RollbackTransaction
finally
CloseConnections
end

[alien2_51]

I think you would get a better response if you posted to
microsoft.public.dotnet.framework.adonet...



[email]temple7502@rogers.com[/email]> wrote in message
news:OkhWa.11484$4UE.3021@news01.bloor.is.net.cabl e.rogers.com...

> Hi everyone,
>
> I am having a devil of a time with SqlTransactions, but I suspect the root
> of the problem is actually pretty simple. I set up a transaction which
> writes several employeeIDs to a datasource, in a loop. Then I deliberately
> cause an error to see if the RollBack works. Only problem is when this
> happens I get an error that says there is a RollBack without any
> BeginTransaction. As you can see in the code, this is clearly not the

case:

>
>
>
> Private Function insertEmployeeID()
>
>
>
> ' This function just gets the EmployeeID from all the enteries a listbox

and

> adds them to
>
> ' a Datasource.
>
>
>
> ' Loop through each driver, and add it to the table
>
> Dim intEmployeeMaxIndex As Integer
>
> Dim intEmployeeIndex As Integer
>
> Dim intEmployeeID As Integer
>
> Dim strInsertErrors As String
>
> Dim objTransaction As SqlTransaction
>
> Dim objCommand As New SqlCommand()
>
> Dim objConnection As SqlConnection
>
>
>
>
>
> intEmployee = employeeDriverList.Items.Count - 1 ' This is just a
> listbox with the employees
>
> objConnection = New SqlConnection(Application("strConnection"))
>
>
>
> Try
>
>
>
> objConnection.Open()
>
> objTransaction = objConnection.BeginTransaction() ' Here is

the

> BeginTransaction, bright as brass
>
>
>
> ' *** Create the Command and set its properties
>
> objCommand.Connection = objConnection
>
>
>
> objCommand.CommandText = "sp_EmployeeInsert"
>
> objCommand.CommandType = CommandType.StoredProcedure
>
> objCommand.Transaction = objTransaction
>
>
>
> Dim objParam As SqlParameter
>
>
>
> For intEmployeeIndex = 0 To intEmployeeMaxIndex
>
> objCommand.Parameters.Clear() ' Clear out the previous
> Parameters for new addition
>
>
>
> intEmployeeID =
> CInt(employeeDriverList.Items(intEmployeeIndex).Va lue)
>
>
>
>
>
> ' *** Set the EmployeeID column
>
> objParam = objCommand.Parameters.Add("@EmployeeID",
> SqlDbType.Int)
>
> objParam.Direction = ParameterDirection.Input
>
> objParam.Value = intEmployeeID
>
>
>
> If intEmployeeIndex = 1 Then
>
> ' *** Set a parameter that doesn't exist to cause an
> error and
>
> ' *** test rollback
>
> objParam = objCommand.Parameters.Add("@blah",
> SqlDbType.Int)
>
> objParam.Direction = ParameterDirection.Input
>
> objParam.Value = intEmployeeID
>
> End If
>
>
>
>
>
> objCommand.ExecuteNonQuery()
>
>
>
> If strInsertErrors <> "" Then
>
> Exit For
>
> End If
>
> Next intAuditGroupDriverIndex
>
>
>
>
>
> Catch e As Exception
>
> objTransaction.Rollback() ' Here is where the app complains
> there is a rollback
>
> ' without a
> beginTransaction
>
> strInsertErrors = "<div style='color:red'>* Error while
> inserting Employee data" + e.Message + "<br/>"
>
> Return strInsertErrors
>
> End Try
>
>
>
> objTransaction.Commit()
>
> objConnection.Close()
>
>
>
> Return strInsertErrors
>
> End Function
>
>
>
>
>
> Please, tell me what I am missing here!!!
>
> Thanks
>
> Dano
>
>