UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Access Db Via .net C#, Access 2007    
 
   
paulroy
post Jan 28 2018, 11:42 AM
Post#1



Posts: 76
Joined: 26-April 10



Hi,
I have an Access 2007 split DB that I have been working on for many years. At one point I created a form that if kept open would automatically backup at night. Recently I decided to write a .NET C# Service that basically mocks the VBA code in the form. The function trims tables to keep things in check by removing old records. The problem I am encountering is that I am getting totally unexpected exceptions from the Service when trying to delete records, but on different records??? I keep a log file and here is a typical entry:
"<2018-Jan-28 10:08:18.583> <c1.2> <CRITICAL> <DBif> <exDBCommand> SQL Query: Delete * From [BottlingAppointments] Where [RecordID]=117706;Exception:Could not delete from specified tables."
I have been struggling with this for a week and not sure what to try next since I have no idea why this is happening. You should note that I have no Relationships in the database. This process runs like a charm via the Access form. In fact after these exceptions I am able to open the table and manually delete the affected records. In addition, if I run a manual backup from Access, there are no exceptions and those records that caused the exceptions in the Service are deleted without an issue?
The Service runs every 15 minutes and based on time can either Backup or send Emails. The DB is opened via an OleDbConnection(connectionString) once, all DB transactions are performed, and the DB is closed at the end.
I realize I may be in the wrong forum, but I am hoping that someone has done something similar and can perhaps share some insights...
Thanks,
Paul

Go to the top of the page
 
DanielPineault
post Jan 28 2018, 01:50 PM
Post#2


UtterAccess VIP
Posts: 5,948
Joined: 30-June 11



Do you ensure no lock file exists prior to trying to perform such operations? How do you know no ones is actively on that record, thus locking it?

--------------------
Daniel Pineault (2010-2017 Microsoft MVP)
Professional Help: http://www.cardaconsultants.com
Free MS Access Code, Tips, Tricks and Samples: http://www.devhut.net

* Design should never say "Look at me". It should always say "Look at this". -- David Craib
* A user interface is like a joke, if you have to explain it, it's not that good! -- Martin LeBlanc


All code samples, demonstration databases, links,... are provided 'AS IS' and are to be used at your own risk! Take the necessary steps to check, validate ...(you are responsible for your choices and actions)
Go to the top of the page
 
paulroy
post Jan 28 2018, 02:56 PM
Post#3



Posts: 76
Joined: 26-April 10



Yes, in fact I check if there are any Lock files before I begin the process and quit if any are found. Normally this would run at 4:00 am so there should be nobody one.
At this point, I know nobody is on since I am testing.
It is so weird, because when I run this from Access there are no issues, when I run from the Service I get these random exceptions.
I think I will have to run requests like transaction, delete a record, read it back, if not gone, delete again. I don't know what else to do to get around this.
I am a little discouraged by this since I spent several weeks writing the Service and it turns out to be completely unreliable????
Thanks
Go to the top of the page
 
jleach
post Jan 28 2018, 04:32 PM
Post#4


UtterAccess Editor
Posts: 9,911
Joined: 7-December 09
From: Staten Island, NY, USA


Did you actually write a windows service for this? They're notoriously difficult to debug and their permissions levels don't tend to follow the rest of the standard windows auth stuff.

Have you considered a simple console shell program that can run off a scheduler? Services are most commonly used for processes that must always be listening/acting on something.

--------------------
Go to the top of the page
 
paulroy
post Jan 29 2018, 09:23 AM
Post#5



Posts: 76
Joined: 26-April 10



Hi jleach,
1) Yes and yes you are right about debugging, but I have an substantial log that I am writing to file (gave up on the Event Log). I started by writing a Console App, did all the debugging (so I thought until this issue) and created an identical (for 99% of it) copy for the Service. This problem occurs on both the Service and Console Application.
2) My Access app is delivered to about 30 customers, I may be wrong here, but I felt this would be totally transparent to them since it must run ALL the time, even after a reboot.
Thanks,
Paul
Go to the top of the page
 
jleach
post Jan 29 2018, 10:31 AM
Post#6


UtterAccess Editor
Posts: 9,911
Joined: 7-December 09
From: Staten Island, NY, USA


