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?

I doubt that the order in which you're trying to insert the rows is causing the constraint exception - it's probably something else. Can you post the SQL definition for the arrival_data table schema? Also, I strongly strongly strongly recommend that you use SqlParameters instead of just concatenating your data directly into the SQL string - this is a huge security vulnerability to SQL injection attacks. — Joe Irby, Apr 07 '17 at 20:58

score 0 · Accepted Answer · edited May 23 '17 at 12:17

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.

how to resolve this sqlite exception constraint

1 Answers1