1

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.

GR7
  • 5,083
  • 8
  • 48
  • 66

3 Answers3

3

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).

David-W-Fenton
  • 22,871
  • 4
  • 45
  • 58
  • thanks David..but first, you should be using SCOPE_IDENTITY() instead of @@IDENTITY...and second, doing it just like it can be done in SQL server is what I'm looking for. With SQL Server you can just add a SELECT SCOPE_IDENTITY() at the end of the sproc, and you get the new Identity in the same execution of ExecuteScalar(). However, I haven't find a way to do this with Access in a single step, that's why I wanted to know how other people is handling this. – GR7 Apr 08 '10 at 14:01
  • I honestly didn't know about SCOPE_IDENTITY(), probably because all my Access apps with SQL Server back ends were upsized from Access and don't have any triggers (which so far as I can tell is the only reason to use it in preference to @@IDENTITY). In Jet/ACE, there aren't any triggers (well, there will be in Access 2010, so maybe they have added SCOPE_IDENTITY() to the new version of Jet), so there's no danger here -- the scope is limited to the current connection, so no issue at all as long as you re-use the same connection -- two steps will work just fine. – David-W-Fenton Apr 11 '10 at 00:43
  • 1
    +1 David is right. Scope_Identity doesn't apply here since it is only valid in a sproc. In sql it prevents you from getting an invalid id e.g. in case a trigger is running on the target table which inserts another value before you can get the new identity. In jet you specify the table SELECT @@Identity FROM sTableName which limits your scope anyway. – Praesagus Apr 12 '10 at 22:56
  • 2
    The FROM clause has no effect at all. I just tested with two inserts into two different tables and tried to specify the table, but got back the same identity number for both, i.e., the one added to the last table that had an insert. – David-W-Fenton Apr 14 '10 at 00:35
0

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.

GR7
  • 5,083
  • 8
  • 48
  • 66
  • But with a Jet/ACE data store, as long as you execute SELECT @@IDENTITY with the same connection immediately after your insert, you'll get the correct Autonumber value. Yes, it's two steps, but IT DOESN"T MATTER -- you'll get a reliable answer. Why do you think it not being a single step is a problem? – David-W-Fenton Apr 10 '11 at 00:35
  • Actually, there's another way to do it, using an recordset, i.e., open a recordset of type add, add a new record, fill in the data, pick up the Autonumber value, save the record, and you're done. But this is a lot more than the two steps using a SQL INSERT and SELECT @@IDENTITY. – David-W-Fenton Apr 10 '11 at 00:36
  • Hi David, thanks for the comment. Well, the reason I wanted it in a single step is because I'm using a .NET application with Enterprise Library, and we have CRUD sprocs. On the data layer of the .NET App, i dont want to have to call two different sprocs in the same C# method call. That's why I felt important to note that this can't be done in a single sproc in Access. To know that the limitation is there is important. – GR7 Apr 11 '11 at 01:41
  • The requirement for a single operation is not really relevant. Wrap it in a transaction, do the two steps, commit the transaction, and you're done. It doesn't matter at all that it took two steps inside the transaction -- it really doesn't. – David-W-Fenton Apr 13 '11 at 01:14
-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

Kishore Kumar
  • 21,449
  • 13
  • 81
  • 113
  • 2
    None of this will work with Access/Jet/ACE. First off, you can't execute two SQL statements in one QueryDef, secondly, SCOP_IDENDITY() is SQL-Server-specific. – David-W-Fenton Apr 12 '10 at 19:42