none
Oledbtransaction insert does not show any error or exception and does not save to any of the tables RRS feed

  • Question

  • I am using transaction, commit or rollback to insert data into two different tables, when I run the project it does not insert into any of the tables, does not provide any error message or exception. 1 is displayed in Textbox1.Text. PS help me

    Using conn As New OleDbConnection(connstr)
                Dim cmd As New OleDbCommand
                Dim transaction As OleDbTransaction = Nothing
                'Call validationCheck()
                ' Set the Connection to the new OleDbConnection.
                cmd.Connection = conn
                ' Open the connection and execute the transaction.
    
                conn.Open()
                'Test connection
                If conn.State = ConnectionState.Open Then
                    MsgBox("Database is Open")
    
                End If
                Try
    
                    ' Start a local transaction with ReadCommitted isolation level.
                    transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted)
                    ' Assign transaction object for a pending local transaction.
                    cmd.Connection = conn
                    cmd.Transaction = transaction
                    ' Execute the commands.
                    cmd.CommandText = "INSERT INTO Student(Student_ID,Programtbl,Leveltbl,Arm)VALUES( ?, ?, ?,?)"
                    cmd.Parameters.AddWithValue("@P1", txtStudentID.Text)
                    cmd.Parameters.AddWithValue("@P2", cboProgram.Text)
                    cmd.Parameters.AddWithValue("@P3", cboLevel.Text)
                    cmd.Parameters.AddWithValue("@P4", cboArm.Text)
                    TextBox1.Text = cmd.ExecuteNonQuery().ToString
                    cmd.ExecuteNonQuery()
                    Dim rcount As Integer = cmd.ExecuteNonQuery
                    TextBox1.Text = rcount
                    cmd.Parameters.Clear()
    
    
    
                    cmd.CommandText = "INSERT INTO StudentNOK(Student_ID, SurName,FirstName, MiddleName)VALUES(?,?,?,?)"
                    cmd.Parameters.AddWithValue("@P1", txtStudentID.Text)
                    cmd.Parameters.AddWithValue("@P2", txtSurnameNK.Text)
                    cmd.Parameters.AddWithValue("@P3", txtFirstNameNK.Text)
                    cmd.Parameters.AddWithValue("@P4", txtMiddleNameNK.Text)
                    cmd.ExecuteNonQuery()
                    'rcount = cmd.ExecuteNonQuery
                    'TextBox1.Text = rcount
                    transaction.Rollback()
                    ' Commit the transaction.
                    transaction.Commit()
                    MsgBox("Both records are written to database.")
    
                Catch ex As Exception
    
    
    
                    ' Do nothing here; transaction is not active.
                End Try
    
                ' The connection is automatically closed when the
                ' code exits the Using block.
            End Using
            

    Friday, September 8, 2017 11:33 AM

Answers

  • Hi alobi,

    Based on your description, you want to use Transaction to insert data into Access database, but you have no message when you insert data unsuccessfully, you can refer to the following code to modify your code.

        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim str As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Database\TestTable.accdb; Persist Security Info=False;"
            ExecuteTransaction(str)
        End Sub
    
        Public Sub ExecuteTransaction(ByVal connectionString As String)
    
            Using connection As New OleDbConnection(connectionString)
                Dim command As New OleDbCommand()
                Dim transaction As OleDbTransaction
    
                ' Set the Connection to the new OleDbConnection.
                command.Connection = connection
    
                ' Open the connection and execute the transaction.
                Try
                    connection.Open()
    
                    ' Start a local transaction.
                    transaction = connection.BeginTransaction()
    
                    ' Assign transaction object for a pending local transaction.
                    command.Connection = connection
                    command.Transaction = transaction
    
                    ' Execute the commands.
                    command.CommandText =
                        "Insert into Test1 (ID, Column1,Column2) VALUES (6, 'FF','ff')"
                    command.ExecuteNonQuery()
                    command.CommandText =
                        "Insert into Test2 (ID, Column1,Column2) VALUES (5, 'EE','ee')"
                    command.ExecuteNonQuery()
    
                    ' Commit the transaction.
                    transaction.Commit()
                    Console.WriteLine("Both records are written to database.")
    
                Catch ex As Exception
                    Console.WriteLine(ex.Message)
                    ' Try to rollback the transaction
                    Try
                        transaction.Rollback()
                        TextBox1.Text = "Transaction is not completed"
    
                    Catch
                        ' Do nothing here; transaction is not active.
                    End Try
                End Try
                ' The connection is automatically closed when the
                ' code exits the Using block.
            End Using
        End Sub

    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.

    • Marked as answer by alobi Tuesday, September 19, 2017 4:58 PM
    Monday, September 11, 2017 3:12 AM
    Moderator

