Why do I get an out of Range message when I am trying to convert Timestamp

Question

So I have the following code :

listOfUserLogs.Add(new Log { TimeStamp = Convert.ToDateTime(myReader["TimeStamp"]), CheckpointId = Convert.ToInt32(myReader["CheckpointId"]) });

And when I run the program I get the System.IndexOutOfRangeException {"TimeStamp"} . I don't understand why is this so and how to fix it .

Note: I edited the post so you can see the entire code and let me know what am I missing.

You can see my program here :

namespace Distance
{
    class Program
    {
        static void Main(string[] args)
        {
            string connectionString = GetConnectionString();


            using (SqlConnection sourceConnection =
                       new SqlConnection(connectionString))
            {
                sourceConnection.Open();

                SqlDataReader myReader = null;

                SqlCommand myCommand = new SqlCommand("SELECT User.Id , [Log].[TimeStamp] ,[Checkpoints].[Id] ,[Checkpoints].[Coordinates] FROM dbo.[Users] INNER JOIN dbo.[Log] ON [Log].[UserId] =[Users].[Id] INNER JOIN dbo.[Checkpoints] ON [Checkpoints].[Id] = [Log].[CheckpointId] ", sourceConnection);

                //SqlCommand myCommand = new SqlCommand("SELECT User.Id ,User.Name ,Checkpoint.Id ,Checkpoint.Coordinates , Log.TimeStamp FROM dbo.Users, INNER JOIN dbo.Log ON Log.UserId = User.Id, INNER JOIN dbo.Checkpoints ON Checkpoint.Id = Log.CheckpointId ;", sourceConnection);

                myReader = myCommand.ExecuteReader();
                var listOfUsers = new List<User>(); //get users from db
                //long countStart = System.Convert.ToInt32(myCommand.ExecuteScalar());
                var listOfCheckpoints = new List<Checkpoint>(); //get checkpoints from db
                var listOfUserLogs = new List<Log>();
                while (myReader.Read())
                {
                    listOfUsers.Add(new User
                    {
                       Id = Convert.ToInt32(myReader["Id"]),
                       Name = myReader["Name"].ToString(),
                       Coordinates = myReader["Coordinates"].ToString()
                    });

                    listOfCheckpoints.Add(new Checkpoint
                    {
                        Id = Convert.ToInt32(myReader["Id"]),
                        Coordinates = myReader["Coordinates"].ToString()
                    });

                    listOfUserLogs.Add(new Log
                    {
                        TimeStamp = Convert.ToDateTime(myReader["TimeStamp"]),
                        CheckpointId = Convert.ToInt32(myReader["CheckpointId"]),
                        UserId =Convert.ToInt32(myReader["UserId"])
                    });

                }


            StringBuilder sb = new StringBuilder();
            foreach (var user in listOfUsers)
            {
                string address = user.Coordinates;

                DateTime currentDate = new DateTime(2014, 8, 1);
                var dictionary = new Dictionary<string, double>();

                while (currentDate <= DateTime.Now)
                {
                    double dayUserDistance = 0.00;
                   // var listOfUserLogs = new List<Log>(); //Get logs where day == currentDate from db
                    var previousCoordinate = address;
                    foreach (var log in listOfUserLogs)
                    {

                        Checkpoint checkpoint = listOfCheckpoints.FirstOrDefault(x => x.Id == log.CheckpointId);


                        dayUserDistance += DistanceCalculator.GetDistance(previousCoordinate, checkpoint.Coordinates);
                        previousCoordinate = checkpoint.Coordinates;

                    }
                    dayUserDistance += DistanceCalculator.GetDistance(previousCoordinate, address);

                    dictionary.Add(currentDate.ToString("yyyy-MM-dd"), dayUserDistance);

                    currentDate = currentDate.AddDays(1);
                }

                sb.Append(user.Name + ";");
                foreach (KeyValuePair<string, double> keyValuePair in dictionary)
                {
                    sb.Append(keyValuePair.Value + ";");
                }
                sb.AppendLine();
            }
            Console.WriteLine();
            Console.ReadLine();
            }
        }
                 private static string GetConnectionString()
                // To avoid storing the sourceConnection string in your code,  
                // you can retrieve it from a configuration file. 
                {
                    return "Data Source=BESA-PC;" +
                        " Integrated Security = true;" +
                        "Initial Catalog=CykelScore2;";
                }

        }
    }

