How to prevent duplicate entries in an Access database using c#?

Question

i have a table of MS Access which contains one column and many rows. The value of a textbox saved to this table. I want to prevent duplicate entries from being submitted. For example, if I type "ankush" in the textbox and this entry already exists in table, then I want to display a msgbox saying that this already exists in the table. using c#

Answer 1

You can create a unique key on the column.

Answer 2

If the field is not your primary key filed you can set an index on the field that contains 'ankush' and make that index unique

[Index=Yes(no duplicates)]

then the Jet DB Engine will not allow the insert and will show a default message.

I am working with an older version of Access, so your mileage may vary, but to show a custom error message you would have to first do a query for the value in the table like this:

SELECT COUNT(MyField)as violated FROM MyTable WHERE MyField = 'input value here'

Then branch in your code if violated > 0 to show your message box.

Answer 3

The focus of the question has completely changed, rendering the answer obsolete.

Answer 4

Just to add to what @Gary.Ray has suggested: if your table has multiple candidate keys ("If the field is not your primary key...") then the failure message the ACE/Jet engine returns unhelpfully does not tell you which key was violated:

The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again.

Happily, there is more than one way to implement a key and using a CHECK constraint has the advantage of returning the name of the constraint in the failure message. For example, this CHECK constraint will duplicates for the column 'MyField':

ALTER TABLE MyTable ADD
   CONSTRAINT MyTable__MyField__no_dups_allowed 
      CHECK (NOT EXISTS (
                         SELECT T1.MyField 
                           FROM MyTable AS T1
                          GROUP
                             BY T1.MyField 
                         HAVING COUNT(*) > 1
                        ));

For data that fail the 'MyField' but would satisfy the other keys in the table, the failure message would be:

One or more values are prohibited by the validation rule 'MyTable__MyField__no_dups_allowed' set for 'MyTable'. Enter a value that the expression for this field can accept.

You can then catch the error in your C# front end and parse the error message for the known constraint names to provide a more meaningful message.