Unless I'm mistaken, I don't believe that Access has a CAST() function.
It has a CONVERT() function, but no CAST().
There are test cases (namely: InsertCategoryViaInsertSatement in
\Maps\Access\OleDb\Category.xml) for Access that try to use this syntax
for making sure <selectKey> returns an int:
<selectKey property="Id" type="post" resultClass="int">
select CAST(@@IDENTITY as int) as value
</selectKey>
I copied the test case into my code and kept getting this error:
Exception: System.Data.OleDb.OleDbException
Message: IErrorInfo.GetDescription failed with E_FAIL(0x80004005).
I found that a common cause for this is that I need to add [ ] around
column names that are reserved words. I thought that was my problem but
after enclosing all my column names that problem still occured. I also
tried changing value to [value].
I discovered that there are several test cases in the Access\OleDb
folder that weren't being called. This was a bit confusing. I was under
the impression that all the sql maps in the test project .xml files
were being called. The statements that didn't have test cases written
didn't have any comments saying that they weren't being called.
I eventually solved my problem by using a simple:
SELECT @@IDENTITY
Is there a better way of doing that? Do I need to call Access'
CONVERT() function to make sure its an int?
I don't think Access is able to deal with more than one statement. It
complains about extra character after the semi-colon:
insert into Categories
(Category_Name, Category_Guid)
values
(#Name#, #Guid:Guid#);
select CAST(SCOPE_IDENTITY() as int) as value
I copied that from the InsertCategory statement.
These gotcha's might seem simple to someone who works with Access a lot
but for someone who rarely works in Acccess they took a few hours to
figure out.
- Ron
