Solved

Trap Duplicate Key Error Message With A Custom Message

Posted on 2007-12-03
9
819 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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

760 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

22 Experts available now in Live!

Get 1:1 Help Now