?
Solved

Trap Duplicate Key Error Message With A Custom Message

Posted on 2007-12-03
9
Medium Priority
?
837 Views
Last Modified: 2008-03-26
I have an application that has a form for submit data to my database.  I have a save button on the form that runs a simple insert statement to get the data to the db.  Sometimes the user will enter a duplicate id and get an unhandled exception error for the primary key duplicate.  I need a way to trap this error and display a message to the user something like "ID Is Already In The Database" with an ok button.
0
Comment
Question by:H-SC
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 55

Expert Comment

by:Jaime Olivares
ID: 20396489
could you post your code to look where to put it?
0
 
LVL 27

Expert Comment

by:VBRocks
ID: 20396565
Use the ErrorCode property of the OleDB.OleDBException class.

Here's an example:

        Dim cn As New OleDb.OleDbConnection()    'create connection
        Dim cmd As New OleDb.OleDbCommand()

        'This command inserts a duplicate record into a database
        With cmd
            .CommandText = "Insert into Table1 (Table1ID, Item) VALUES (1, 'Item');"
            .CommandType = CommandType.Text
            .Connection = cn
        End With


        Try
            cn.Open()
            cmd.ExecuteNonQuery()
        Catch ex As OleDb.OleDbException

            'Handle the error using the ErrorCode property:
            If ex.ErrorCode = -2147467259 Then
                MsgBox("Duplicate key exists")

            End If
        Finally
            cn.Close()
        End Try

0
 
LVL 1

Author Comment

by:H-SC
ID: 20396579
jaime_olivares,
no problem...here it is.  ID is the key

Private Sub save_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles save.Click
        Dim connect As New SqlClient.SqlConnection("Server=myserver;Database=mydatabase;Trusted_Connection=yes")
        connect.Open()
        Dim command As New SqlClient.SqlCommand("insert into mytable(id,description) values ('" & id.Text & "','" & desc.Text.Text & "')", connect)
        Command.ExecuteNonQuery()
        Command.dispose()
        connect.Close()
        connect = Nothing
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 27

Expert Comment

by:VBRocks
ID: 20396580
Also...  You can access that property with the SqlClient.SqlException class as well as OleDB.

0
 
LVL 27

Expert Comment

by:VBRocks
ID: 20396598
Do it like this:

        Dim connect As New SqlClient.SqlConnection _
            ("Server=myserver;Database=mydatabase;Trusted_Connection=yes")

       Try
             connect.Open()
             Dim command As New SqlClient.SqlCommand("insert into mytable(id,description) values ('" &  _
                  id.Text & "','" & desc.Text.Text & "')", connect)
             Command.ExecuteNonQuery()
             Command.dispose()

        Catch ex As SqlClient.SqlException

            'Handle the error using the ErrorCode property:
            If ex.ErrorCode = -2147467259 Then
                MsgBox("Duplicate key exists")

            End If
        Finally
                    connect.Close()
                    connect = Nothing
        End Try

0
 
LVL 27

Expert Comment

by:VBRocks
ID: 20396609
Oops, put the "Command.dispose()" line of code in the Finally block also.

0
 
LVL 1

Author Comment

by:H-SC
ID: 20396686
VBRocks,

When I hit the button it is not showing the message box.   When I put a check on the error code and put <> 1 then I see the message.  Could it be that the error code is a different one than -2147467259
0
 
LVL 27

Accepted Solution

by:
VBRocks earned 2000 total points
ID: 20396730
It could be.  Have it show you the message when it runs, like this:

        Dim connect As New SqlClient.SqlConnection _
            ("Server=myserver;Database=mydatabase;Trusted_Connection=yes")

       Try
             connect.Open()
             Dim command As New SqlClient.SqlCommand("insert into mytable(id,description) values ('" &  _
                  id.Text & "','" & desc.Text.Text & "')", connect)
             Command.ExecuteNonQuery()
             Command.dispose()

        Catch ex As SqlClient.SqlException

            MsgBox ex.ErrorCode

            'Handle the error using the ErrorCode property:
            'If ex.ErrorCode = -2147467259 Then
            '    MsgBox("Duplicate key exists")

            'End If
        Finally
                    connect.Close()
                    connect = Nothing
        End Try

0
 
LVL 1

Author Comment

by:H-SC
ID: 20396832
VBRocks,

That worked perfect.  It allowed me to see the error code being thrown (-2146232060) and then I just put that error cde into your example and worked great!!  Many Thanks
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This tutorial demonstrates one way to create an application that runs without any Forms but still has a GUI presence via an Icon in the System Tray. The magic lies in Inheriting from the ApplicationContext Class and passing that to Application.Ru…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

718 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question