Get Autonumber from newly inserted record in Access 2007 using Enterprise Library 4.1

Question

It's been ages since I last used Access as a back end but I'm being forced to. I'm using Enterprise Library 4.1, the Data Access Application Block.. with .NET 3.5 and I wanted to know the best way (code sample if possible) to write an insert query that will automatically give me the newly inserted auto-number back..or if it's not possible to do it in one step, how do you recommend doing it?

thanks for your help.

Answer 1

After further research, this just cannot be done in Access in a single step, which is what I was looking for. I will provide links to the pages where I found confirmation about the limitation when i get come.

Answer 2

With a single connection:

1. execute the INSERT statement.

2. then get the result of SELECT @@IDENTITY, which will return the Autonumber value inserted in step 1.

In other words, it's just like SQL Server (and has been since 1999, when Jet 4 was introduced including support for SELECT @@IDENTITY).

Answer 3

This how iam doing in SQL Server. This will return the autonumber primary key in output vaiable

CREATE PROCEDURE [dbo].[TEST](

@p_ID NUMERIC(9,0) OUT ,

@p_NAME NVARCHAR(150)

AS BEGIN

   INSERT INTO EMR_INV_MAST_ORDERSET(NAME)
  VALUES (@p_NAME)
  SELECT @p_ID= SCOPE_IDENTITY()

END