A first chance exception of type 'System.InvalidOperationException' occurred in System.Data.dll

Question

I am building a C# application that reads a text file, writes the data read into an access 97 DB, it then reads the db and writes a file. I seem to be having some issues with my OLEDB connection string. Below is my code, anyone have any ideas why I am getting this exception?

The database does exist and I only open the connection before I execute the query and then I close it again.

               string fileName = "JobLogReport.mdb";
            string connectionString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + fileName + ";";
            OleDbConnection cnn = new OleDbConnection(connectionString);

            string[] fileNames = openFileDialog1.FileNames;
            int lineSkips = 2;
            int fileCount = 1;

            for (int i = 0; i < fileNames.Length; i++) 
            {
                if (i == 0) { tbProgress.Text += "Reading Job Log File Number:" + fileCount + "\r\n"; }
                if (i == 1) { tbProgress.Text += "Reading Job Log File Number:" + (fileCount + 1) + "\r\n"; }

                tbProgress.Text += "- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - \r\n";
                tbProgress.Text += "Populating Database \r\n";
                tbProgress.Text += "- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - \r\n";
                fileReader = new StreamReader(fileNames[i].ToString());
                while (!fileReader.EndOfStream)
                {
                    String file = fileReader.ReadLine();
                    if (file.StartsWith("IC.PXPSG"))
                    {
                        OleDbCommand command = new OleDbCommand();
                        char[] delimiters = new char[] { ' ' };
                        string[] line = file.Split(delimiters, StringSplitOptions.RemoveEmptyEntries);

                        command.CommandText = "INSERT INTO DocsReceived (Filename, Documents) VALUES (?,?)";
                        command.Parameters.AddWithValue("@filename", line[0]);
                        command.Parameters.AddWithValue("@documents", Int32.Parse(line[1], NumberStyles.Any, CultureInfo.InvariantCulture));
                        command.Connection = cnn;
                        cnn.Open();
                        command.ExecuteNonQuery();
                        cnn.Close();

Looking at the connection object I notice two things.

Server Version says:'cnn.ServerVersion' threw an exception of type 'System.InvalidOperationException'
Also cnn.Open() and cnn.OpenAsync() both don't change the state of the connection.

Seems like cnn.Open(); isn't opening the connection which is causing the exception to be thrown. — GhostDZ9, Jan 22 '15 at 16:04
Are you specifying the full path to the DB or just the file name? — Jacob Lambert, Jan 22 '15 at 16:24
Hi.. Can you try Odbc stuffs in place of OleDB? Also try setting up a DSN to include the Access Db as a connection string. — Anurag, Jan 22 '15 at 16:29
Also ODBC requires a driver, I need it to work without a driver. — GhostDZ9, Jan 22 '15 at 16:34

score 0 · Answer 1 · edited Jun 09 '17 at 08:37

0

Try setting up a DSN using Control Panel>Administrative Tools>Setup data sources (ODBC)>System DSN>Add.

Give the Data Source name as MYDSN, then click on Select and browse to your Access DB.

See the snapshot.

enter image description here

after that try the code as follows:

string strDSN = "DSN=MYDSN";
string cmdText = "Insert into AccessTable (ColumnA,ColumnB) Values (?,?)";
using (OdbcConnection cn = new OdbcConnection(strDSN))
{
    using (OdbcCommand cmd = new OdbcCommand(cmdText, cn))
    {
        cn.Open();
        foreach (DataRow r in dt.Rows)
        {
            cmd.Parameters.Clear();
            cmd.Parameters.AddWithValue("@p1", r["ColumnA"].ToString());
            cmd.Parameters.AddWithValue("@p2", r["ColumnB"].ToString());
            cmd.ExecuteNonQuery();
        }

    }
}

edited Jun 09 '17 at 08:37

Tshilidzi Mudau

5,472
6
32
44

answered Jan 22 '15 at 16:39

Anurag

510
8
27

The image in the answer? – Anurag Jan 22 '15 at 16:48
I have added a new image. Check it. – Anurag Jan 22 '15 at 17:11
The image is still broken. – GhostDZ9 Jan 22 '15 at 17:14
Its not broken for me brother. Anyways have hosted the image here http://s10.postimg.org/umihd50rd/odbc.png. Please have a look. – Anurag Jan 22 '15 at 17:17

score 0 · Answer 2 · answered Jan 22 '15 at 18:38

So after doing more investigation I have determined that the oledb driver in my connection string supports only 32bit applications. I am using a 64 bit machine and so the application properties automatically makes it a 64 bit application.

I did the following steps to resolve the issue:

Right Click on the Project in Solution Explorer
Select Properties
Select the Build Tab
Change Platform Target from 'Any CPU' to 'x86'(32Bit)
Re-build the application and it should work.

A first chance exception of type 'System.InvalidOperationException' occurred in System.Data.dll

2 Answers2