transaction rollback

[cwfontan]

can I loop the 2nd command and still rollback if everything is not complete?

        Try
            sqlconn.Open()
            'BeginTransaction() Requires Open Connection
            trans = sqlconn.BeginTransaction()

            'Assign Transaction to Command
            cmd.Transaction = trans

            '1st command to execute. This will be rollback if 2nd command fails
            cmd.CommandText = "UPDATE dbo.Security_GroupUsers SET Inactive=0 WHERE depotID=" & depotID & " AND GroupID=" & groupID
            cmd.ExecuteNonQuery()
            For Each user In lb_groupUsers_SecurityMgr.Items
                '2nd command to execute
                cmd.CommandText = "INSERT INTO dbo.Security_GroupUsers (groupID,depotID,userID,audit,Inactive) VALUES (" & groupID & "," & depotID & "," & userID & ",'" & Audit & "',0)"
                cmd.ExecuteNonQuery()
            Next
            trans.Commit()
        Catch
            trans.Rollback()
            Console.WriteLine("Error while processing command. Previous execution was rollback")
        Finally
            sqlconn.Close()
        End Try

 

[demausdauth]

All the operations you perform with the command object that is in transaction should rollback or commit based on which one you call.

 

[cjard]

please read the PQ link in my signature