Getting values back from OleDbDataReader reading from Access database

Question

Below it the code I'm using to connect to an Access database and pull the values from the query. Problem is.. I cannot get any values back from the reader object. I can see that there are the correct amount of rows, however I keep getting an InvalidOperationException (whether I use GetValue() or GetString()) saying "No data exists for the row/column."

        System.Data.OleDb.OleDbConnection conn = new
        System.Data.OleDb.OleDbConnection();
        conn.ConnectionString = @"Provider=Microsoft Office 12.0 Access Database Engine OLE DB Provider;" +
                                @"Data source= C:\Users\nearod\Desktop\ImportDB.accdb";
        try
        {
            conn.Open();
            OleDbCommand cmd = new OleDbCommand("SELECT * FROM [SQL Agent Unique ID Test Load]", conn);

            OleDbDataReader reader = cmd.ExecuteReader();

            string companyCode = reader.GetValue(0).ToString();
            string agentId = reader.GetString(1);
            string firstName = reader.GetString(2);
            string lastName = reader.GetString(3);
            string nameSuffix = reader.GetString(4);
            string corporateName = reader.GetString(5);
            string entityType = reader.GetString(6);
            string obfSSN = reader.GetString(7);
            string obfFEIN = reader.GetString(8);
            string dummyIndicator = reader.GetString(9);
            // Insert code to process data.
        }
        catch (Exception ex)
        {
            MessageBox.Show("Failed to connect to data source");
        }
        finally
        {
            conn.Close();
        }

Solution 1

you have to call Read method like below (use using instead of disposing yourself connection

string connectionString = @"Provider=Microsoft Office 12.0 Access Database Engine OLE DB Provider;" + @"Data source= C:\Users\nearod\Desktop\ImportDB.accdb";

string queryString=  "SELECT * FROM [SQL Agent Unique ID Test Load]";
 try
     {
    using (OleDbConnection connection = new OleDbConnection(connectionString))
        {
            OleDbCommand command = new OleDbCommand(queryString, connection);
            connection.Open();
            OleDbDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                string companyCode = reader.GetValue(0).ToString();
            string agentId = reader.GetString(1);
            string firstName = reader.GetString(2);
            string lastName = reader.GetString(3);
            string nameSuffix = reader.GetString(4);
            string corporateName = reader.GetString(5);
            string entityType = reader.GetString(6);
            string obfSSN = reader.GetString(7);
            string obfFEIN = reader.GetString(8);
            string dummyIndicator = reader.GetString(9);
            // Insert code to process data.
            }
            reader.Close();
        }
   }
catch (Exception ex)
   {
            MessageBox.Show("Failed to connect to data source");
   }

Solution 2

Modify your code like this:

using (OleDbConnection connection = new OleDbConnection(connectionString))
    {
        OleDbCommand command = new OleDbCommand(queryString, connection);
        connection.Open();

        using (OleDbDataReader reader = command.ExecuteReader())
        {

            while (reader.Read())
            {
                string companyCode = reader.GetValue(0).ToString();
                string agentId = reader.GetString(1);
                string firstName = reader.GetString(2);
                string lastName = reader.GetString(3);
                string nameSuffix = reader.GetString(4);
                string corporateName = reader.GetString(5);
                string entityType = reader.GetString(6);
                string obfSSN = reader.GetString(7);
                string obfFEIN = reader.GetString(8);
                string dummyIndicator = reader.GetString(9);
                // Insert code to process data.
            }
        }
    }