Check for Primary Key before Inserting New Record

Hi,

Can someone please tell me the best practices for checking the primary key field before inserting a record into my database?

As an example I have created an asp.net page using VB with an SQL server database. The web page will just insert two fields into a table (Name & Surname into the Names table). The primary key or the Names table is "Name". When I click the Submit button I would like to check to ensure there is not a duplicate primary key. If there is return a user friendly message i.e. A record already exisits, if there no duplicate, add the record.

I guess I could use try, catch within the .APSX page or would a stored procedure be better?

Thanks

Brett

one way you could do this is write a stored proc where you can check :

CREATE PROC ...

@.intResult INT OUTPUT

SET @.intResult = 0

IF NOT EXISTS (SELECT <col> FROM <table> WHERE <condition>

BEGIN

-- do the insert here

-- SET @.intResult to 1

END

Now in your application check for the value of intResult. If its 1 the INSERT was successful. If it was 0 the record already exists. You can take this further and also return any error messages.

|||

Thanks for the information.

Please can you let me know how can I check in my ASP.NET page the value of intResult?

Regards,

Brett

|||

ndinakar wrote:

Now in your application check for the value of intResult. If its 1 the INSERT was successful. If it was 0 the record already exists. You can take this further and also return any error messages.

Return Codes are not needed in languages supporting exceptions. Instead, throw an exception from your SP within SQL Server ...

IF EXISTS(SELECT * FROM <tb> WHERE <pk> = @.pk) BEGIN
RAISERROR('A Document with a number of %s already exists.', 16, 2, @.pk)
RETURN
END

In the ASP code, use a TRY/CATCH around the Execute method. If the error returned is a user defined error (50000), wrap the message in your own exception and send it directly back to the client.

|||

Thanks again for your help, could you please post me an example of how the code for the Try/Catch would look in ASP.NET using VB.
Regards,

Brett

|||check out the recent articles in my blog..I have some sample code that uses Try/Catch block's.
|||

I have read your article but I still don't understand how I can check the RAISERROR from the stored procedure. I then want to display an error to the user saying for example "Duplicate Name Found" if the RAISERROR occurs but if the record is added I would like a message saying "Record Added".

Are there any book you can recommend that deal with ASP.NET & SQL Stored Procedures.

|||I dont have sample code but am sure you;d find it if you google.