How to trap a Primary Key Constraint Violation

[Christiaan van Bergen]

Hi all,

I want to trap a Primary Key Constraint Violation. This is an exception of
the type System.Data.SqlClient.SqlException that is thrown whenever I insert
a record with an already existing key in the database.

Is there a way to trap this specific exception?
The only way I can come up with is to check the Exception.Message on the
presence of the text "Violation of PRIMARY KEY".

There must be a better way.
Please advice

Regards
Christiaan

 

[Guest]

Dear Christiaan van Bergen,

I want to trap a Primary Key Constraint Violation. This is an exception of
the type System.Data.SqlClient.SqlException that is thrown whenever I insert
a record with an already existing key in the database.

First of all do not try to insert a record with an already existing key in
the database.

Is there a way to trap this specific exception?

Yes, theare is a way.

The only way I can come up with is to check the Exception.Message on the
presence of the text "Violation of PRIMARY KEY".

Never try to check the Exception.Message on the presence of the text for
example, in your case "Violation of PRIMARY KEY".(Exception Numbers are
always better) Because sometimes, you may have similar kind of text in some
other Exception .

There must be a better way.

As per my knowledge goes, the better way is : before inserting the record,
try to select the record from the database with the condition like
WHERE id='123' (fetch the value from the control. for example
either
use may enter the value: In this case for example get the value from the
textbox
or
you may generate the number: in this case get that value.

your SQL Query looks like this:
===================
SELECT cust_id from Customer WHERE cust_id="' & txtCustID.text & '"

if you get some cust_id, then, already that key (or id) is existing in the
database. In this case, show a message to the user like "already that
customer existed in the database, please enter some other user"
Message something like above. You have to tailer-made to your requirements.
else, Insert into the database.

If record exists then
Messagebox.Show("Customer already exists, Please Enter Different
Customer")
else
INSERT INTO Table (field(s)) VALUES(value1,value2,.....);
end if

Please advice

This is my advice

For anything and everthing, you can get back to me

Thanks
Regards
Venkat_KL

 

[Christiaan van Bergen]

Thank you Venkat,

But unfortunatly you told me nothing new. Checking on the Exception.Message
is very error prone.
And checking the database before inserting is certainly a good way for
preventing this exception, but is terrible for performance.

I was hoping for a solution in the manner of
System.Data.DBConcurrencyException. So I can reject an insert if it was
already done.

The thing is, I need this trap for a kind of concurrency. A normal
concurrency check, checks if the diffgram has changed. But in case of an
insert there is no diffgram to check. So a DBConcurrencyException is not
thrown, but a System.Data.SqlClient.SqlException. However, the latter does
not provide access to the culporate row.


Christiaan