Returning the id of an inserted record -- dotnet ado.net

Nathan Sokalski
2/29/2008 1:20:34 AM

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/

Kerry Moorman
2/29/2008 4:05:03 AM

Nathan,

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

Kerry Moorman

[quoted text, click to view]

Andrew J. Kelly
2/29/2008 9:20:13 AM

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

[quoted text, click to view]

Mike C#
2/29/2008 9:18:17 PM

[quoted text, click to view]

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.

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages about dotnet ado.net

dotnet ado.net : Returning the id of an inserted record