Error-handling with Access ODBC datasource

[philcha]

I want to add a record to an Access table which I've configured as an ODBC datasource. Even if I run a query to see if the proposed key already exists, I still have to check for a "duplicate key" error after the INSERT. The problem is that CFCATCH is returning a CFCATCH.NativeErrorCode value of -1605 (yes, with a minus sign). My Access manual says the "duplicate key" error code is 3022, and this is what I get if I try to add a duplicate key in VB.

Even crazier, if my error-handler tests for -1605 it does not get a match and so does not recognise the cause of the error.

So what's going wrong and how can I fix / avoid / work round it?

Notes on software I'm using:
CF version 4.01
Windows 98 with Personal Web Server
I've tried setting the DB up as an Access 2.0 DB (the last Win 3.1 format) and as an Access 3.0 DB (Access 95 or 97 format, I'm not sure which). In either case I have the same problem with INSERT but the rest of my DB accesses work fine.

Here's an outline of the code:

<cftry>
[the INSERT code - works fine if not duplicate key]
<cfcatch type=&quot;Database&quot;>
<cfswitch expression=&quot;CFCatch.NativeErrorCode&quot;>
<cfcase value=&quot;-1605&quot;>
[create &quot;duplicate key message to display]
<cfcase>
<cfcase value=&quot;....&quot;>
[handle other errors]
</cfswitch>
</cfcatch>
</cftry>

 

[GunJack]

Hi Philcha,

I think you need to change

<cfswitch expression=&quot;CFCatch.NativeErrorCode&quot;>
to
<cfswitch expression=#CFCatch.NativeErrorCode#>

Without the pound signs, you're passing a text string and not the variable.

GJ

 

[philcha]

Gunjack,

Thanks for answering part 2 of my query (and why didn't I think of that - doh)!

But why the $%^&amp;* is the CFCATCH.NativeErrorCode value so wildly different from what my Access manual's telling me? If I can't resolve this I'll only ever be able to handle &quot;duplicate key&quot; errors, although I should also provide for &quot;database unavailable&quot;, &quot;DBMS error&quot;, &quot;disk error&quot; and &quot;database full&quot;.

Anyone ogt any ideas?

 

[GunJack]

Hey Philcha,

What does the #cfcatch.message# and #cfcatch.detail# fields contain? I don't work with error codes but the -1605 may be a lower level error code perhaps and the 3022 may be passed out in the message or detail fields at a higher level.

GJ

 

[philcha]

GunJack,

Message:
ODBC Error Code = 23000 (Integrity constraint violation)

Detail:
[Microsoft][ODBC Microsoft Access Driver] 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.
SQL = &quot;INSERT INTO ... (the whole INSERT statement)
Data Source = ....
(the SQL again)
file and position in which the error arose
calling stack

By the way, catch.sqlstate = 23000 (as per cfcatch.message)

I've just rechecked the CFML Reference (again!) and the way it's phrased the only useful info (specific and coded) should be cfcatch.NativeErrorCode.

I'm beginning to wonder if there's some misunderstandng between CF and the Access JET engine over number formatting. I reckon decimal 3022 is hex 188E and if you swap the pairs of hex digits you get 8E18. If the error code is a 2-byte integer, hex 8E18 has a negative value. I'm very hazy about 2s complement notation but I'd have thought that would give a negative ending in 9 or 7 rather than 5.

Any ideas?