internal class DistanceCalculator
    {
        public static double GetDistance(string previousCoordinate, string coordinates)
        {
            string[] PairSequence = previousCoordinate.Split(',');

            float sLatitude = float.Parse(PairSequence[0]);
            float sLongitude = float.Parse(PairSequence[1]);

            string[] PairSequence2 = coordinates.Split(',');

            float eLatitude = float.Parse(PairSequence2[0]);
            float eLongitude = float.Parse(PairSequence2[1]);



            var sCoord = new GeoCoordinate(sLatitude, sLongitude);
            var eCoord = new GeoCoordinate(eLatitude, eLongitude);



            return sCoord.GetDistanceTo(eCoord);

        }
    }

    internal class Checkpoint
    {
        public int Id { get; set; }
        public string Coordinates { get; set; }

    }

    internal class Log
    {
        public DateTime TimeStamp { get; set; }
        public int CheckpointId { get; set; }
        public int UserId { get; set; } 

    }

    internal class User
    {
        public int Id { get; set; }
        public string Coordinates { get; set; }
        public string Name { get; set; }

    }

Answer 1

There are a lot of problems in your code above. Basically you are trying to model your classes following the exact model of your tables and this is not always the best path.
For example, I would design your classes in this way

(To avoid naming confusion between the new class Coordinate and the string coordinates I have renamed the latter to Location)

internal class Coordinate
{
    public int coordID { get; set; }                // This is your CheckpointID
    public string Location { get; set; }            // This is the string coordinate loaded 
    public DateTime TimeStamp { get; set; }         // This is the TimeStamp of the coordinate
}

internal class User
{
    public int Id { get; set; }
    public string Name { get; set; }
    public List<Coordinate> Coordinates {get;set;}
}

With these changes in place I would modify your query in this way

@"SELECT User.Id AS UserID, 
        [Log].[TimeStamp],
        [Checkpoints].[Id] as CheckPointID,
        [Checkpoints].[Coordinates] as Location 
  FROM dbo.[Users] INNER JOIN dbo.[Log] 
                        ON [Log].[UserId] = [Users].[Id] 
                   INNER JOIN dbo.[Checkpoints] 
                        ON [Checkpoints].[Id] = [Log].[CheckpointId]
  ORDER BY User.ID, [Log].[TimeStamp]"   <--- This order by is very important

At this point your loop should change with

User usr = null;
int curUserID = -1;
while (myReader.Read())
{
    int id = Convert.ToInt32(myReader["UserId"]);
    if(curUserID != id)
    {
        // Enter this block only if the user changes from the previous one
        // They are ordered so you are sure to get them in line
        usr = new User()
        {
             Id = id, 
             Name = reader["Name"].ToString(), 
             Coordinates = new List<Coordinate>()
        };
        curUserID = id;
        listOfUsers.Add(usr);
    }

    // Add all the coordinates that belong to the same user
    Coordinate cc = new Coordinate()
    {
        cc.coordID = Convert.ToInt32(reader["CheckPointID"]);
        cc.TimeStamp = Convert.ToDateTime(reader["TimeStamp"]);
        cc.Location = reader["Location"].ToString();
    };
    usr.Coordinates.Add(cc);
}

At the end of this loop you could loop on the listOfUser and calculate the distance using the List<Coordinate> that belongs to a specific user after ordering them using the TimeStamp

foreach(User usr in listUser)
{
    ...
    foreach(Coordinate cc in usr.Coordinates.OrderBy(x => x.TimeStamp)
    {
        ......
    }

}

Said that, I really recommend you to spend a bit of your time trying to learn the usage of a modern ORM tool (Entity Framework, Dapper) that would remove all the code to load data from DB and let you concentrate your efforts on the logic required by your task.