All replies

  • Here is a full working example for adding rows to two tables in a ms-access database which also returns the new primary keys.

    https://1drv.ms/u/s!AtGAgKKpqdWjiGU0bavadLKql6VI

    Container for passing data into the class below

    Public Class Customer
        Public Property Identfier As Integer
        Public Property Name As String
        Public Property ContactName As String
        Public Property ContactTitle As String
    
    End Class
    
    

    Data operations

    Imports System.Data.OleDb
    Public Class Operations
        Private Builder As New OleDbConnectionStringBuilder With
        {
            .Provider = "Microsoft.ACE.OLEDB.12.0",
            .DataSource = IO.Path.Combine(AppDomain.CurrentDomain.BaseDirectory, "Database1.accdb")
        }
        Private mExceptiom As Exception
        ''' <summary>
        ''' Container to remember an exception thrown
        ''' </summary>
        ''' <returns></returns>
        Public ReadOnly Property Exception As Exception
            Get
                Return mExceptiom
            End Get
        End Property
        ''' <summary>
        ''' Adds one record per table
        ''' </summary>
        ''' <param name="pCustomer"></param>
        ''' <param name="pCustomer1"></param>
        ''' <returns></returns>
        Public Function AddNewRowsForTwoTables(ByVal pCustomer As Customer, ByVal pCustomer1 As Customer) As Boolean
            Dim Success As Boolean = True
            Dim Affected As Integer = 0
    
            Dim trans As OleDbTransaction = Nothing
    
            Try
                Using cn As New OleDbConnection With {.ConnectionString = Builder.ConnectionString}
                    Using cmd As New OleDbCommand With {.Connection = cn}
                        cmd.CommandText =
                            <SQL>
                                INSERT INTO Customers (CompanyName,ContactName, ContactTitle) 
                                Values(@CompanyName,@ContactName, @ContactTitle)
                            </SQL>.Value
    
                        cmd.Parameters.AddWithValue("@CompanyName", pCustomer.Name)
                        cmd.Parameters.AddWithValue("@ContactName", pCustomer.ContactName)
                        cmd.Parameters.AddWithValue("@ContactTitle", pCustomer.ContactTitle)
    
                        cn.Open()
                        trans = cn.BeginTransaction()
                        cmd.Transaction = trans
    
                        Affected = cmd.ExecuteNonQuery()
                        If Affected = 1 Then
                            cmd.CommandText = "Select @@Identity"
                            pCustomer.Identfier = CInt(cmd.ExecuteScalar)
                            Success = True
                        Else
                            Throw New Exception("Failed to add record for customers")
                        End If
    
                        cmd.CommandText =
                            <SQL>
                                INSERT INTO Customers1 (CompanyName,ContactName, ContactTitle) 
                                Values(@CompanyName,@ContactName, @ContactTitle)
                            </SQL>.Value
    
                        cmd.Parameters(0).Value = pCustomer1.Name
                        cmd.Parameters(1).Value = pCustomer1.ContactName
                        cmd.Parameters(2).Value = pCustomer1.ContactTitle
    
                        Affected = cmd.ExecuteNonQuery()
                        If Affected = 1 Then
                            cmd.CommandText = "Select @@Identity"
                            pCustomer1.Identfier = CInt(cmd.ExecuteScalar)
                            Success = True
                        Else
                            Throw New Exception("Failed to add record for customers1")
                        End If
    
                        trans.Commit()
    
                    End Using
                End Using
            Catch ex As Exception
                Success = False
                mExceptiom = ex
                Try
                    trans.Rollback()
                Catch exTrans As Exception
                    mExceptiom = exTrans
                End Try
            End Try
    
            Return Success
    
        End Function
    End Class
    

    Form code

    Public Class Form1
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim ops As New Operations
            Dim cust1 As New Customer With
                {
                    .Name = "Karen's coffee shop",
                    .ContactName = "Karen Payne",
                    .ContactTitle = "Owner"
                }
            Dim cust2 As New Customer With
                {
                    .Name = "Bob's coffee shop",
                    .ContactName = "Bob Smith",
                    .ContactTitle = "Owner"
                }
            If ops.AddNewRowsForTwoTables(cust1, cust2) Then
                MessageBox.Show($"Cust1 id: {cust1.Identfier} Cust2 id: {cust2.Identfier}")
            Else
                If ops.Exception IsNot Nothing Then
                    MessageBox.Show($"Failed to add rows{Environment.NewLine}Error: {ops.Exception.Message}")
                Else
                    MessageBox.Show("Failed to add rows")
                End If
            End If
        End Sub
    End Class
    


    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

    Friday, September 8, 2017 1:18 PM
    Moderator
  • I don't know what type of database you are working with, but I would check the following first (#2 in your case):

    https://social.msdn.microsoft.com/Forums/en-US/ad837d14-31ea-445f-b830-1a8682b69e68/faq-my-database-isnt-being-updated-but-no-errors-occurred-in-my-application?forum=adodotnetdataproviders


    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, September 8, 2017 1:39 PM
  • I  am working with Access database
    Friday, September 8, 2017 2:48 PM
  • I  am working with Access database

    Then definitely check the Copy to Output Directory property for the database to make sure it has been properly set.

    Paul ~~~~ Microsoft MVP (Visual Basic)

    Friday, September 8, 2017 3:36 PM
  • Here is a sample with descriptions for what Paul ask you to try.

    https://code.msdn.microsoft.com/Working-with-Copy-to-11fbc251?redir=0


    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

    Friday, September 8, 2017 4:11 PM
    Moderator
  • Thanks I will go try  it Now

    Friday, September 8, 2017 4:44 PM
  • Hi alobi,

    Based on your description, you want to use Transaction to insert data into Access database, but you have no message when you insert data unsuccessfully, you can refer to the following code to modify your code.

        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Dim str As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Database\TestTable.accdb; Persist Security Info=False;"
            ExecuteTransaction(str)
        End Sub
    
        Public Sub ExecuteTransaction(ByVal connectionString As String)
    
            Using connection As New OleDbConnection(connectionString)
                Dim command As New OleDbCommand()
                Dim transaction As OleDbTransaction
    
                ' Set the Connection to the new OleDbConnection.
                command.Connection = connection
    
                ' Open the connection and execute the transaction.
                Try
                    connection.Open()
    
                    ' Start a local transaction.
                    transaction = connection.BeginTransaction()
    
                    ' Assign transaction object for a pending local transaction.
                    command.Connection = connection
                    command.Transaction = transaction
    
                    ' Execute the commands.
                    command.CommandText =
                        "Insert into Test1 (ID, Column1,Column2) VALUES (6, 'FF','ff')"
                    command.ExecuteNonQuery()
                    command.CommandText =
                        "Insert into Test2 (ID, Column1,Column2) VALUES (5, 'EE','ee')"
                    command.ExecuteNonQuery()
    
                    ' Commit the transaction.
                    transaction.Commit()
                    Console.WriteLine("Both records are written to database.")
    
                Catch ex As Exception
                    Console.WriteLine(ex.Message)
                    ' Try to rollback the transaction
                    Try
                        transaction.Rollback()
                        TextBox1.Text = "Transaction is not completed"
    
                    Catch
                        ' Do nothing here; transaction is not active.
                    End Try
                End Try
                ' The connection is automatically closed when the
                ' code exits the Using block.
            End Using
        End Sub

    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.

    • Marked as answer by alobi Tuesday, September 19, 2017 4:58 PM
    Monday, September 11, 2017 3:12 AM
    Moderator