locked
oledbtransaction is not saving to the database RRS feed

  • Question

  • I have this code below, it runs without error as far as I can  tell. At the end it  pops up a  message that "Both Records are written the table" but it actually does not save any value in DB. I am been working with this for two day and still getting the same result. Pls help. 

        Private Sub Testtransaction_Click(sender As Object, e As EventArgs) Handles Testtransaction.Click
            Dim conn As New OleDbConnection
            Dim cmd As New OleDb.OleDbCommand
            conn.ConnectionString = getConnection()
            Dim transaction As OleDbTransaction = Nothing
            'connection to New oledb Connectiont
            cmd.Connection = conn
            'open connection and execute transaction
            conn.Open()
            Try
                transaction = conn.BeginTransaction(IsolationLevel.ReadCommitted)
                cmd.Connection = conn
                cmd.Transaction = transaction
                'execute the commands
                cmd.CommandText = "INSERT INTO RegPat(Titl) Values(?)" 
                cmd.Parameters.AddWithValue("?", cboTitle.Text)
                cmd.ExecuteNonQuery()
                cmd.Parameters.Clear()
                cmd.CommandText = "INSERT INTO RegNoK(Title)Values(?)"
                cmd.Parameters.AddWithValue("?", CboTitleNok.Text)
                cmd.ExecuteNonQuery()
                transaction.Commit()
            Catch ex As Exception
                transaction.Rollback()
            End Try
            'transaction.Commit()
            MsgBox("Both Records Are Written to the tables")
        End Sub

    Friday, July 20, 2018 4:50 PM

Answers

  • If the database is in Solution Explorer, select the database properties, select "Copy to output directory", make sure it's set to copy if newer.

    See also 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

    • Proposed as answer by Paul P Clement IV Wednesday, July 25, 2018 5:19 PM
    • Marked as answer by alobi Wednesday, August 1, 2018 6:12 AM
    Saturday, July 21, 2018 3:59 AM
  • I have changed my code to Begin transaction() but I am still have the same result. Will appreciate all help thanks


    Are you sure the code is pointing to the right database?  
    • Marked as answer by alobi Wednesday, August 1, 2018 6:12 AM
    Friday, July 20, 2018 8:55 PM

All replies

  • BeginTransaction(IsolationLevel.ReadCommitted)


    Why are you using the ReadCommitted on inserts of records? It seems to me that there should be no IsolationLevel and just a simple BeginTransaction() should be used.

    Friday, July 20, 2018 5:02 PM
  • How should I re-write it?
    Friday, July 20, 2018 6:56 PM
  • I have changed my code to Begin transaction() but I am still have the same result. Will appreciate all help thanks

    Friday, July 20, 2018 7:43 PM
  • I have changed my code to Begin transaction() but I am still have the same result. Will appreciate all help thanks


    Are you sure the code is pointing to the right database?  
    • Marked as answer by alobi Wednesday, August 1, 2018 6:12 AM
    Friday, July 20, 2018 8:55 PM
  • Will go back and cross check

    Friday, July 20, 2018 9:32 PM
  • If the database is in Solution Explorer, select the database properties, select "Copy to output directory", make sure it's set to copy if newer.

    See also 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

    • Proposed as answer by Paul P Clement IV Wednesday, July 25, 2018 5:19 PM
    • Marked as answer by alobi Wednesday, August 1, 2018 6:12 AM
    Saturday, July 21, 2018 3:59 AM