How to catch specific OleDbException?

Question

I need to catch the following specific exception:

System.Data.OleDb.OleDbException was caught ErrorCode=-2147467259
Message="The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again." Source="Microsoft JET Database Engine" StackTrace: at System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) at System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) at System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) at System.Data.OleDb.OleDbCommand.ExecuteNonQuery()

I'm not sure what ErrorCode is but it looks unreliable.

Can I rely on Message being identical across platforms?

Can I somehow use the OleDbHResult hr value found in the stack trace? (See https://stackoverflow.com/a/991660/327528)

Is the only solution to do a text search of Message for words like duplicate and primary key?

Joe · Answer 1 · 2012-10-09T09:48:01.500

7

You're right that the ErrorCode (-2147467259 = 0x80004005 = E_FAIL) is too generic, and relying on the message is very fragile - e.g. consider localization.

I would look at the OleDbException.Errors collection to try to find something more specific.

edited Oct 09 '12 at 09:48

answered Oct 09 '12 at 09:41

Joe

122,218
32
205
338

What about `OleDbHResult hr` ? – CJ7 Oct 09 '12 at 09:43
@CJ7 - if you look at `System.Data.dll` with Reflector, you'll see the definition of the internal enum `OleDbhResult`. From what I can see it's the same as `ErrorCode` = E_FAIL, i.e. non-specific. – Joe Oct 09 '12 at 09:47

score 2 · Accepted Answer · answered Oct 10 '12 at 12:25

2

The JET error code for a OleDbException can be found from:

OleDbError.SQLState

which is 3022 for the exception provided in the question.

answered Oct 10 '12 at 12:25

CJ7

22,579
65
193
321

score 2 · Answer 3 · edited Jul 25 '19 at 09:46

2

You can try using the SQLState property for each error in the OleDbException.Errors collection

Found this reference: SQL State Error Codes <- Not working anymore
Updated reference: SQL error codes

edited Jul 25 '19 at 09:46

Abir Stolov

37
9

answered Feb 11 '13 at 17:18

Abir

103
8

not found http://www.rosam.se/doc/atsdoc/Server%20-%20Messages%20and%20Codes/ADC5906BBD514757BAEE546DC6F7A4FA/0.htm – Kiquenet Jul 18 '19 at 07:31

score -1 · Answer 4 · edited Jan 28 '13 at 13:38

-1

try {
  cmd.ExecuteNonQuery();
} catch (OleDbException ex) {
  if (ex.ErrorCode == -2147467259) {
    MessageBox.Show("Hi , yes");
  }
}

edited Jan 28 '13 at 13:38

palaѕн

72,112
17
116
136

answered Jan 28 '13 at 13:21

zaher Ahmad alBalawi

1

How to catch specific OleDbException?

4 Answers4