Select statement using OleDbCommand throws InvalidOperationException

Question

I need to find a record in C# but I get an InvalidOperationException:

public static Auto findAuto(int kfznr)
{
    Auto retAuto = new Auto();
    try
    {
        myOleDbConnection.Open();
        string query = "SELECT * FROM Auto WHERE Auto.KFZNR = " + kfznr; 
        
        OleDbCommand select = new OleDbCommand();
        select.Connection = myOleDbConnection;
        select.CommandText = query;
        OleDbDataReader reader = select.ExecuteReader();

        while (reader.Read())
        {
            Auto at = new Auto(Convert.ToInt32(reader[0]), Convert.ToString(reader[1]), Convert.ToString(reader[2]));

            retAuto = at;
        }
    }
    catch (OleDbException e)
    {

        Console.WriteLine(e.ToString());
    }

    return retAuto;
}

I get the error in the while loop at the creation of the new Auto. When I run the same query in the SQLDeveloper I get one record (take a look at the first screenshot) but in my C# program I get there is no data for my row/cell.

When I hover the reader I get the following image. It says that the reader has rows:

Hope that you can help me fix this problem.

Answer 1

You need to use reader.GetValue(0)

    public static Auto findAuto(int kfznr)
    {
        Auto retAuto = new Auto();
        try
        {
            myOleDbConnection.Open();
            string query = "SELECT * FROM Auto WHERE Auto.KFZNR = " + kfznr; 

            OleDbCommand select = new OleDbCommand();
            select.Connection = myOleDbConnection;
            select.CommandText = query;
            OleDbDataReader reader = select.ExecuteReader();

            while (reader.Read())
            {
                Auto at = new Auto(Convert.ToInt32(reader.GetValue(0)), Convert.ToString(reader.GetValue(1)), Convert.ToString(reader.GetValue(2)));

                retAuto = at;
            }
        }
        catch (OleDbException e)
        {

            Console.WriteLine(e.ToString());
        }

        return retAuto;
    }

Also wanted to add...you can access the reader values by column name as well using:

reader["ColumnName"]...don't forget the "" around the name of the column ;)

public static Auto findAuto(int kfznr)
{
    Auto retAuto = new Auto();
    try
    {
        myOleDbConnection.Open();
        string query = "SELECT * FROM Auto WHERE Auto.KFZNR = " + kfznr; 

        OleDbCommand select = new OleDbCommand();
        select.Connection = myOleDbConnection;
        select.CommandText = query;
        OleDbDataReader reader = select.ExecuteReader();

        while (reader.Read())
        {
            Auto at = new Auto(Convert.ToInt32(reader["col1"]), Convert.ToString(reader["col2"]), Convert.ToString(reader["col3"]));

            retAuto = at;
        }
    }
    catch (OleDbException e)
    {

        Console.WriteLine(e.ToString());
    }

    return retAuto;
}