CODEWITHSUNDEEP
c#mysqlsql-updateunique
Raymart Calinao
Raymart Calinao

Reputation: 161

How do i prevent duplicate records in my database while updating records?

in my mysql table I made look like this one

ControlNum|Title  |NumOfEpis|Description|
001       |naruto |500      |some text  |
002       |conan  |700      |some text  |

now I made a user control in C# where in the user is allowed to update all columns except the primary"ControlNum". so assuming each column has its own textboxes in that said user control so in order to avoid duplication in the title column like for example if the user edit the row 2 title and put it "naruto" also it would be duplicated..so I create this method called checkData(); 

void checkData()
    {
        SuspendLayout();
        try
        {
            MySqlConnection conn = new MySqlConnection(myConnection);
            conn.Open();
            MySqlCommand command = new MySqlCommand("SELECT * FROM maindatabase.animelist where TitleAnime=?Title;", conn);
            //command.Parameters.AddWithValue("?CN", int.Parse(a.ToString()));
            command.Parameters.AddWithValue("?Title", textBox3.Text);
            MySqlDataReader reader = command.ExecuteReader();

            int ctr = 0;
            while (reader.Read())
            {
                ctr++;

            }
            if (ctr == 1)
            {
                my = Form.ActiveForm as MyList;
                my.msg = new Message_Box();
                my.msg.Descrip.Text = "Record is already in the Database";
                my.msg.Title.Text = "Duplicate Record";
                my.msg.ShowDialog();
            }
            else
            {

                updateData();
            }


            conn.Close();
            ResumeLayout();
        }
        catch (Exception ex)
        {
            MessageBox.Show("" + ex);
        }
    }


it was working but my problem is what if the user only update the number of epis and descrip and doesn't really intend to update the title,my method still detect that there's a duplicate since my logic was "if(ctr == 1)"..I think I'm missing some method or I'm in a wrong approach here..so I hope someone will enlighten me..sorry for being noob here Y.Y

Upvotes: 3

Views: 8351

Answers (3)

Dave Manning
Dave Manning

Reputation: 820

If your application supports multiple users you need to ensure changes are not made by another user between your check for duplicates and the database update.

The easiest way to do this is as mbeckish suggested, create a UNIQUE constraint on the title column:

ALTER TABLE maindatabase.animelist 
ADD CONSTRAINT U_animelist_TitleAnime UNIQUE (TitleAnime)

The database engine will then enforce unique titles and your client can handle user feedback by catching any constraint violation exception:

void checkData()
{
    SuspendLayout();
    try
    {

        updateData();

    }
    catch (Exception ex)
    {
        MySqlException sqlEx = ex as MySqlExecption;
        // If there is a constraint violation error.
        // (I may have the wrong error number, please test.)
        if (sqlEx != null && sqlEx.Number == 1062) 
        {
            my = Form.ActiveForm as MyList;
            my.msg = new Message_Box();
            my.msg.Descrip.Text = "Record is already in the Database";
            my.msg.Title.Text = "Duplicate Record";
            my.msg.ShowDialog();
        } 
        else 
        {
            MessageBox.Show("" + ex);
        }
    }
    finally
    {
        ResumeLayout();
    }
}

Upvotes: 2

Steve
Steve

Reputation: 216293

You don't want to have two Title with the same content. This could be achieved automatically with an UNIQUE index on that column. However, if you don't want to add an index for this then you could change your query to 

SELECT ControlNum FROM maindatabase.animelist 
WHERE TitleAnime=?Title;

Then your logic should check also the ControlNum for differences

int currentNum = int.Parse(a.ToString()));
while (reader.Read())
{
    int ctrlNum = reader.GetInt32(0);
    if(ctrlNum != currentNum)
        ctr++;

}
if (ctr > 0)
    ......

In this way you increment your counter only if the ControlNum retrieved is different from the one you have selected in your interface

Another approach is the following (see comment below from @mbeckish)

SELECT 1 FROM maindatabase.animelist 
WHERE TitleAnime=?Title AND ControlNum <> ?CN

command.Parameters.AddWithValue("?CN", int.Parse(a.ToString()));
command.Parameters.AddWithValue("?Title", textBox3.Text);
object result = command.ExecuteScalar();
if(result != null)
{
    // Found a record with different ControlNum but with the same title
    // Error here
}

This is probably preferable to the first one because you don't need a loop and could use a simpler and more performant ExecuteScalar that returns only the first column of the first row (the 1 in this case) without building the MySqlDataReader object

Upvotes: 2

Pratap Das
Pratap Das

Reputation: 574

Change the duplicate finding SQL :

SELECT MAX(Title) as Count
FROM maindatabase.animelist
GROUP BY Title  , NumOfEpis, Description

if Count > 1 , then there is a duplicate.

Upvotes: 0

Related Questions