Trapping duplicate record exceptions

[John Dann]

Is there a specific way in ado.net of trapping an attempted (Access)
database update by da.update that would otherwise result in a record
with a duplicated primary key? The exception generated seems to be
OleDbException which isn't a very specific exception to trap, since it
could presumably be generated for other reasons than a duplicate key.

JGD

 

[Miha Markic [MVP C#]]

Hi John,

You should check out OleDbException ErrorCode property and Errors
collection - they'll tell you what is the problem. In case you are wondering
why there is a collection of errors - it is because there can be several
errors reported at once.

 

[John Dann]

Hi John,

You should check out OleDbException ErrorCode property and Errors
collection - they'll tell you what is the problem. In case you are wondering
why there is a collection of errors - it is because there can be several
errors reported at once.

Thanks, but it does sound like my only way of specifically catching
Duplicate error exceptions is to catch the condition:

exception.errocode = -2147467249

which seems a little inelegant albeit the only viable approach.

My other observation is that this error condition rejects the updating
of a database by a dataset irrespective of however many rows the
dataset may contain. So an attempt to update a database with a large
multi-row dataset containing just one potentially duplicate value
rejects the whole dataset, I guess the only workaround is to perform
any update just one row at a time. Or have I misunderstood how this
should work?

JGD

 

[Miha Markic [MVP C#]]

Hi John,

Thanks, but it does sound like my only way of specifically catching
Duplicate error exceptions is to catch the condition:

exception.errocode = -2147467249

Yes, the error is database specific.

which seems a little inelegant albeit the only viable approach.

My other observation is that this error condition rejects the updating
of a database by a dataset irrespective of however many rows the
dataset may contain. So an attempt to update a database with a large
multi-row dataset containing just one potentially duplicate value
rejects the whole dataset, I guess the only workaround is to perform
any update just one row at a time. Or have I misunderstood how this
should work?

Normally you want all records either to succeed or to fail, so you put the
Update into a transaction and there is no problem.
However, if you want to continue update even if there are errors, you should
implement RowUpdated event and set e.Status accordingle (perhaps to
UpdateStatus.SkipCurrentRow).