Inserting in sql Database while maybe out of range

Question

I have an question. I'm trying to insert some values into an mySql database, but I have a small issue.

        for (int i = 0; i < Chauffeurlijst.Count; i++)
        {
            db.Insert("Insert into route (Voertuigen_ID,Chauffeurs_ID,Planning_ID) VALUES(@voertuigid,@chauffeurid,@planningid", new List<KeyValuePair<string, object>>
        {

            new KeyValuePair<string, object>("@voertuigid", Voertuigenlijst[i].ID),
            new KeyValuePair<string, object>("@chauffeurid", Chauffeurlijst[i].ID),
            new KeyValuePair<string, object>("@planningid", planning.ID)
        });
        }

I have two notable variables: A voertuigenlijst(a list of voertuig) and a chauffeurlijst (a list of chauffeur) But the problem is that it could be that Voertuigenlijst's count is smaller than the count of Chauffeurlijst. It could also be the same. When the chauffeurlijst is smaller the program will ofcourse crash because the last item in the list of voertuigen already has been allocated. What I would like to do is when the voertuigenlijst[i] doesn't exist anymore I want to do i-1. Is there a nice solution for this problem?

Answer 1

You can do this by using another local variable to determine the index for Voertuigenlijst. Basically, you want to check that the index is within your range. If not, then use the last index in your list. For example --

for (int i = 0; i < Chauffeurlijst.Count; i++)
{
    var j = i < Voertuigenlijst.Count ? i : Voertuigenlijst.Count - 1;
    db.Insert("Insert into route (Voertuigen_ID,Chauffeurs_ID,Planning_ID) VALUES(@voertuigid,@chauffeurid,@planningid",
        new List<KeyValuePair<string, object>>
        {
            new KeyValuePair<string, object>("@voertuigid", Voertuigenlijst[j].ID),
            new KeyValuePair<string, object>("@chauffeurid", Chauffeurlijst[i].ID),
            new KeyValuePair<string, object>("@planningid", planning.ID)
        });
}

This will use the last Voertuig in the list if Chauffeurlijst.Count > Voertuigenlijst.Count. However, this code does not handle the case where Voertuigenlijst.Count > Chauffeurlijst.Count. I'm sure you can figure out a good solution using this example though. You may also want to handle the case where one is empty.