Expert: robert mamayev - 11/9/2009

Question
Hello,
 I have a doubt. I have designed a front end to enter book details. Back end is SQL server. Here i have made Accession number as Primary key. User can enter all book details using this front end. I want to know if user repeats the Accession number which is a primary key how to give a error message to the user.

for example :
 If user had already entered accession number as 1000
and if the user repeats the same accession number, then primary key violation is done. How to give an error message to user.

Answer
First of all, if possible, change your design!! Make a new column a primary key and use an auto-increment to populate its content from within SQL Server. This is your so-called surrogate key. And create another column to store values that users specify (like SKU). Letting users specify primary key is a bad idea, in general

Now, if you cannot change your design, trap error inside stored procedure. As soon as SQL Server troughs a key/constraint violation, trap it inside exception block and return it inside your cursor back to front end (your application). If this value inside cursor is NULL then all is fine; if the value is not NULL then you have an error and display it to the user. Here you have to make a call to SQL Server just to find out if error occurred.  With the surrogate key approach (the one I described in the beginning) you are safe.

Regards,
Robert