OleDBCommand.ExecuteNonQuery() method behaving asynchronously with MS Access DB - WHY?!

All, this is VS 2005:

I have a relatively simple MS Access database in which I have built several "stored procedures" (UPDATE, INSERT INTO, and DELETE statements)..

One of them is a simple INSERT INTO...SELECT FROM statement which populates a table. This table is then used as a source for proceeding code that uses an OleDBDataAdapter to fill a DataTable.

I am executing the INSERT INTO...SELECT FROM statement using EXECUTE statment in the CommandText property of the OleDBCommand object, and the ExecuteNonQuery method.

Here comes the problem:

ExecuteNonQuery fires...
...code execution proceeds...
...and then it's a luck of the draw whether or not the records are injected into the destination table by the time the OleDBDataAdapter tries to fill the DataTable.

Below I am using a hack to check that it's finished (Do..Loop Until dt.Rows.count = the result from the ExecuteNonQuery)

This just can't be how this method was designed to work. Little help here?

Thank you so much for your time... I will not post all of the code, but all of what matters! FYI, there are NO exceptions thrown, and the connection is most certainly open...
                    Console.WriteLine(Tabs() & "Preparing the new exception data...")
                    cmd.CommandText = "EXECUTE usp_PopulateNewExceptionsTable" : intNewExceptions = cmd.ExecuteNonQuery()
                    da.SelectCommand.CommandText = "SELECT * FROM tblExceptionsNew"
                        dt = New DataTable
                    Loop Until dt.Rows.Count = intNewExceptions
                    Console.WriteLine(Tabs(2) & "Complete!")
                Catch ex As Exception
                    dteEnd = Now : Console.WriteLine("Process failed at " & dteEnd.ToLongTimeString)
                    strMessage &= "Application Error #: 8" & vbCrLf
                    strMessage &= "Application Error Message: An error has occurred while preparing the new exception data. " & _
                                  ex.Message & vbCrLf
                    strMessage &= "Process Start Time: " & dteStart.ToLongTimeString & vbCrLf
                    strMessage &= "Process End Time: " & dteEnd.ToLongTimeString & vbCrLf & vbCrLf
                    swLog.WriteLine(strMessage) : swLog.Flush()
                    swLog.Close() : swLog = Nothing
                    If da IsNot Nothing Then da = Nothing
                    If con IsNot Nothing Then
                        If con.State <> ConnectionState.Closed Then con.Close()
                        con = Nothing
                    End If
                    If cmd IsNot Nothing Then cmd = Nothing
                    If dt IsNot Nothing Then dt = Nothing
                    GC.WaitForPendingFinalizers() : GC.Collect()
                    Return 8
                    Exit Function
                End Try
                    Console.WriteLine(Tabs() & "Processing the TIFF and XML files...")
                    da.SelectCommand.CommandText = "SELECT * FROM qryWorkflowItems"
                    dt = New DataTable
                    intWorkflowItems = da.Fill(dt)
                    If intWorkflowItems > My.Settings.FailsafeVolume Then
                        If Not IO.Directory.Exists(My.Settings.FailsafeOutputPath & Today.ToString("yyyy-MM-dd")) Then
                            IO.Directory.CreateDirectory(My.Settings.FailsafeOutputPath & Today.ToString("yyyy-MM-dd"))
                        End If
                    End If

Open in new window

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alexandre SimõesManager / Technology SpecialistCommented:
You're using EXECUTE to call you "sp" so  the process becomes asynchronous.
As you're using DataAdpters, why don't you create the insert statement on the dataadapter and call it from there?
This will make the call synchronous, it will only step to the next line of the code when the insert is actually done.

Another idea is to try to run that [usp_PopulateNewExceptionsTable] without the EXECUTE instruction.
AzaghalSEIAuthor Commented:
I'll it this morning, and let you know the results.. What I find odd is that this chunk works like a champ:
  Console.WriteLine(Tabs() & "Finalizing exception data...")
                    cmd.CommandText = "EXECUTE usp_UpdateExceptionAges" : cmd.ExecuteNonQuery()
                    cmd.CommandText = "EXECUTE usp_MarkClearedExceptions" : cmd.ExecuteNonQuery()
                    cmd.CommandText = "EXECUTE usp_MarkPreexistingItems" : cmd.ExecuteNonQuery()
                    cmd.CommandText = "EXECUTE usp_ArchiveClearedItems" : cmd.ExecuteNonQuery()
                    cmd.CommandText = "EXECUTE usp_DeleteMarkedCurrentItems" : cmd.ExecuteNonQuery()
                    cmd.CommandText = "EXECUTE usp_DeleteMarkedNewItems" : cmd.ExecuteNonQuery()
                    cmd.CommandText = "EXECUTE usp_MoveNewExceptions" : cmd.ExecuteNonQuery()
                    cmd.CommandText = "EXECUTE usp_EmptyNewExceptionsTable" : cmd.ExecuteNonQuery()
                    Console.WriteLine(Tabs(2) & "Complete!")

