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

Question

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

Answer 1

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();
    }
}