How to prevent duplicate records in Access database using C #?

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

[Index=Yes(no duplicates)]

      

        
        
        
      

    

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

I'm working with an older version of Access, so your mileage may vary, but to show your own error message, you need to first query for the value in the table like this:

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

      

        
        
        
      

    

Then go to your code if broken> 0 to display a message box.

Just add to what @ Gary.Ray suggested: if there are multiple candidate keys in your table ("If this field is not your primary key ...") then the error message that ACE / Jet engine returns is useless , don't tell me which key was broken:

The changes you made to the table were not successful because they create duplicate values ​​in the index, primary key, or relationship. + Modify data in a field or fields that contain duplicate data, delete the index, or override the index to allow duplicate records and try again.

Fortunately, there are several ways to implement the key, and using a constraint CHECK

has the advantage of returning the constraint name in the error message. For example, this constraint CHECK

will duplicate the "MyField" column:

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 does not return "MyField" but satisfies other keys in the table, the error message will be:

One or more values ​​are prohibited from the MyTable__MyField__no_dups_allowed

'set for' validation rule 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 known constraint names to provide a more meaningful message.