CODEWITHSUNDEEP
sqlsql-servervb.nettransactions
user1216456
user1216456

Reputation:

Sql transaction does not rollback in vb.net

I deliberately trigger a SqlException in the following insert statement in the code below and replaced the correct column name ModifiedBy by ModifiedB

Upvotes: 0

Views: 3019

Answers (2)

Iztoksson
Iztoksson

Reputation: 990

You need to pass your transaction and connection to all your commands you wish to use inside a SQL transaction, this is my example (I cut some things out using notepad so it might give errors in studio)

Private Sub main()
    Using sql_conn
    sql_conn.Open()
    Dim SQL_transaction_INPUT As SqlClient.SqlTransaction = sql_conn.BeginTransaction

    Try
        Dim isOK as Boolean = False
        isOK = update_BSE(myID, sql_conn, SQL_transaction_INPUT)

        If isOK Then
            SQL_transaction_INPUT.Commit()
            sql_conn.Close()
        Else
            SQL_transaction_INPUT.Rollback()
            sql_conn.Close()
        End If

    Catch ex As Exception

        SQL_transaction_INPUT.Rollback()
        If sql_conn.State = ConnectionState.Open Then sql_conn.Close()

    End Try
    End Using
End Sub

Private Function update_BSE(ByVal _IDmod As Integer, _
       ByVal conn_with_trans As SqlConnection, _
       ByVal conn_transaction As SqlTransaction) As Boolean

    Dim ins As String = "UPDATE something WHERE IDrec = @IDmod"
    Dim cmdINS As New SqlCommand(ins, conn_with_trans, conn_transaction)

    Try

        With cmdINS.Parameters
            .Add("IDmod", SqlDbType.Int).Value = _IDmod
        End With
        cmdINS.ExecuteNonQuery()

        Return True

    Catch ex As Exception
        Return False
    End Try
End Function

Upvotes: 0

mr.Reband
mr.Reband

Reputation: 2430

You need to wrap the entire SQL Code in a transaction. Take this example: 

create table errTest
(
intVal int
)

insert into errTest select 1
insert into errTest select 1/0
select * from errTest  --one record

The second insert fails, but since no transaction was explicitly started, each insert is inherently its own transaction. The first succeeds, the second fails, and the table ends up retaining the successful insert.

If all inserts are wrapped in a transaction, and if xact_abort is on, then any error thrown by any insert will cause the entire transaction to rollback: 

create table errTest
(
intVal int
)

set xact_abort on
begin transaction
    insert into errTest select 1
    insert into errTest select 1/0
commit transaction
select * from errTest  --zero records

Upvotes: 2

Related Questions