Using a SQL function in Visual Studio to determine duplicate key insert

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  
BEGIN
      DECLARE @OverlapCount int

      SELECT @OverlapCount = COUNT(*)
      FROM dbo.Estimators
      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

      RETURN @OverlapCount
END

Open in new window

imstac73Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Guy Hengel [angelIII / a3]Billing EngineerCommented:
presuming you know how to run sql code in general from your code, here some sql code:
select dbo.DetectOverlaps ('A3X', 'esimator_key', 1)

Open in new window

0
mrichmonCommented:
Actually a smart way to do this would be to use a trigger to detect overlaps and prevent it using a constraint in the database
0
imstac73Author Commented:
Mrichmon:
Could you elaborate on this please?  In the related article you had originally said to create a function and then add a check constraint using the function.  Is this not the best way to do it?  Is this all happens in SQL, do I use a Try...Catch in my code behind?  If so, in what event?
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

mrichmonCommented:
It really depends on what you want to do, where, and how.

Your scenario is different it appears.  In the other post they wanted to ensure that the room was not already reserved.  The issue was overlapping time ranges not duplicate entries.  

I just re-read what you described and you appear to want to prevent duplicate initials - basically prevent a duplicate field value.

If that is really all you want then the best way is actually to create a unique constraint on the field in the table.  Then it simply cannot be duplicated no matter what - no extra checking needed.

And actually you don't even need to do that since by making that column a primary key it will automatically enforce a unique constraint on it.

So your users CANNOT insert a duplicate value - your database will not allow it based on your primary key setup.
0
imstac73Author Commented:
So, to notify the user that they cannot insert the record would I do a Try Catch in my code behind for my formview?  
0
mrichmonCommented:
Yep.  And you should be able to catch the specific primary key violation exception - but I can't tell you teh exact code because it depends on your server, the name of the primary key, etc.

The best way to figure out what to catch is to start with:

catch (Exception ex)
{
      // write out all exception properties here - especially type
}

then once you know the type you can change to something like
catch (SqlException ex)
{
     // it will have other properties
}

and you repeat until you find out the exact specifics of what to catch.

Probably if you are using SQL Server you will end up with testing the exception Number property to ensure it is 2627.

You could do something like this:

catch(SqlException ex)
{
      if(ex.Number == 2627 && ex.Message.Contains("PRIMARY KEY"))
      {
            // handle it with message to user
      }
      else
      {
            throw ex;
      }
}

or many other options...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
mrichmonCommented:
You could also look for the exact primary key message thrown by that primary key name - but you would have to see what  your database server throws to set that up...
0
imstac73Author Commented:
This is what I added to my code.  When I enter a duplicate value and then click Insert on my formview it just sits there.  Not sure what I'm doing wrong.
 

Protected Sub fvEstimators_ItemInserted(ByVal sender As System.Object, ByVal e As System.Web.UI.WebControls.FormViewInsertedEventArgs) Handles fvEstimators.ItemInserted
        Try
            fvEstimators.InsertItem(True)


        Catch ex As Exception


            If (ex.Message.Contains("PRIMARY KEY")) Then


                Response.Write("Estimator Initials entered have already been used; please use a different letter combination")

            Else : Throw ex

            End If


        End Try

Open in new window

0
mrichmonCommented:
You will have to see what is happening in fvEstimators.InsertItem(True)

It is possible it is being caught there.  Also check the database and see if the value was not inserted...
0
imstac73Author Commented:
I finally got it working after looking at another post.  Here is what I used in case anyone else looks this up.  
 

Protected Sub fvEstimators_ItemInserted(ByVal sender As System.Object, ByVal e As System.Web.UI.WebControls.FormViewInsertedEventArgs) Handles fvEstimators.ItemInserted
        ' Use the Exception property to determine whether an exception
        ' occurred during the insert operation.
        If e.Exception Is Nothing Then

            ' Use the AffectedRows property to determine whether the
            ' record was inserted. Sometimes an error might occur that 
            ' does not raise an exception, but prevents the insert
            ' operation from completing.
            If e.AffectedRows <> 1 Then

                MsgBox("An error occurred during the insert operation.", vbExclamation, "Error")

                ' Use the KeepInInsertMode property to remain in insert mode
                ' when an error occurs during the insert operation.
                e.KeepInInsertMode = True
            Else
                fvEstimators.Visible = False
                gvEstimators.Visible = True
                gvEstimators.DataBind()

            End If

        Else

            ' Insert the code to handle the exception.
            MsgBox("Estimator Initials entered have already been used; please use a different letter combination", vbExclamation, "Error")

            ' Use the ExceptionHandled property to indicate that the 
            ' exception has already been handled.
            e.ExceptionHandled = True
            e.KeepInInsertMode = True

        End If

        

    End Sub

Open in new window

0
imstac73Author Commented:
Had to find additional help from other article.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.