asked on

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...

Try
                    Console.WriteLine(Tabs() & "Preparing the new exception data...")
                    cmd.CommandText = "EXECUTE usp_PopulateNewExceptionsTable" : intNewExceptions = cmd.ExecuteNonQuery()
                    da.SelectCommand.CommandText = "SELECT * FROM tblExceptionsNew"
                    Do
                        dt = New DataTable
                        da.Fill(dt)
                    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
                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

Alexandre Simões

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.

AzaghalSEI

ASKER

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

AzaghalSEI

ASKER

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.

AzaghalSEI

ASKER

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...!

SOLUTION

Alexandre Simões

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

AzaghalSEI

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Alexandre Simões

--> "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.

AzaghalSEI

ASKER

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ões

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.

AzaghalSEI

ASKER

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.