Can you post the ADO code you're using to manage this? I can't quite get any ideas from the description you posted (although if you have a more specific exception type and/or hresult number, that might point us somewhere in terms of determining why the row can't be deleted).

You're not multithreading anything are you? If the ADO code is properly wrapped up there shouldn't be any self-imposed lock conflicts, but...

--------------------
Go to the top of the page
 
paulroy
post Jan 29 2018, 11:32 AM
Post#7



Posts: 76
Joined: 26-April 10



Please note that I may have found a workaround (ugly as [censored]) by trying to access the db a second time after it fails (you will see it in the exDBCommand method below) and in my testing this morning it has yet to more than once??? Again, please note that I am accessing hundreds of records in that loop and may get 15 to 20 failures on the first Delete, but not the second Delete in the exception code???
Thanks again!
Paul

Here is the loop causing the issue:
CODE
            //---------------------------------------------------------
            // Start off by Trimming all Bottling Appointments
            int lngOlderThan = Convert.ToInt32(db.getDBFieldValue("DeleteBottlingAppointmentsOlderThan", "CompanyProfile", "RecordID=1", 0));
            if (lngOlderThan > 0) {
                DataTable dtBottleTags = new DataTable("BottleTags");
                //if (!DBifData.getTable("Select * From BottleTags", daDataAdapter, dtBottleTags)) return;
                if (!db.getTable("Select * From BottleTags", dtBottleTags)) return;

                DataTable dtBottlingAppointments = new DataTable("BottlingAppointments");
                string strSQL = "SELECT * FROM BottlingAppointments Where BottleDate < #" + DateTime.Today.AddMonths(-lngOlderThan).ToString("MM-dd-yyyy") + "# AND AppointmentClosed=True;";
                //if (!dbIFData.getTable(strSQL, daDataAdapter, dtBottlingAppointments)) return;
                if (!db.getTable(strSQL, dtBottlingAppointments)) return;
                foreach (DataRow rowBA in dtBottlingAppointments.Rows) {
                    //Find all Bottle Tags linked to this appointment
                    int intBottlingAppointmentID = Convert.ToInt32(rowBA["RecordID"]);
                    DataRow[] rowsBT = dtBottleTags.Select("BottlingAppID=" + intBottlingAppointmentID.ToString());

                    //Loop through each BottleTag
                    foreach (DataRow rowBT in rowsBT) {
                        int intBottleTagID = Convert.ToInt32(rowBT["RecordID"]);
                        //Look for and delete the Barrel Reservation
                        if (!(rowBT.IsNull("BarrelReservationID"))) {
                            int intBarrelReservationID = Convert.ToInt32(rowBT["BarrelReservationID"]);
                            db.exDBCommand("Delete * From [BarrelReservations] Where [RecordID]=" + intBarrelReservationID.ToString() + ";");
                        }

                        //Delete all Bottle Details
                        db.exDBCommand("Delete * From [BottleTagDetails] Where [BottleTagID]=" + intBottleTagID + ";");
                    }

                    //Delete the Bottle Tag itself
                    db.exDBCommand("Delete * From [BottleTags] Where [BottlingAppID]=" + intBottlingAppointmentID.ToString() + ";");

                    //Delete the BottlingAppointment
                    db.exDBCommand("Delete * From [BottlingAppointments] Where [RecordID]=" + intBottlingAppointmentID.ToString() + ";");
                }
                dtBottleTags.Dispose();
                dtBottleTags = null;
                dtBottlingAppointments.Dispose();
                dtBottlingAppointments = null;
            }


and here is the code that accesses Access

CODE
        public int exDBCommand(string strSQL)
        {
            try {
                OleDbCommand cmd = new OleDbCommand(strSQL, conDB);
                return cmd.ExecuteNonQuery();
            }
            catch (Exception ex) {
                Thread.Sleep(500);
                try {
                    Logs.addEvent("Warning", "DBif", "exDBCommand", "SQL Query Failed First Time, 2nd Attempt: " + strSQL + "Exception:" + ex.Message + "; " + ex.HResult);
                    OleDbCommand cmd = new OleDbCommand(strSQL, conDB);
                    return cmd.ExecuteNonQuery();
                }
                catch (Exception exc) {
                    Thread.Sleep(1000);
                    try {
                        Logs.addEvent("Warning", "DBif", "exDBCommand", "SQL Query Failed Second Time, 3rd Attempt: " + strSQL + "Exception:" + exc.Message + "; " + exc.HResult);
                        OleDbCommand cmd = new OleDbCommand(strSQL, conDB);
                        return cmd.ExecuteNonQuery();
                    }
                    catch (Exception exce) {
                        Logs.addEvent("CRITICAL", "DBif", "exDBCommand", "SQL Query Failed Third Time: " + strSQL + "Exception:" + exce.Message + "; " + exce.HResult);
                        return -1;
                    }
                }
            }
        }
Go to the top of the page
 
jleach
post Jan 29 2018, 12:43 PM
Post#8


UtterAccess Editor
Posts: 9,911
Joined: 7-December 09
From: Staten Island, NY, USA


TBH, this looks a bit messy... I'm seeing a number of database-ish variables and I'm not seeing that any of the approrpiate objects are being disposed correctly, so while I'm not positive by any means, this very well might be a case of the process "tripping over itself" so to speak.

First thing I would do is make sure all connections, commands and adapters are properly being disposed. This is easiest done via using block:

CODE
using new(OleDbConnection conn = new OleDbConnection(connString))
    using (OleDbCommand cmd = new OleDbCommand(conn)) {

   // now conn and cmd will always be properly disposed regardless of how the method exits
   // do your work here

}


Next I"m curious why you have "db" and "dbifdata" - two different databases? In any case, if they ultimately point to the same place, better to wrap them up in a single variable.

And finally, rather than looping and executing delete command for each positive iteration, why not gather an array of record IDs, then run one command to delete all of the applicable IDs?

Again, I think it's a case of the code getting in its own way, and those would be some basic optimizations I'd start out with (lacking further insight into requirements).

hth

--------------------
Go to the top of the page
 
paulroy
post Jan 29 2018, 05:12 PM
Post#9



Posts: 76
Joined: 26-April 10



Thank you for your feedback.
This process runs every 15 minutes, the main routine instantiates a DBif class that opens the connection. The db (class variable) is then passed to 4 functions to perform time based events. The code you are seeing is only a part of the Backup process. When the last function terminates it returns to Main which calls CloseConnection which closes and disposes the connection and the service goes to sleep. I admit that I may need coaching, but I thought that creating one connection for the entire process would be more effort efficient than closing and opening the connection for every db access. Please correct me if I am wrong.

dbIFdata is commented out.

Sorry, but not sure I follow your recommendation to store all IDs in an array. There are 4 different tables and no consistency in the data.

I understand that you should not be writing my code, but I can certainly provide more code if you want to get a clearer understanding.
Thanks for the help!
Paul
Go to the top of the page
 
jleach
post Jan 29 2018, 05:23 PM
Post#10


UtterAccess Editor
Posts: 9,911
Joined: 7-December 09
From: Staten Island, NY, USA


>> I thought that creating one connection for the entire process would be more effort efficient than closing and opening the connection for every db access. Please correct me if I am wrong. <<

ADO.NET handles this automatically via connection pooling: it'll keep a number of connections handy, and when you close it via code, it doesn't fully close it but caches it for a period. Thus, the standard practice (with ADO.NET and most other .NET providers at least) is to open just before you need it and close as soon as you're done.

With that said, I'm not sure it's the issue, but could play a part.

As for the rest, it's really hard to say without knowing everything that's going on. I'm shooting in the dark as it is.

Regarding storing IDs, I just happened to notice that your loop is executing the deletes when it finds whatever condition flags it as needing to be deleted, then uses the ID to identify what to delete. I was thinking rather than performing the deletes themselves iteritively within the loop, instead record all of the IDs that need deleting, then after the loop is done and you've tracked all IDs-to-be-delete, run a single command to delete them all.

Something like this (aircode):

CODE
var barrelReservationIDsToDelete = new List<int>();
var bottlingAppointmentIDsToDelete = new List<int>();
// etc

for (blah blah blah) {
   // pretend you found a condition
   barrelReservationIDsToDelete.Add(yourID);
   // other stuff in your loop
   bottlingAppointmentIDsToDelete.Add(yourOtherID);
   // continue your loop gathering all IDs
}

// then execute them at once when the loop is done:
cmd.CommandText = "DELETE FROM YourTable WHERE ID IN (" + string.Join(barrelReservationIDsToDelete, ',');";
cmd.ExecuteNonQuery();
// etc


(NOTE! THIS IS BAD: you should use a parameterized version, NOT string concatenation, but that gets the idea across)

Something like that: then you a small handful of executing commands rather than multiple successive ones being fired in a loop.

(I might also add that I find the requirement to delete any records on regular basis to be a bit strange, especially in a case like this where there's many rows being deleted from different tables... makes me wonder if the root cause of the trouble might be something more fundamentally normalization related, in which case an operation like this shouldn't really be required anyway... idle speculation though).

Sorry I don't have anything in the way of more firm suggestions...

--------------------
Go to the top of the page
 
paulroy
post Jan 29 2018, 06:44 PM
Post#11



Posts: 76
Joined: 26-April 10



Thank you Jack, it's great to get another opinion and will try tomorrow!
Paul
Go to the top of the page
 
paulroy
post Jan 30 2018, 05:17 PM
Post#12



Posts: 76
Joined: 26-April 10



Jack,
I tried your suggestion about grouping all IDs in a group and then call Delete once for the group and I have to admit, at least up this point, it has worked like a charm, no errors!!!
I will keep testing and appreciate your help.
Thanks,
Paul
Go to the top of the page
 
sneakerhead22
post Mar 14 2018, 12:43 PM
Post#13



Posts: 53
Joined: 18-July 17



Paulroy how were you able to connect to the access db through c#??? I have been trying to do this for a while now. Althought I feel like if SQL would be the way to go at this point.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    19th June 2018 - 03:46 AM