Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Trap Duplicate Key Error Message With A Custom Message

Posted on 2007-12-03
9
Medium Priority
?
842 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
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

Independent Software Vendors: 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!

Question has a verified solution.

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

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…
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses
Course of the Month12 days, 16 hours left to enroll

972 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