StrataFrame Forum

Catching Specific Exception question...

http://forum.strataframe.net/Topic7410.aspx

By StarkMike - 3/12/2007

How would I catch a specific exception? For instance I have a table with a compound primary key... the primary key is not an auto increment but I dont want to allow duplicates to be inserted. When I try to insert a duplicate it throws an exception, naturally. I am able to trap the SQLException but I'm not sure how to catch that specific 'Violation of PRIMARY KEY constraint'...



Should I query the table before I attempt the insert instead of trying to catch the exception?
By Trent L. Taylor - 3/12/2007

Well, you cannot override the exception since the error is actually happening on the SQL Server itself and you will never be able to work around the constraint.  We generally test for the uniquness of a value using a scalar command within the CheckRulesOnCurrentRow event.

Public Function CheckForDuplicate(ByVal MyPassedTestValue As String) As Integer
Dim loCommand As New SQLCOmmand()
Dim lnPK As Integer

'-- Skip the current record
IF Me.Count > 0 Then
    lnPK = Me.PrimaryKeyField
Else
    lnPK = -1
End If

'-- Build the command text
loCommand.CommandText = "SELECT COUNT(*) FROM MyTable WHERE MyPrimaryKeyField != @MyPrimaryKeyField And MyTestField = @MyTestField"

'-- Create the parms
loCommand.Parameters.Add("@MyPrimaryKeyField",SqlDataType.Int)
loCommand.Parameters.Add("@MyTestField",SqlDataType.VarChar)

'-- Set the parms
loCommand.Parameters("@MyPrimaryKeyField").Value = lnPK
loCommand.Parameters("@MyTestField").Value = MyPassedTestValue

'-- Return the results
Return CType(Me.ExecuteScalar(loCommand), Integer)
End Sub