dcsimg
Results 1 to 3 of 3

Thread: [RESOLVED] Can undo operation if error?

  1. #1

    Thread Starter
    Lively Member
    Join Date
    Nov 2018
    Posts
    76

    Resolved [RESOLVED] Can undo operation if error?

    Hi
    I need to run two insert queries on two SQL tables. Each query inserts a record to different table. One table contains the general info and the other contains detail of that record

    I need that if the second insert query fails, the first query be undone. Is that possible? And how?

    Thanks for help.

  2. #2
    Frenzied Member gibra's Avatar
    Join Date
    Oct 2009
    Location
    ITALY
    Posts
    1,600

    Re: Can undo operation if error?

    You should use a transaction.
    You need to search for transaction on your database help, because it's different for each database.

  3. #3

    Thread Starter
    Lively Member
    Join Date
    Nov 2018
    Posts
    76

    Re: Can undo operation if error?

    Thank you. I have tried it and it seems it works. Just to be sure that it rolls back, I have changed one of the column names to provoke an error and it rolled back the transaction.
    I had to put both queries between BEGIN TRANSACTION and COMMIT; also put a semicolon at the end of each query.

    Code:
            
    Dim QueryWithRollback As String =
                                 "BEGIN TRY 
                                    BEGIN TRANSACTION" _
                                      & MyGeneralQuery & MyDetailQuery &
                                   "COMMIT
                                  End Try
                                  BEGIN CATCH
                                  If @@TRANCOUNT > 0
                                         ROLLBACK TRAN
                                  End Catch"
    
        If InsertStuffToTables(QueryWithRollback, "New Item - general") Then
                MsgBox("New request has been submitted sucessfully For ID: " & ThisID, MsgBoxStyle.Information, "New request submitted!")
         End If
    
        Function InsertStuffToTables(ThisQuery As String, MyType As String)
            Try
                SQLcon.Open()
    
                MySqlCommand = New SqlCommand(ThisQuery, SQLcon)
                MySqlCommand.ExecuteNonQuery()
                SQLcon.Close()
                Return True
    
            Catch ex As SqlException
                SQLcon.Close()
                MsgBox(ex.Message, MsgBoxStyle.Critical, "SQL error while registering " & MyType & " request")
                Return False
            Catch ex As Exception
                SQLcon.Close()
                MsgBox(ex.Message, MsgBoxStyle.Critical, "General error while registering " & MyType & " request")
                Return False
            End Try
            If SQLcon.State = ConnectionState.Open Then
                SQLcon.Close()
            End If
        End Function
    Thank again for the help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width