How to solve Connection is closed-Error with OleDBConnection

Question

I am normally using Java and only use C# for reading an old MS Access 95 Database so I am new to this. I have a C# script that opens an OleDBConnection to the access db and reads several data from it:

using System;
using System.Data.OleDb;


namespace ReadMsAccessDB
{
    class Program
    {
        static void Main(string[] args)
        {
            const string connectionString = "Provider = Microsoft.Jet.OLEDB.4.0; Data Source = C:/workspace-csharp/ReadMsAccessDB/ReadMsAccessDB/File/remote.mdb; Persist Security Info = True";
        
            using (var con = new OleDbConnection(connectionString))
            {
                con.OpenAsync();
                var cmd = con.CreateCommand();
                cmd.CommandText = "select * from summary_0199_550";
                cmd.Connection = con;

                using (var dataReader = cmd.ExecuteReader())
                {
                    while (dataReader.Read())
                    {
                        var output = "ScanDateTime: " + dataReader.GetValue(3) + "\n"
                                                        + "RCP_Name: " + dataReader.GetValue(13) + "\n"
                                                        + "Slot: " + dataReader.GetValue(14) + "\n"
                                                        + "DFCT_Tot: " + dataReader.GetValue(19) + "\n"
                                                        + "Area_Count: " + dataReader.GetValue(22) + "\n"
                                                        + "Part1: " + dataReader.GetValue(23) + "\n"
                                                        + "Part2: " + dataReader.GetValue(24) + "\n"
                                                        + "Part3: " + dataReader.GetValue(25) + "\n"
                                                        + "Part4: " + dataReader.GetValue(26) + "\n"
                                                        + "Part5: " + dataReader.GetValue(27) + "\n"
                                                        + "Part6: " + dataReader.GetValue(28) + "\n"
                                                        + "Part7: " + dataReader.GetValue(29) + "\n"
                                                        + "Part8: " + dataReader.GetValue(30) + "\n"
                                                        + "HazeRegion: " + dataReader.GetValue(33) + "\n"
                                                        + "HazeAverage: " + dataReader.GetValue(34) + "\n"
                                                        + "HazePeak: " + dataReader.GetValue(35) + "\n\n";
                        Console.WriteLine(output);
                    }

                }

            }

            Console.ReadLine();
        }
    }
}

The project is a console app and when I hit Run in Visual Studio while having the configuration mode on Debugging it works perfectly fine. But when I try to run it without Debugging Mode or publishing it and try to run the .exe file I get an connection Error like this:

Unhandled exception. System.InvalidOperationException: ExecuteReader requires an open and available Connection. The connection's current state is closed.
at System.Data.OleDb.OleDbConnection.CheckStateOpen(String method)
at System.Data.OleDb.OleDbCommand.ValidateConnection(String method)
at System.Data.OleDb.OleDbCommand.ValidateConnectionAndTransaction(String method)
at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method)
at System.Data.OleDb.OleDbCommand.ExecuteReader(CommandBehavior behavior)
at ReadMsAccessDB.Program.Main(String[] args) in C:\workspace-csharp\ReadMsAccessDB\ReadMsAccessDB\Program.cs:line 18

I dont understand why it says that the connection state is closed because I am opening the connection with con.OpenAsync(); in the using section?

`async` methods need to be awaited using the `await` keyword. Otherwise the execution of the current method continues before the `OpenAsync` method finishes. You can probably use the non-async variant `Open` if it exists. — Biesi, Sep 28 '20 at 08:12
When I use just ```Open``` I get an exception that the ```OLEDB.4.0``` Provider is not registered on the local machine: ```Unhandled exception. System.InvalidOperationException: The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine.``` — NECben067, Sep 30 '20 at 06:00
Does this help then? https://stackoverflow.com/questions/1991643/microsoft-jet-oledb-4-0-provider-is-not-registered-on-the-local-machine — Biesi, Sep 30 '20 at 07:08
I installed the ```Ace``` Driver and set the Provider to ```Provider=Microsoft.ACE.OLEDB.12.0;``` as mentioned in the answer but it did not work. I got the same exception. But then I set the target platform to x86 for the project and now it seems to works with the ```Jet``` Driver. — NECben067, Sep 30 '20 at 07:30
if file format is mdb, then JET can be used. If file format is accDB, then you have to use ACE. And yes, you have to set/force the target CPU to x86. And of course you can't do a async open unless you wire up code to wait until the connection is done. And if you going to wait, then you might as well open without async code. and by running as compiled code, it runs faster, and thus the open code is not yet finished and the code drops right on past that without waiting until open and starts running the code that needs the connection. So compiled code runs faster - too fast without a open wait. — Albert D. Kallal, Oct 01 '20 at 13:06

How to solve Connection is closed-Error with OleDBConnection

0 Answers0