Checking for duplicate row before inserting

I need to know how to check/compareall fields in a row based on Cust Id before inserting a new record. As it is right now, if the Add/Update button is pressed continually, it increments the record number and adds a duplicate record. And you know you'll never stop people from double clicking a button that only needs to be clicked once.

I thought about just disabling the Update button after the insert occurs, then re-enable it if they go back and edit a field, but that's not a solid solution.I'd do it in your stored procedure.

You've got something like:

CREATE PROCEDURE dbo.spInsertRec
 (
 @.OneVal INT,
 @.OtherVal VARCHAR(50)
 )
AS
INSERT CustTable (SomeField, OtherField) VALUES (@.OneVal, @.OtherVal)
GO

I'd change it to something like:

CREATE PROCEDURE dbo.spInsertRec
 (
 @.OneVal INT,
 @.OtherVal VARCHAR(50)
 )
AS
IF EXISTS
 (SELECT CustID FROM CustTable
 WHERE SomeField = @.OneVal AND OtherField = @.OtherVal)
 BEGIN
 RETURN
 END
ELSE
 BEGIN
 INSERT CustTable (SomeField, OtherField) VALUES (@.OneVal, @.OtherVal)
 END
GO

That's pretty simple, obviously...You'll likely want to return something if a match is found, or even if the new record gets inserted...But it shows you the basics, I hope.

Regards,

Xander|||That looks like what I'm looking for. Since the add buttons click event runs 5 functions to insert data into other tables; if I call the SP in the first function, that should intercept the insertion process all together and break right?

InsertReportData()Would calling the SP here break and prevent the other functions from firing?
InsertCustInterest()
InsertCustVisits()
InsertCustQuotes()
InsertHistory()

I suppose that I could code a boolean to catch if the SP returns a duplicate using True/False, then use it to display a message in my errmsg label; notifying the user that they have attempted to store a duplicate record.

Thanks for the help, any other info would be greatly appreciated.
PD|||InsertReportData()Would calling the SP here break and prevent the other functions from firing?
InsertCustInterest()
InsertCustVisits()
InsertCustQuotes()
InsertHistory()

No, the fact that a record wasn't inserted will not, in and of itself prevent the other functions from firing.

You've got the idea though...If you pass a boolean value back, you can use it to decide whether to fire the other functions or not.

You could pass an output parameter from the stored procedure, for instance, or return a scalar value that lets you know what the stored procedure did, and wrap your four subsequent functions in an IF block, (perhaps even inside the first function on successful insertion?) and that should give you what you want.|||I got it to work, but it does prevent the updating of the fields that are not part of the SP in one of my functions (Strange).

I am going to check it out. I thought about appending the table to store all the field data and re-code the SP to check all the fields, fire it first, and then let it yield to the other functions if no duplicate exists.

Thanks.|||Well, it has turned into a proverbial nightmare. If I yield to the other insert functions from my SP, then duplicate records can be created in those tables. So I guess I am going to have to write SP's for each of those functions to prevent duplicates there as well.

I inherited this app a couple of months ago from an outside firm, and what a mess it has been.