Open in new window

AzaghalSEIAuthor Commented:
I set cmd.CommandType = CommandType.StoredProcedure; however, when I attempt to do this:

cmd.CommandText = "usp_PopulateNewExceptionsTable" : intNewExceptions = cmd.ExecuteNonQuery()

Instead of this:

cmd.CommandText = "EXECUTE usp_PopulateNewExceptionsTable" : intNewExceptions = cmd.ExecuteNonQuery()

I get this error:

Expected query name after EXECUTE.

Have I misinterpreted your suggestion? I can't stand this thing!!

Thank you for your help.

Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

AzaghalSEIAuthor Commented:
I am now attempting to wrap each group of EXECUTE statements within an OleDBTransaction through the connection to the database...

I will post my results...!
Alexandre SimõesManager / Technology SpecialistCommented:
Do you really need to use SP's on access?
If you use queries like any other, access identifies it as Insert or Update query and from OleDB you can use it passing the parameters.

I think this if the best approach...

I would advise you to make a small sample, converting one of your SPs into this kind of approach.
AzaghalSEIAuthor Commented:

 I understand your point of view, but in this case the business wanted a different level of personnel to be able to control the filtration and population of the data. None of the statements require any variable parameters and are related to the tagging, moving, and deleting of records. (Basically they wanted to be able to screw with the statements ad hoc without any change control... wonderful practice!)

 I have found that by wrapping all of the ExecuteNonQuery code within an OleDBTransaction I was able to have execution remain synchronous.

 Thank you very much for replying, and I invite anyone else to share their experience and ideas as I find this to be a silly way to switch between asynch and synch execution.

 Below I have posted two code samples, and in the first example intCount would reach 19 before the row count was correct (i.e. the EXECUTE statement complete.) With the second it is complete on the first pass.
                    cmd.CommandText = "EXECUTE usp_PopulateNewExceptionsTable" : intNewExceptions = cmd.ExecuteNonQuery()
                    da.SelectCommand.CommandText = "SELECT * FROM tblExceptionsNew"
                    Dim intCount As Integer = 0
                        dt = New DataTable
                        intCount += 1
                    Loop Until dt.Rows.Count = intNewExceptions
                    txn = con.BeginTransaction : cmd.Transaction = txn
                    cmd.CommandText = "EXECUTE usp_PopulateNewExceptionsTable" : intNewExceptions = cmd.ExecuteNonQuery() : txn.Commit()
                    da.SelectCommand.CommandText = "SELECT * FROM tblExceptionsNew"
                    Dim intCount As Integer = 0
                        dt = New DataTable
                        intCount += 1
                    Loop Until dt.Rows.Count = intNewExceptions

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Alexandre SimõesManager / Technology SpecialistCommented:
--> "None of the statements require any variable parameters"

So, why don't you create views with your sql statements?
Users can still access them and you will be able to call them synchronously from your code.
AzaghalSEIAuthor Commented:
I'm not sure I follow what you are saying. They are DELETE, UPDATE, and INSERT INTO..SELECT FROM statements that are performing actions on the database.

Data is shifted from table to table tracking status, age of items, cleared dates, and the like.

Could you clarify? I suspect we're just speaking a slightly different language!
Alexandre SimõesManager / Technology SpecialistCommented:
You were saying that those actions don't require any input arguments right?
If so, you can create a new query on Access and write those DELETE, UPDATE and INSERT statements.

I don't know if I'm not understanding you right... sorry if I'm not.
AzaghalSEIAuthor Commented:
Hmmm... I hate these moments, don't you? haha

OK, right now I have:

8 tables (With all of the appropriate indexes, relationships, and keys...)
5 views (What you might call queries, or SELECT statements?)
17 stored procedures (What you might call action queries?)

The 17 stored procedures (or action queries) consist of 3 UPDATE statements (or Update Queries), 4 INSERT INTO...SELECT FROM statements (or Append Queries), and 10 DELETE statements (or Delete Queries.)

These stored procedures/action queries are executed in a specific order by my console application in order to empty tables for repopulation of base data, and move them around from new to current to archive tables, and similar actions. Excel workbooks, .TIF files, and .XML files are created from this data for other post-processing and automation.

Now using this method the support personnel can simply go into the backend database and modify any of the "semi-hard-coded" parameters for this workflow in order to add or remove items conditionally from the workflow. There is no need for me to alter the console application source code, or config file.

That all being said I don't agree with letting people just have at it with the database, but they are relatively qualified so integrity is not a major concern.

I suppose I find the general plan acceptable and it would be akin to using stored procedures in say MS SQL Server, but that was not in the budget (what is these days?) for this project.

So given the plan I'm hoping to hear more from folks on the best coding style/methods for this process via Ole.

I hope you don't feel patronized by my explanation; I meant no disrespect and was only trying to be thorough!!

Thank you as always.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.