how to resolve this sqlite exception constraint

Question

I am making a flight data app on Windows Store apps. The app will only track flights departing from & arriving in Pakistan.

I'm getting my data via JSON from

https://developer.flightstats.com/

and saving it to SQLite database.

This is my function that is getting the data

p.s: all the variables used in the connection string are declared and assigned but as they are irrelevant I'm not showing them.

public async void getdata()
{
    var dbpath = Path.Combine(Windows.Storage.ApplicationData.Current.LocalFolder.Path, "flight_record.sqlite");

    for (int hour = 0; hour < 24; hour++)
    {
        HttpClient web = new HttpClient();
        var response = await web.GetStringAsync("https://api.flightstats.com/flex/flightstatus/rest/v2/json/airport/status/KHI/arr/" + year + "/" + Month + "/" + today + "/" + hour + "?appId=" + appId + "&appKey=" + appKey + "&utc=false&numHours=1&maxFlights=10");

        jsondata1 data1 = JsonConvert.DeserializeObject<jsondata1>(response);

        for (int i = 0; i < data1.flightStatuses.Length; i++)
        {
            int f_ID = Convert.ToInt32(data1.flightStatuses[i].flightId);
            string l_time = data1.flightStatuses[i].arrivalDate.dateLocal.TimeOfDay.ToString();
            string year1 = data1.flightStatuses[i].arrivalDate.dateLocal.Year.ToString();
            string month1 = data1.flightStatuses[i].arrivalDate.dateLocal.Month.ToString();
            string day1 = data1.flightStatuses[i].arrivalDate.dateLocal.Day.ToString();
            string departureAirport1 = data1.flightStatuses[i].departureAirportFsCode.ToString();
            string arrivalAirport1 = data1.flightStatuses[i].arrivalAirportFsCode.ToString();
            string stats = data1.flightStatuses[i].status.ToString();
            string FsCode = data1.flightStatuses[i].carrierFsCode.ToString();
            string flightNumber1 = data1.flightStatuses[i].flightNumber.ToString();

            //here arival_data is my sqlite database table

            using (var db = new SQLite.SQLiteConnection(dbpath))
            {
                db.Execute("Insert into arrival_data (flightID, time, year, month, day, departureAirport, arrivalAirport, status, airline, carrierFsCode, flightNumber) values ('" + f_ID + "', '" + l_time + "', '" + year1 + "', '" + month1 + "', '" + day1 +
                    "', '" + departureAirport1 + "', '" + arrivalAirport1 + "', '" + stats + "', '" + airline1 +
                    "', '" + FsCode + "', '" + flightNumber1 + "')");
            }
        }
    }
}

My request query only supports 1 query per hour, so i have made a loop that will run 23 times.

every hour the number of flights are different so I've used data1.flightStatuses.Length in the second loop.

After that I'm inserting the data into the table.

When I run the app I get the following exception

I did debugging and instead of inserting data to db, I'm displaying it in the listbox and found that this is the result

If you look at the result, the time is not in the incrementing order. Hence I get the exception!

So how do I organize the data so the time is in incrementing order, or is there another way?

Answer 1

Several causes are possible, but the order in which you insert the data is not one of them.

Let's take a closer look at your sample data, especially the 1st & 4th row, they are duplicates! This makes me think a unique constraint is being violated.

First thing to look at would be the primary key on your table. Is it flightID?

You'll need to have a think about what you want to do with multiple rows for the same flight. You may want to look into a way of efficiently updating the data when it already exists or inserting if it does not, also known as UPSERT, see Q&A here.