none
Additional information: Invalid operation. The connection is closed. RRS feed

  • Question

  • I have two tables in a database. I am using these tables to explore the application of transaction, insert, update, delete, rollback and commit. I have four textboxes two to each table and a command button. I tried to insert to these tables and got the following “Additional information: Invalid operation. The connection is closed.” exception at underlined statement. I  will appreciate all help. My code is show below

     Private Sub btnTransaction_Click(sender As Object, e As EventArgs) Handles btnTransaction.Click
            Using connection As New OleDbConnection(connstr)
                Dim Cmd As New oledbCommand
                Dim transaction As oledbtransaction
                'connection to New oledb Connection
                Cmd.connection = connection
                'open connection and execute transaction
                Try
                    connection.open()
    
                Catch ex As Exception
                    'Start Local connection
                    transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted)
                    'assign transaction Object to local 
                    Cmd.connection = connection
                    Cmd.Transaction = transaction
                    'execute the commands
                    Cmd.commandtext = "INSERT INTO RelInfo(Surname, FirstName) Value (@SName, @FName)"
                    Cmd.Parameters.AddWithValue("@SName", txtSurname.Text)
                    Cmd.Parameters.AddWithValue("@FName", txtFirstName.Text)
                    Cmd.ExecuteNonQuery()
    
                    Cmd.CommandText = "INSERT INTO StudentInfo(SName, FName) Value (@SName, @FName)"
                    Cmd.Parameters.AddWithValue("@SName", txtSName.Text)
                    Cmd.Parameters.AddWithValue("@FName", txtFName.Text)
                    Cmd.ExecuteNonQuery()
    
                    'commit transaction
                    transaction.Commit()
                    MsgBox("Both Records Are Written to the database")
                    Console.WriteLine(ex.Message)
                    'Try to rollback the transaction
                    Try
                        transaction.Rollback()
                    Catch
                        ' do nothing transaction is not active anymore
    
                    End Try
                End Try
            End Using
            Dim response As DialogResult = MessageBox.Show("Already been executed." &
                            Environment.NewLine & _
                            "Proceed with transaction?", "Performing transanction", _
                            MessageBoxButtons.YesNo)
                End Sub
    End Class


    • Edited by alobi Saturday, September 2, 2017 11:30 AM
    Saturday, September 2, 2017 11:28 AM

All replies

  • Why do you have all the code in the catch then have another try catch in that catch, it does not make sense. The way I read this, to enter the catch as shown there must be an exception thrown in the outer try.

    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Saturday, September 2, 2017 11:36 AM
    Moderator
  • Hi alobi,

    Normally, we use like this:

    Try
                'tryStatements  
    
            Catch ex As Exception
                'catchStatements
    
            Finally
                'finallyStatements  
            End Try

    If you expect that a particular exception might occur during a particular section of code, put the code in a Try block and use a Catch block to retain control and handle the exception if it occurs.

     If an exception occurs when processing the Try block, each Catch statement is examined in textual order to determine whether it handles the exception, with exception representing the exception that has been thrown.

    So. please put the code inside the catch into try.

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    • Proposed as answer by Stanly Fan Wednesday, September 13, 2017 9:05 AM
    Monday, September 4, 2017 6:22 AM
    Moderator
  • Thanks Cherry Bu, You are appreciated. I am going to work on my skills.

    • Edited by alobi Tuesday, September 5, 2017 3:11 PM
    Tuesday, September 5, 2017 3:10 PM
  • Hi alobi,

    If your issue have been solved , please remember to close your thread by marking the helpful post as answer, it is beneficial to other community members who face the same issue.

    Thanks for your participation in the forum activities.

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, September 6, 2017 1:12 AM
    Moderator