Trap Duplicate Key Error Message With A Custom Message

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.
LVL 1
H-SCAsked:
Who is Participating?
 
VBRocksConnect With a Mentor Commented:
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
 
Jaime OlivaresSoftware ArchitectCommented:
could you post your code to look where to put it?
0
 
VBRocksCommented:
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
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
H-SCAuthor Commented:
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
 
VBRocksCommented:
Also...  You can access that property with the SqlClient.SqlException class as well as OleDB.

0
 
VBRocksCommented:
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
 
VBRocksCommented:
Oops, put the "Command.dispose()" line of code in the Finally block also.

0
 
H-SCAuthor Commented:
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
 
H-SCAuthor Commented:
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
All Courses

From novice to tech pro — start learning today.