IndexOutOfRangeException when reading in a field from an Access Database

Question

I'm having trouble reading values into a list from an Access Database. The values look like this: Field is called EventDistance Values are 77 77 77 77 55 112 45 46

My code looks like this:

 private void StartSchedule_Click(object sender, EventArgs e)
    {
        string ConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=F:\A2 Computing\C# Programming Project\TriHard.accdb";
        string SelectQuery = "SELECT Time.AthleteID, Athlete.AthleteName,
                              Time.EventTime, Event.EventDistance 
                              FROM Event INNER JOIN 
                                   (Athlete INNER JOIN [Time] 
                                    ON Athlete.[AthleteID] = Time[AthleteID]) 
                                    ON Event.[EventID] = Time.[EventID];";

        OleDbConnection Connection = new OleDbConnection(ConnectionString);
        OleDbCommand Command = new OleDbCommand(SelectQuery, Connection);
        Command.Connection.Open();
        OleDbDataReader Reader = Command.ExecuteReader(CommandBehavior.CloseConnection);

        PaceCalculator pace = new PaceCalculator();
        List<PaceCalculator> Distancelist = new List<PaceCalculator>();
        while (Reader.Read())
               {
                   pace.Distance = (int)Reader["Event.EventDistance"];
                   Distancelist.Add(pace);
               }

And its throwing IndexOutOfRanceException was unhandled for Event.EventDistance. There are only 8 integers to be read in, but there must be room for my data to expand. How do I make it so it just reads in the integers without throwing an index error?

Steve · Accepted Answer · 2015-02-05 13:05:22Z

If you look at the source code of the OleDbDataReader for the indexer that gets a string as input parameter and then follow the code, you will notice that the string passed to the indexer is searched between the field names available and, if not found, an IndexOutOfRange exception is thrown.

In your query, there is no field named "Event.EventDistance".
There is a field named EventDistance (the tablename should be stripped away from the Reader indexer.

But you have also another problem, your loop add repeatedly the same PaceCalculator instance to the list.
So in every loop you set the EventDistance to the same PaceCalculator instance and add it again and again to the list. At the end of the loop the list is full of elements that points to the same PaceCalculator instance and this instance has the value of the last record read.

You could fix this problem moving the creation of the instance inside the loop

   List<PaceCalculator> Distancelist = new List<PaceCalculator>();
   while (Reader.Read())
   {
       PaceCalculator pace = new PaceCalculator();
       pace.Distance = (int)Reader["EventDistance"];
       Distancelist.Add(pace);
   }

Gord Thompson · Accepted Answer · 2015-02-04 21:57:52Z

2

When you run a query like

SELECT Table1.Column1, Table2.Column2 FROM Table1 INNER JOIN Table2 ...

the resulting columns in the OleDbDataReader are (usually) just named "Column1" and "Column2", so to reference them you just need to use

reader["Column1"]

not

reader["Table1.Column1"]

You are getting an IndexOutOfRange exception because there is no column in the reader named "Event.EventDistance".

answered Feb 4, 2015 at 21:57

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

1 Comment

George Boulton Over a year ago

Thank you so much... I have been stressing over this so much, but its solved my error. Thank you again!

Collectives™ on Stack Overflow

IndexOutOfRangeException when reading in a field from an Access Database

2 Answers 2

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related