Capture Primary Key Violation Error

Hello,,

I need to capture the primary key violation error:

If e.CommandName = "Insert" Then
Dim EmployeeIDTextBox As TextBox = CType(dvContact.FindControl("EmployeeIDTextBox"), TextBox)
Dim LastName As TextBox = CType(dvContact.FindControl("LastName"), TextBox)
Dim FirstName As TextBox = CType(dvContact.FindControl("FirstName"), TextBox)

Using cmdAdd As New System.Data.SqlClient.SqlCommand

'Establish connection to the database connection
Dim sqlcon As New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("eConnString").ToString)

'Open connection
sqlcon.Open()

'Pass opened connection (see above) to the command object
cmdAdd.Connection = sqlcon

'Using "With/End With" pass content to columns from text objects and datatime variables (see above)
With cmdAdd
.Parameters.Add(New SqlClient.SqlParameter("@.EmployeeID", EmployeeIDTextBox.Text))
.Parameters.Add(New SqlClient.SqlParameter("@.LastName", LastName.Text))
.Parameters.Add(New SqlClient.SqlParameter("@.FirstName", FirstName.Text))

'Establish the type of commandy object
.CommandType = CommandType.Text

'Pass the Update nonquery statement to the commandText object previously instantiated
.CommandText = "INSERT INTO ATTEmployee(EmployeeID, LastName, FirstName & _
"VALUES (@.EmployeeID, @.LastName, @.FirstName)"
End With

'Execute the nonquerry via the command object
cmdAdd.ExecuteNonQuery() '<==Need to capture primaryKey violation, give user message, cancel insert,return to detailView ReadOnly

'I haven't figured out the correct code to capture the primary key violation

EDITMsg.Text="You can not insert an duplicate record. Try Again."

'Close the sql connection
sqlcon.Close()
End Using
End If

Thank you for your help

Just wrap the ExecuteNonQuery call in a try block, then catch the SqlException. that object has a lot of information about all the errors that come from SQL Server.

Make sense?

Don

|||

No,

I tried to write Try,,Catch,,End Try, but it did not work. I got errors with the using/end using.

|||

Oh! You said you wanted to trap errors on the ExecuteNonQuery. So let's take this one thing at a time and look at using.

First, there really is no reason to use using with a SqlCommand object, since it is a managed resource, although it uses a connection. Better to use it with the SqlConnection object, which definitely needs closing when you're done with it.

That said, what errors do you get with the using? But it doesn't really matter at this point.

Don

|||

I do need to trap the error on the ExecuteNonquery.

Let me start again, I need to capture the Primary Key Violation Error, write a message, i.e. ErrorMsg.Text = "You have an error...".

|||

Okay, then something like this should do it:

Try
cmdAdd.ExecuteNonQuery()
Catch SqlException as sqlEx
' Do something here
Catch 'other exeptions if you need to
End Try

What you do with the exception depends on what you want to present to the UI. You'll normally want to use the exception object's Errors property to get a collection of SqlError objects with the details of what SQL Server sent about the error, and there may be more than one error. You can handle the primary key exception this way.

You also need to manage the connection to the database. It remains open if the error severity is 19 or less.

Does this make sense? Is this what you tried and said didn't work?

Don

|||

I tried the "Try-Catch-End Try" and it is not working nor has not worked for me..I shows are error when there is a duplicate record and when there I try to insert a new record.

My snipet:
<asp:DetailsViewID="dvContact"DataKeyNames="EmployeeID"DataSourceID="sqlEmployeeByID"Height="346px"Width="385px" OnItemInserted="Display_Insert_Msg"
<InsertItemTemplate>
<asp:ButtonID="Button8"CommandName="Insert"Text="Insert"runat="server"Font-Size="10pt"Width="50px"/>
<asp:ButtonID="Button9"CommandName="Cancel"Text="Cancel"runat="server"Font-Size="10pt"Width="50px"/>
</InsertItemTemplate>

Along with the above I have:

Protected Sub Display_Insert_Msg(ByVal sender As Object, ByVal e As DetailsViewInsertedEventArgs)

If e.Exception IsNot Nothing Then
'Error in the new inserted data value.
'Display error message.
' ErrorMessage.Visible = True
EditMSG.Text = "• Duplicate Employee ID Numbers not allowed." <<=== This line writes to the browser when there is a new record and a duplicate record.
e.ExceptionHandled = True
e.KeepInInsertMode = True
Else
'Update the FormView control display
'to reflect a product row insertion.
EditMSG.Text = String.Empty
dvContact.DataBind()
grdEmployees.DataBind()
End If

End Sub

Now, with the Try-Catch-End

Try
'Execute the nonquerry via the command object
cmdAddEmployee.ExecuteNonQuery()
Catch Sqlex As System.Exception
lblErrorMsg.Text = Sqlex.ToString '& ex.Number
End Try