How to catch a duplicate key insertion? RRS feed

  • Question

  • User-1032202698 posted

    Hi! What I'm trying to do is this:

    catch ex as system.data.oledb.oledbexception 
    end try 
    problem: What have I to check to determine the duplicate key event? Which system.data.oledb.oledbexception member/property?
    Any idea? Thank you very much.
    Tuesday, September 16, 2008 10:31 AM

All replies

  • User-1666980993 posted

    I would strongly recommend against the pattern you are attempting.  Prevention of duplicate key insertion can and should be handled in your stored procedure.  If you are using SQL Server, test for the presence of the record using the EXISTS SQL statement:


    IF EXISTS (SELECT CustomerID FROM Customer WHERE CustomerID = 33)
       UPDATE Customer...
       INSERT INTO Customer (...)


    If you are using some other database, execute a scalar query against the database to test for the presence of the primary key value.  This will require two queries (one to test and another to insert/update) but it is a much better practice than relying on exceptions for your program flow.  Exceptions are to be avoided, never to be depended on for the execution of your business logic.

    Tuesday, September 16, 2008 11:08 AM
  • User-1032202698 posted

    Hi! Thank you for your answer.

    Yes, using SQL Server I can do it! But, what about with MS Access or MySQL or...?

    Any idea?

    I think I'm not so dummy, so I don't want to search the substring "duplicate key" into the string message reported by system.data.oledb.oledbException. Do you agree?

    It should be exists a smart way to verify duplicate key condition?




    Tuesday, September 16, 2008 1:42 PM