SqlCommand "INSERT INTO" Duplicate Primary Key... Exception Thrown? 1

[BoulderBum]

I'm thinking of how I'm going to design part of a little project of mine.

The part in question is a registration page where the client selects a user name, which is then inserted (with other info) into a table (on SQL Server). Of course, I don't want duplicate user names (which are primary keys) and was hoping to handle duplicate key values via exception handling.

Is there an exception thrown under such circumstances? If so, what is its name? If I use such an approach, will the whole duplicate data insert be aborted/ will the original non-duplicate fields be in danger of being corrupted? I don't know enough about the behavior of such inserts to know if the solution I propose is viable.

The only other thing I can think of is to have a seperate query determine if a user name already exists, but that seems less efficient. Is there a better way not mentioned in my post?

 

[hotwu]

i think the best way, and one of the fewest methods used by programmers these days is to let the database handle database activity. pass your information into a stored proc and have the stored proc determine whether it is a viable option or not... have the stored proc return a value to your application letting it know whether it was successful or not (if it was you could return the new id). this will allow you to make only one call to the database and keep your application from actually throwing an error to begin with.

joel konecny
global110.com

 

[BoulderBum]

Thanks, wu.

I figured I would try to make one query (INSERT) instead of two (SELECT then INSERT) if possible in order to minimize database workload. In lieu of an exception, is there a value returned from SQL Server to indicate an unsucessful INSERT?

 

[hotwu]

if an error occurs in sql, it will be raised to your application.

my advice to you is to let a single stored proc handle the verification, you may find that letting the database handle the error may be less efficient then letting the database verify a good value before hand.

i think there are definitely times when a coder should check an error being raised as a valid form of verification, but i think in this instance it is just not the best decision ~ but it is not mine to make . i honestly do hope this helps.

joel konecny
global110.com

 

[BoulderBum]

I'll go with your suggestion. Thanks again, Joel.