locked
How to catch a duplicate key insertion? RRS feed

  • Question

  • User-1032202698 posted

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

    try 
    
       insertQuery 
    
    catch ex as system.data.oledb.oledbexception 
    
       updateQuery 
    
    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.
     
    fedrok
    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)
    BEGIN
       UPDATE Customer...
    END
    ELSE
       INSERT INTO Customer (...)
    END

     

    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?

    thx!

    fedrok

     

    Tuesday, September 16, 2008 1:42 PM