Returning the id of an inserted record

I am writing an ASP.NET application, and am forced to use Access as my
database. I need a way to return the id (which is an AutoNumber field in the
table I am inserting into) of a record when I insert it. In SQL Server, I am
able to use a statement like the following:

INSERT INTO tablename (fieldnames) OUTPUT inserted.id VALUES(valuestoinsert)

Take note of the OUTPUT inserted.id part of this statement. This returns the
value of the field named 'id' for the record that was just inserted.
Unfortunately, this technique does not work in Access. Is there any way to
retrieve the id of the record that was just inserted when using Access? Any
help would be appreciated. Thanks.
--
Nathan Sokalski
njsokalski@hotmail.com
http://www.nathansokalski.com/

KerryMoorman
Fri Feb 29 06:05:03 CST 2008

Nathan,

After the insert statement you need to do a Select @@Identity statement to
retrieve the generated ID value.

Kerry Moorman


"Nathan Sokalski" wrote:

> I am writing an ASP.NET application, and am forced to use Access as my
> database. I need a way to return the id (which is an AutoNumber field in the
> table I am inserting into) of a record when I insert it. In SQL Server, I am
> able to use a statement like the following:
>
> INSERT INTO tablename (fieldnames) OUTPUT inserted.id VALUES(valuestoinsert)
>
> Take note of the OUTPUT inserted.id part of this statement. This returns the
> value of the field named 'id' for the record that was just inserted.
> Unfortunately, this technique does not work in Access. Is there any way to
> retrieve the id of the record that was just inserted when using Access? Any
> help would be appreciated. Thanks.
> --
> Nathan Sokalski
> njsokalski@hotmail.com
> http://www.nathansokalski.com/
>
>
>

Andrew
Fri Feb 29 08:20:13 CST 2008

I wasn't aware that @@Identity was also in Access so you may want to post
this question in an ACCESS news group not a SQL Server one. And for future
reference you should not use @@IDENTITY in SQL Server, you should use
SCOPE_IDENTITY() instead for this type of situation.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors


"Kerry Moorman" <KerryMoorman@discussions.microsoft.com> wrote in message
news:506B01EA-17B1-42A3-8A0D-9D74254E07B4@microsoft.com...
> Nathan,
>
> After the insert statement you need to do a Select @@Identity statement to
> retrieve the generated ID value.
>
> Kerry Moorman
>
>
> "Nathan Sokalski" wrote:
>
>> I am writing an ASP.NET application, and am forced to use Access as my
>> database. I need a way to return the id (which is an AutoNumber field in
>> the
>> table I am inserting into) of a record when I insert it. In SQL Server, I
>> am
>> able to use a statement like the following:
>>
>> INSERT INTO tablename (fieldnames) OUTPUT inserted.id
>> VALUES(valuestoinsert)
>>
>> Take note of the OUTPUT inserted.id part of this statement. This returns
>> the
>> value of the field named 'id' for the record that was just inserted.
>> Unfortunately, this technique does not work in Access. Is there any way
>> to
>> retrieve the id of the record that was just inserted when using Access?
>> Any
>> help would be appreciated. Thanks.
>> --
>> Nathan Sokalski
>> njsokalski@hotmail.com
>> http://www.nathansokalski.com/
>>
>>
>>

Mike
Fri Feb 29 20:18:17 CST 2008

"Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message
news:upquw4teIHA.1208@TK2MSFTNGP05.phx.gbl...
>I wasn't aware that @@Identity was also in Access so you may want to post
>this question in an ACCESS news group not a SQL Server one. And for future
>reference you should not use @@IDENTITY in SQL Server, you should use
>SCOPE_IDENTITY() instead for this type of situation.


Access supports it only through the Jet OLEDB 4.0 provider:
http://support.microsoft.com/kb/815629

You're right though, he'll probably more authoritative answers from the
Access newsgroup.