How do I catch this exception?

Question

System.Data.OleDbException (0x80004005): The changes you requested to the table were not successful because they would create duplicate values in the index, primary key..."

I know how to catch all exceptions. But I want to catch THIS SPECIFIC ONE so I can call one of my subroutines to generate a different ID that will not violate the primary key.

One TEMPORARY solution I've already applied was to query the database to find all existing distinct IDs, add them to a list, and then regenerate an ID that hasn't been used yet by checking if the list contains it already (via a do while loop). However, I don't want to create a data type that could possibly house thousands of IDs.

Thanks in advance!

---

-Nothing to see. Move along.

Answer 1

Try
  ' do your stuff here
Catch ex As Data.OleDbException
  ' Catch this specific exception
Catch ex As Exception
  ' Catch all other exceptions
End Try 

If this is not enough, you can also provide When clause:

When

Optional. A Catch statement with a When clause will only catch exceptions when expression evaluates to True. A When clause is only applied after checking the type of the exception, and expression may refer to the identifier representing the exception.

Try
   [ tryStatements ]
[ Catch [ exception [ As type ] ] [ When expression ] 
   [ catchStatements ] ]
[ Exit Try ]
...
[ Finally
   [ finallyStatements ] ]
End Try

Answer 2

Seems that you somehow change your primary key or create it away from the server.

Try to use a separated technical primary key and a logical key. You will see that all other methods fail sooner or later (even if your program is maybe 6 months running at the enduser).

The best column type for a technical key is in my idea the GUID, that does for instance not change at DataBase cleanups.

---

Success
Cor