Oh those Transaction Headaches!

Discussion in 'ASP .Net' started by Dano, Nov 17, 2003.

  1. Dano

    Dano Guest

    Hi all!

    Perhaps a wise soul can help me here. I have an insert routine for an
    ASP.Net application and it works fine, but I decided to test the transaction
    rollback capabilities by stopping the SQL server when it was just about to
    insert the record (I use a breakpoint and then stop the server). What should
    have happened is that the transaction is rolled back and my catch block
    response.writes the error which would be something about the network
    connection.

    Unfortunately, within the catch block when it tries to rollback the
    transaction I get another error which states

    This SqlTransaction has completed; it is no longer usable


    Here is the general look of my code


    ' *** Use SQL commands to directly insert info into DataSource

    Dim strConnection As String

    Dim objConnection As SqlConnection

    Dim objCommand As New SqlCommand()

    Dim objTransaction As SqlTransaction



    objConnection = New SqlConnection(Application("strConnection"))

    Try



    objConnection.Open()

    objTransaction = objConnection.BeginTransaction()



    ' *** Create the Command and set its properties

    objCommand.Connection = objConnection

    objCommand.Transaction = objTransaction

    objCommand.CommandText = "sp_LogInsert2"



    objCommand.CommandType = CommandType.StoredProcedure



    ' Set the various Parameters for the stored Procedure

    Blah, Blah, Blah



    objCommand.ExecuteNonQuery() ' I stop the SQL server just before it
    executes this line

    objTransaction.Commit()

    Catch objError As Exception



    'Error was encountered so roll back all the inserts

    objTransaction.Rollback()



    ' Display error details

    Response.write("**** Error while inserting data ****" +
    objError.Message + "<br/>" + objError.Source)



    Finally

    objConnection.Close()

    End Try



    For some reason many people tell me it has to do with the Try block. Wrox
    Professional ASP.Net has many examples of having the BeginTransaction within
    the Try block. But just for the sake of argument I moved the Open Connection
    and the Begin Transaction outside of the try block. No difference was seen.

    Could someone please tell me what I am doing wrong?

    Dano
    Dano, Nov 17, 2003
    #1
    1. Advertising

  2. I think this is not an ASP.NET question.

    The error is telling you, that the transaction is completed. You cannot
    rollback (or commit) a transaction that is already complete. It's done.


    "Dano" <> wrote in message
    news:p19ub.459$...
    > Hi all!
    >
    > Perhaps a wise soul can help me here. I have an insert routine for an
    > ASP.Net application and it works fine, but I decided to test the

    transaction
    > rollback capabilities by stopping the SQL server when it was just about to
    > insert the record (I use a breakpoint and then stop the server). What

    should
    > have happened is that the transaction is rolled back and my catch block
    > response.writes the error which would be something about the network
    > connection.
    >
    > Unfortunately, within the catch block when it tries to rollback the
    > transaction I get another error which states
    >
    > This SqlTransaction has completed; it is no longer usable
    >
    >
    > Here is the general look of my code
    >
    >
    > ' *** Use SQL commands to directly insert info into DataSource
    >
    > Dim strConnection As String
    >
    > Dim objConnection As SqlConnection
    >
    > Dim objCommand As New SqlCommand()
    >
    > Dim objTransaction As SqlTransaction
    >
    >
    >
    > objConnection = New SqlConnection(Application("strConnection"))
    >
    > Try
    >
    >
    >
    > objConnection.Open()
    >
    > objTransaction = objConnection.BeginTransaction()
    >
    >
    >
    > ' *** Create the Command and set its properties
    >
    > objCommand.Connection = objConnection
    >
    > objCommand.Transaction = objTransaction
    >
    > objCommand.CommandText = "sp_LogInsert2"
    >
    >
    >
    > objCommand.CommandType = CommandType.StoredProcedure
    >
    >
    >
    > ' Set the various Parameters for the stored Procedure
    >
    > Blah, Blah, Blah
    >
    >
    >
    > objCommand.ExecuteNonQuery() ' I stop the SQL server just before it
    > executes this line
    >
    > objTransaction.Commit()
    >
    > Catch objError As Exception
    >
    >
    >
    > 'Error was encountered so roll back all the inserts
    >
    > objTransaction.Rollback()
    >
    >
    >
    > ' Display error details
    >
    > Response.write("**** Error while inserting data ****" +
    > objError.Message + "<br/>" + objError.Source)
    >
    >
    >
    > Finally
    >
    > objConnection.Close()
    >
    > End Try
    >
    >
    >
    > For some reason many people tell me it has to do with the Try block. Wrox
    > Professional ASP.Net has many examples of having the BeginTransaction

    within
    > the Try block. But just for the sake of argument I moved the Open

    Connection
    > and the Begin Transaction outside of the try block. No difference was

    seen.
    >
    > Could someone please tell me what I am doing wrong?
    >
    > Dano
    >
    >
    Dino Chiesa [Microsoft], Nov 17, 2003
    #2
    1. Advertising

  3. Dano

    bruce barker Guest

    you cannot roll the transaction back because you lost the connection and
    there is no way to notify sqlserver to rollback (it will on its own). there
    are other cases when rollbacks will fail, such as when the transaction did a
    rollback on its own.

    -- bruce (sqlwork.com)


    "Dano" <> wrote in message
    news:p19ub.459$...
    > Hi all!
    >
    > Perhaps a wise soul can help me here. I have an insert routine for an
    > ASP.Net application and it works fine, but I decided to test the

    transaction
    > rollback capabilities by stopping the SQL server when it was just about to
    > insert the record (I use a breakpoint and then stop the server). What

    should
    > have happened is that the transaction is rolled back and my catch block
    > response.writes the error which would be something about the network
    > connection.
    >
    > Unfortunately, within the catch block when it tries to rollback the
    > transaction I get another error which states
    >
    > This SqlTransaction has completed; it is no longer usable
    >
    >
    > Here is the general look of my code
    >
    >
    > ' *** Use SQL commands to directly insert info into DataSource
    >
    > Dim strConnection As String
    >
    > Dim objConnection As SqlConnection
    >
    > Dim objCommand As New SqlCommand()
    >
    > Dim objTransaction As SqlTransaction
    >
    >
    >
    > objConnection = New SqlConnection(Application("strConnection"))
    >
    > Try
    >
    >
    >
    > objConnection.Open()
    >
    > objTransaction = objConnection.BeginTransaction()
    >
    >
    >
    > ' *** Create the Command and set its properties
    >
    > objCommand.Connection = objConnection
    >
    > objCommand.Transaction = objTransaction
    >
    > objCommand.CommandText = "sp_LogInsert2"
    >
    >
    >
    > objCommand.CommandType = CommandType.StoredProcedure
    >
    >
    >
    > ' Set the various Parameters for the stored Procedure
    >
    > Blah, Blah, Blah
    >
    >
    >
    > objCommand.ExecuteNonQuery() ' I stop the SQL server just before it
    > executes this line
    >
    > objTransaction.Commit()
    >
    > Catch objError As Exception
    >
    >
    >
    > 'Error was encountered so roll back all the inserts
    >
    > objTransaction.Rollback()
    >
    >
    >
    > ' Display error details
    >
    > Response.write("**** Error while inserting data ****" +
    > objError.Message + "<br/>" + objError.Source)
    >
    >
    >
    > Finally
    >
    > objConnection.Close()
    >
    > End Try
    >
    >
    >
    > For some reason many people tell me it has to do with the Try block. Wrox
    > Professional ASP.Net has many examples of having the BeginTransaction

    within
    > the Try block. But just for the sake of argument I moved the Open

    Connection
    > and the Begin Transaction outside of the try block. No difference was

    seen.
    >
    > Could someone please tell me what I am doing wrong?
    >
    > Dano
    >
    >
    bruce barker, Nov 17, 2003
    #3
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Stephajn Craig

    Impersonation headaches

    Stephajn Craig, Dec 16, 2003, in forum: ASP .Net
    Replies:
    2
    Views:
    472
    bruce barker
    Dec 17, 2003
  2. BinnuChowdary
    Replies:
    1
    Views:
    536
    Swanand Mokashi
    May 1, 2006
  3. BinnuChowdary
    Replies:
    0
    Views:
    411
    BinnuChowdary
    May 2, 2006
  4. BinnuChowdary
    Replies:
    1
    Views:
    544
    =?UTF-8?B?R8O2cmFuIEFuZGVyc3Nvbg==?=
    May 2, 2006
  5. Vencz Istv?n
    Replies:
    2
    Views:
    282
Loading...

Share This Page