I have a formview control in my page that allows users to insert records into my sql table (Estimators). The table consists of 3 fields (Estimator_Initials char(3) -Primary Key, Estimator nvarchar(50) and Active bit).
I need to validate that when a user inserts a record that their estimator_initials value is not a duplicate key. I found this article about creating a function to do this. I've used this template and created a function in SQL to match my table(not sure if it's right though as I have not written/used a function before).
The part I'm not sure about is where I need to call the function in my backend code of my formview and how to call it. Could someone please help me with this? I've included my SQL function below.
CREATE FUNCTION dbo.DetectOverlaps (@Estimator_Initials char(3), @estimator nvarchar(50), @Active bit)
RETURNS char AS
DECLARE @OverlapCount int
SELECT @OverlapCount = COUNT(*)
WHERE @Estimator_Initials IS NULL -- Don't compare against self
AND Estimator = estimator
AND Active = active
-- If @Estimator_Initials is null then we are doing an insert and we need to account for itself because the view will not see it yet
IF @Estimator_Initials IS NULL
SET @OverlapCount = @OverlapCount + 1