How do I prevent duplicate records in my database when updating records?
in my mysql table, i pretended like this
ControlNum|Title |NumOfEpis|Description|
001 |naruto |500 |some text |
002 |conan |700 |some text |
I have now created a custom control in C # where the user is allowed to update all columns except the main "ControlNum". so assuming each column has its own textboxes in the specified custom control, so to avoid duplication in the header column, for example if the user edits the row 2 header and puts it "Naruto" it will be duplicated as well. 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 worked, but my problem is that if the user only updates the number of episodes and descriptions and doesn't really intend to update the title, my method still detects that there is a duplicate, since my logic was "if (ctr == 1) ".. I think I am missing some method, or I am wrong here, so I hope someone enlightens me ... marry me here as a noob YY
source to share
If your application supports multiple users, you need to ensure that no changes are made by another user between your checking for duplicates and updating the database.
The easiest way to do this, as suggested by mbeckish, is to create a UNIQUE constraint on the header column:
ALTER TABLE maindatabase.animelist
ADD CONSTRAINT U_animelist_TitleAnime UNIQUE (TitleAnime)
The database engine will then apply unique headers and your client can process 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();
}
}
source to share
You don't want to have two headlines with the same content. This can be achieved automatically with the UNIQUE index in this column. However, if you don't want to add an index for this, you can change your query to
SELECT ControlNum FROM maindatabase.animelist
WHERE TitleAnime=?Title;
Then your logic should also check ControlNum for differences
int currentNum = int.Parse(a.ToString()));
while (reader.Read())
{
int ctrlNum = reader.GetInt32(0);
if(ctrlNum != currentNum)
ctr++;
}
if (ctr > 0)
......
This way you only increment the counter if the retrieved ControlNum is different from the one you selected in your interface.
Another approach is as follows (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 it can use the simpler and more efficient ExecuteScalar that only returns the first column of the first row (1 in this case) without creating a MySqlDataReader object
source to share