Inserting duplicate Values into a Primary Key Column

[elmo29]

I have a table with a Primary Key.

I am using Visual Basic as the fornt end, if a duplicate value goes into the primary key column an error occurs (obviously .....). I have put the insert into a transaction and if an @@error occurs it has a return value of 1 And the transaction rolls back. But this doesn't catch the error, I am given a violation error before it even gets to that stage!

So I am thinking, ok the @@error doesn't recognise an idiot putting a duplicate value in, so what does? How *are* you supposed to stop users typing in a duplicate values?

Hope you can help!

 

[crystalized]

I have 2 possible suggestions, I don't know if either would serve your purposes, but they are what I thought of.

Generate the key yourself, without allowing the user to enter the key value at all.

Or

Test for a duplicate of the key in the table before attempting to insert the new record into the table.

I do not know what type of situation you are working with so I don't know if either of these suggestions will be of much help.