[RESOLVED] Prevent getting duplicate records

[nikel]

Hello, I have a database and if I call GetData() I'm getting duplicate records on DataGridView. Here's my code:

private void GetData()
{
    // Retrieve the data.
 
    sqliteDataAdapter.Fill(dataTable);
 
    // The table can be used here to display and edit the data.
    // That will most likely involve data-binding but that is not a data access issue.
}
 
private void SaveData()
{
    // Save the data.
    sqliteDataAdapter.Update(dataTable);
}

If I add the code below to all places where I declare datatable (and set id column to unique), I'm getting many errors:

try
{
    sqliteDataAdapter.Fill(dataTable);
}
catch (ConstraintException)
{
    DataRow[] rowErrors = dataTable.GetErrors();
 
    System.Diagnostics.Debug.WriteLine("YourDataTable Errors:"
        + rowErrors.Length);
 
    for (int i = 0; i < rowErrors.Length; i++)
    {
        System.Diagnostics.Debug.WriteLine(rowErrors[i].RowError);
 
        foreach (DataColumn col in rowErrors[i].GetColumnsInError())
        {
            System.Diagnostics.Debug.WriteLine(col.ColumnName
                + ":" + rowErrors[i].GetColumnError(col));
        }
    }
}

[techgnome]

The problem isn't the code... the problem is the data... there are different ways to get duplicate data. One is the table in the database itself has the data duplicated. The other is the result of a query.

The first is easy enough to explain... it means the data is duplicated in the table. If your IDs are set "correctly" then you'd be getting different IDs on the rows... doesn't sound like that's the issue, so I'm going to guess you've got two tables you're selecting data from and you've got a join condition that ties them together. The problem then is that for each row you have in one table, you've got multiple related rows in the second table resulting in "duplicate" data.

Consider this:
table: People
-------
ID, Name, Address

Table: Cars
-------
ID, Make, Model, Color

Table: CarsPeopleOwn
-------
ID, PeopleID, CarID


So the People table is full of, well, People... and the Cars is full of Cars and CarsPeopleOwn links the two... now people can own more than one... I own two... so I have one record in the People table, two records in the CarsPeopleOwn table... and since the cars I own are different makes and models, there's multiple rows in there...

If I'm the only one in the table and I select from P:eople, I get one record... but as soon as I join to the CarsPeopleOwn table, I get two records... resulting in "duplicate" data.

Hopefully all this makes sense.

Long story short, trying to fix it in the code is too late...the horse has already left the barn. What you need to be looking at is the data and the SQL that created the datatable you're trying to fill... the solution lies there.

-tg

[nikel]

thank you for your reply.

[IkkeEnGij]

Hopefully all this makes sense.

it makes sense...in a way
but only if that design is implemented in a wrong way
if it is implemented as having a 1 to 1 relationship between Cars and CarsPeopleOwn
then there can not be duplicate records in CarsPeopleOwn

a nice idea would be to make CarID the primary key of CarsPeopleOwn
(and that would have a very interesting side effect)

of cource that is assuming that a car can only be owned by 1 person
(what, according to my spouse is pure nonsence)

[techgnome]

it makes sense...in a way
but only if that design is implemented in a wrong way
if it is implemented as having a 1 to 1 relationship between Cars and CarsPeopleOwn
then there can not be duplicate records in CarsPeopleOwn

a nice idea would be to make CarID the primary key of CarsPeopleOwn
(and that would have a very interesting side effect)

of cource that is assuming that a car can only be owned by 1 person
(what, according to my spouse is pure nonsence)

It depends on what other data you attach to it and where... I have a Ford F150 ... so do a million other people... In the example I was laying out, the Cars table is the generic Ford F150... the same one that a million people own... so Cars and CarsPeopleOwn would need a one to many relationship. At least that's how I saw it in my head. Sounds like you're thinking the Cars table was the actual instance of each million cars out there. And that's what makes this so much fun. :P If that were the case, then I would have just put PeopleID directly on the Cars table and not bothered with the middle lookup.
But it's all moot. The point I was trying to demonstrate is how it's possible to get duplicate data when it's not really duplicate. It just LOOKs like duplicate data. The reality is if I were designing a set of tables to track cars and ownership, there would be a mass amount of tables... as there would be all kinds of things to track: make, model, colors, types, lic #, & the vehicle ID would be the VIN

