Solved

Trap Duplicate Key Error Message With A Custom Message

Posted on 2007-12-03
9
820 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
  • 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
 
LVL 27

Expert Comment

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

0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
This Micro Tutorial will teach you how to censor certain areas of your screen. The example in this video will show a little boy's face being blurred. This will be demonstrated using Adobe Premiere Pro CS6.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

910 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now