I want when I fill my database ,if a value is REPETITIOUS ,then appeared error

Question

hello.

I use this code till I see data in my database and edit,delet and insert data in that . but I want when I insert data to database , that is not REPETITIOUS , and if that is REPETITIOUS show me a message box about it.

Code :

private void btnok_Click(object sender, EventArgs e)
    {
        string id = idTextBox.Text.ToString();
        string name = nameTextBox.Text.ToString();
        string family = familyTextBox.Text.ToString();
        table1BindingSource.EndEdit();
        table1TableAdapter.Update(database1DataSet.Table1);

    }

    private void butins_Click(object sender, EventArgs e)
    {
        table1BindingSource.AddNew();
    }

    private void butdelete_Click(object sender, EventArgs e)
    {
        table1BindingSource.RemoveCurrent();
        table1TableAdapter.Update(database1DataSet.Table1);
    }

    private void butedit_Click(object sender, EventArgs e)
    {
        table1BindingSource.EndEdit();
        table1TableAdapter.Update(database1DataSet.Table1);
    }

I write this Query,but I dont know , how can I use that? I write that in "Database1DataSet.xsd" , and appeared fillby() and gatedataby() in Tableadapter.

query :

SELECT     id, name, family
FROM         Table1
WHERE     (id = @id) AND (name = @name) AND (family = @family)

Answer 1

Well, you could put a UNIQUE constraint spanning the interesting columns (presumably [id], [name] and [family]) in the database... that will prevent duplicates pretty well (by raising an exception that you can catch and message box appropriately).

The other approach is to check for existing values first - but unless you use something like serializable isolation-level you still have a race condition.

Answer 2

I'd suggest that you follow Marc's answer.

The exception you'll catch is a SqlException with error number 2601, it may be something like this:

catch(System.Data.SqlClient.SqlException lExSql)
{
    if(lExSql.Number == 2601)
        return Result.Duplicate;
    else
        return Result.Failure;
}
catch
{
    return Result.Failure;
}

Also a side note: there's no need to call TextBox.Text.ToString(), because the Text property is a String itself.