-tg

[nikel]

These duplicates occur one time. If I restart my program it doesn't have any duplicates, I get them when I save new record. Another strange thing is a few records aren't duplicated. Can anyone help?

[nikel]

Here's my new record function:

private void addNew_tsmi_Click(object sender, EventArgs e)
{
    info_lbl.Text = "yeni program ekleniyor";
 
    List<string> list = new List<string>();
    int number;
 
    using (InsertData insertData = new InsertData())
    {
        insertData.ShowDialog();
        list = insertData.GetValues();
    }
 
    m_dbConnection.Open();
    sqliteDataAdapter = new SQLiteDataAdapter();
    SQLiteCommand insert = new SQLiteCommand(
                            "INSERT INTO tbl_programs " +
                            "(int_isSelected, txt_programName, int_is86, int_is64, " +
                            "txt_preCommand, txt_preSwitch, txt_executable, " +
                            "txt_switches, int_timeout, txt_md5, txt_version, " +
                            "txt_description, txt_category, dt_insertDate) " +
                            "VALUES(" +
                            ":isSelected, :programName, :is86, :is64, :preCommand, " +
                            ":preSwitch, :executable, :switches, :timeout, " +
                            ":md5, :version, :description, :category, :insertDate);",
                            m_dbConnection);
    insert.Parameters.AddWithValue("isSelected", 0);
    insert.Parameters.AddWithValue("programName", list[0]);
    insert.Parameters.AddWithValue("is86", 1);
    insert.Parameters.AddWithValue("is64", 1);
    insert.Parameters.AddWithValue("preCommand", list[3]);
    insert.Parameters.AddWithValue("preSwitch", list[4]);
    insert.Parameters.AddWithValue("executable", list[5]);
    insert.Parameters.AddWithValue("switches", list[6]);
    insert.Parameters.AddWithValue("timeout", 30);
    insert.Parameters.AddWithValue("md5", list[8]);
    insert.Parameters.AddWithValue("version", list[9]);
    insert.Parameters.AddWithValue("description", list[10]);
    insert.Parameters.AddWithValue("category", list[11]);
    insert.Parameters.AddWithValue("insertDate", Convert.ToDateTime(list[12]));
    sqliteDataAdapter.InsertCommand = insert;
    sqliteDataAdapter.InsertCommand.ExecuteNonQuery();
    sqliteDataAdapter.SelectCommand = new SQLiteCommand(
                            "SELECT DISTINCT " +
                            "ID, " +
                            "int_isSelected, txt_programName, int_is86, int_is64, " +
                            "txt_preCommand, txt_preSwitch, txt_executable, " +
                            "txt_switches, int_timeout, txt_md5, txt_version, " +
                            "txt_description, txt_category, dt_insertDate " +
                            "FROM tbl_programs WHERE ID <> NULL;",
                            m_dbConnection);
    sqliteDataAdapter.SelectCommand.ExecuteScalar();
    m_dbConnection.Close();
    programs_dgv.Sort(programs_dgv.Columns["txt_programName"], ListSortDirection.Ascending);
    GetData();
 
    info_lbl.Text = "hazır";
}

[IkkeEnGij]

Sounds like you're thinking the Cars table was the actual instance of each million cars out there. And that's what makes this so much fun. :P If that were the case, then I would have just put PeopleID directly on the Cars table and not bothered with the middle lookup.

congrats, so you did pay attention when i said:

a nice idea would be to make CarID the primary key of CarsPeopleOwn
(and that would have a very interesting side effect)

I have a Ford F150 ... so do a million other people... In the example I was laying out, the Cars table is the generic Ford F150

that is in compleet contradiction with your Cars table
In the example you were laying out, in the Cars table was the generic Ford F150
it would just have 2 fields: a make and a model: 'Ford' and 'F150'

so you own 3 fords F150
2 yellow and a red
how are you going to put in CarsPeopleOwn that you own 3 fords F150 ? (without having duplicate data ?)

i know, that is not duplicate data, it just seems so, right ?

to sum up:
to prove a point (and it was a just point, that you were right to make)
you made up a completely contrived example
and all i did was turn that completely contrived example
in a workable system

[nikel]

bump

[nikel]

OK I fixed it. When the program first loaded, there was a command builder and I got rid of it. Instead I used selectcommand. Thanks for the helps.