Get ID of new Record

[varnix]

I am doing a data insert into an MS Access 2000 DB and I need to get the id of the new record that was just inserted. but I don't want to go through the hassle of querying the DB for it.

I found a reference or two out on the web that indicates that something like this should work:

<cfquery datasource="#mydsn#" name="myquery">
insert into table (field1,field2)
values ('#value1#','#value2#');
select @@IDENTITY as 'latestid'
</cfquery>

But, when I have the semi-colon in there I get an error about extra characters at the end of the SQL statement. If I remove it, the system tells me that it needs a semi-colon.

Now - to be honest, I'm not really sure if I have an IDENTITY field in my DB. I can't find anything on how their created and I assume that this is the primary key field (normally ID).

So...if anyone could help me out, that would be greatly appreciated!

 

[NorthStarDA]

i think the Identity column is specific to SQL server. im not aware of a similar function for access, ive use access and mysql, and i usually have to do a second run at the database to get the last entered id- i would also appreciate if anyone knows a better way to do it.

 

[imstillatwork]

i know this is old, but you can do this.

<cfquery datasource="#mydsn#" name="myquery">
insert into table (field1,field2)
values ('#value1#','#value2#')

</cfquery>

<cfquery datasource="#mydsn#" name="anotherquery">
select @@IDENTITY as 'latestid'
</cfquery>

and then use cftransaction around all of that so that only one person uses it at a time, no mix ups.

 

[r937]

access uses AUTONUMBER instead of IDENTITY columns

i'm told @@IDENTITY does work in access, but i've never used it myself

the best way to solve this problem, in my humble opinion, starts with acknowledging that any autonumber or identity column is a surrogate key

as the name suggests, there is (or should be!!) another key lurking in the table

in data modelling this is called a candidate key

i just call it the "real" primary key

anyhow, if you're going to have to run two queries, why bother with a transaction lock?

just query the row back using the values of the candidate key

you do have a UNIQUE constraint on the candidate key, right?

failure to declare this UNIQUE constraint is the reason that so many people post messages on forums like "HELP!! How do I remove duplicates from my table??"





rudy

http://r937.com/

SQL Consulting