0

I wrote the transaction below in C# to insert data into 4 tables in Access. I deployed the application and when one of the users is inserting data, the insert is failing and the transaction isn't rolling back. I can tell that there's a failed insert because the ID column has skipped numbers when I do have a successful insert.

Would you be able to review my code to see what could be causing this? And secondly, how could I test for this prior to deploying? I can't seem to replicate the user error.

         public static void InsertIndividualOwner(TaxInfo taxInfo, OwnerAddress address, OwnerEmailAddress emailAddress, IndividualOwner owner)
    {
        int ownerTaxInfoID = 0;
        int addressRecord = 0;
        int emailRecord = 0;
        int ownerRecord = 0;
        int specialistID = Properties.Settings.Default.DefaultUserId;
        string insertTaxInfoString = "Insert Into TaxInfo (TIN, BIRT, CAL, PIN, DateAdded, ModifiedBySpecialistID) " +
        "VALUES(@TIN, @BIRT, @CAL, @PIN, Date(), @ModifiedBySpecialistID)";
        string insertAddressString = "Insert Into OwnerAddress (ownerTaxInfoID, streetAddress1, streetAddress2, city, stateID, zip, country, DateAdded, ModifiedBySpecialistID) " +
         "Values(@ownerTaxInfoID, @streetAddress1, @streetAddress2, @city, @stateID, @zip, @country, Date(), @ModifiedBySpecialistID)";
        string insertEmailAddressString = "Insert Into OwnerEmailAddress (ownerTaxInfoID, emailAddress, DateAdded, ModifiedBySpecialistID) Values (@ownerTaxInfoID,@emailAddress, Date(), @ModifiedBySpecialistID)";
        string insertOwnerNameString = "Insert Into IndividualOwner (firstName, lastName, ownerTaxInfoID, DateAdded, ModifiedBySpecialistID) " +
        "VALUES(@firstName, @lastName, @ownerTaxInfoID, Date(), @ModifiedBySpecialistID)";
        string selectID = "Select @@Identity";
        using (OleDbConnection connection = new OleDbConnection(Constants.ACCESSCONNECTIONSTRING))
        {
            connection.Open();



            using (OleDbTransaction transaction = connection.BeginTransaction())
            {
                try
                {
                    using (OleDbCommand insertTaxInfo = new OleDbCommand(insertTaxInfoString, connection, transaction))
                    {
                        insertTaxInfo.Parameters.AddWithValue("@TIN", taxInfo.tin);
                        insertTaxInfo.Parameters.AddWithValue("@BIRT", taxInfo.birtNo);
                        if (string.IsNullOrEmpty(taxInfo.cal))
                        {
                            insertTaxInfo.Parameters.AddWithValue("@CAL", DBNull.Value);
                        }

                        else insertTaxInfo.Parameters.AddWithValue("@CAL", taxInfo.cal);
                        if (string.IsNullOrEmpty(taxInfo.pin))
                        {
                            insertTaxInfo.Parameters.AddWithValue("@PIN", DBNull.Value);
                        }

                        else insertTaxInfo.Parameters.AddWithValue("@PIN", taxInfo.cal);
                        insertTaxInfo.Parameters.AddWithValue("@ModifiedBySpecialistID", specialistID);

                        try
                        {
                            insertTaxInfo.ExecuteNonQuery();

                            insertTaxInfo.CommandText = selectID;
                            ownerTaxInfoID = (int)insertTaxInfo.ExecuteScalar();
                        }
                        catch (OleDbException ex)
                        {

                            throw ex;
                        }
                    }

                    using (OleDbCommand insertAddress = new OleDbCommand(insertAddressString, connection, transaction))
                    {
                        insertAddress.Parameters.AddWithValue("@ownerTaxInfoID", ownerTaxInfoID);
                        insertAddress.Parameters.AddWithValue("@streetAddress1", address.streetAddress1);
                        if (address.streetAddress2 == "")
                        {
                            insertAddress.Parameters.AddWithValue("@streetAddress2", DBNull.Value);
                        }

                        else insertAddress.Parameters.AddWithValue("@streetAddress2", address.streetAddress2);
                        insertAddress.Parameters.AddWithValue("@city", address.city);
                        insertAddress.Parameters.AddWithValue("@stateID", address.stateID);
                        insertAddress.Parameters.AddWithValue("@zip", address.zip);
                        insertAddress.Parameters.AddWithValue("@country", address.country);
                        insertAddress.Parameters.AddWithValue("@ModifiedBySpecialistID", specialistID);

                        try
                        {
                            insertAddress.ExecuteNonQuery();
                            insertAddress.CommandText = selectID;
                            addressRecord = (int)insertAddress.ExecuteScalar();
                        }
                        catch (OleDbException ex)
                        {

                            throw ex;
                        }

                    }

                    using (OleDbCommand insertEmailAddress = new OleDbCommand(insertEmailAddressString, connection, transaction))
                    {
                        insertEmailAddress.Parameters.AddWithValue("@ownerTaxInfoID", ownerTaxInfoID);
                        insertEmailAddress.Parameters.AddWithValue("@emailAddress", emailAddress.emailAddress);
                        insertEmailAddress.Parameters.AddWithValue("@ModifiedBySpecialistID", specialistID);

                        try
                        {
                            insertEmailAddress.ExecuteNonQuery();
                            insertEmailAddress.CommandText = selectID;
                            emailRecord = (int)insertEmailAddress.ExecuteScalar();
                        }
                        catch (OleDbException ex)
                        {

                            throw ex;
                        }
                    }

                    using (OleDbCommand insertOwner = new OleDbCommand(insertOwnerNameString, connection, transaction))
                    {
                        insertOwner.Parameters.AddWithValue("@firstName", owner.firstName);
                        insertOwner.Parameters.AddWithValue("@lastName", owner.lastName);
                        insertOwner.Parameters.AddWithValue("@ownerTaxInfoID", ownerTaxInfoID);
                        insertOwner.Parameters.AddWithValue("@ModifiedBySpecialistID", specialistID);

                        try
                        {
                            insertOwner.ExecuteNonQuery();
                            insertOwner.CommandText = selectID;
                            ownerRecord = (int)insertOwner.ExecuteScalar();
                        }
                        catch (OleDbException ex)
                        {

                            throw ex;
                        }

                    }

                    transaction.Commit();
                    AddOwner?.Invoke();
                }
                catch (Exception ex)
                {

                    transaction.Rollback();
                    throw ex;
                }
            }
        }
    }
Nicole
  • 104
  • 1
  • 2
  • 11
  • I cannot answer yet but I can see a very bad practice here. Never use _throw ex_, you loose the stacktrace and make everything more difficult to see where the error occurs. Also the internal try/catch seems to be unnecessary – Steve Sep 11 '18 at 13:11
  • Thanks. Can you please point me in the direction of what I should be using? – Nicole Sep 11 '18 at 13:12
  • Just _throw_ and no _ex_ – Steve Sep 11 '18 at 13:13
  • 1
    Explained better here https://stackoverflow.com/questions/730250/is-there-a-difference-between-throw-and-throw-ex – Steve Sep 11 '18 at 13:13

0 Answers0