RE: trying to copy the autonumber from one table to the next

From: Brian Brown (BrianBrown_at_discussions.microsoft.com)
Date: 12/06/04 

Date: Mon, 6 Dec 2004 06:19:07 -0800

Sparks,

You cannot call just SELECT @@IDENTITY. Sql has no way of knowing what
record you want to look for. Would it be possible for you to write a stored
proc and send it your parameters? Then you could use a return statement in
the stored proc to return the ID of the inserted record and then grab it
using ExecuteScalar(). This is a lot more efficient and reliable all the way
around.

Good Luck!

Relevant Pages

  • RE: Inserting/deleting data in table in multiuser environment
    ... Add a field to the table in the SQL DB? ... Capture the value to a variable before you send it to the stored proc, ... I did create a table in a front-end to just copy data from that table on SQL ... the stored procedure will be running each time with report generation anyway. ...
    (microsoft.public.access.modulesdaovba)
  • Retrieving primary key of newly added record
    ... stored proc explicty). ... key field is an identity/autonumber one in SQL Server - that is SQL ... I'm worried that if I do another explicit query, ... Handling concurrency with stored procs. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: full-text contains * issue
    ... You may also want to consider using a stored proc for this situation as it ... EXEC usp_FTSearchPubsInfo '' ... > no data but was concatenation of the fields I wanted to index. ... > This is the SQL ...
    (microsoft.public.sqlserver.fulltext)
  • Re: Parameter Sniffing - Need more info
    ... Consider a stored proc defined as follows: ... In 6.5, at compile time SQL ... when compiling a plan. ... execution plans, but a key requirement for everything to work as expected ...
    (microsoft.public.sqlserver.programming)
  • Re: type argument in OpenRecordset method
    ... format call to stored proc ... The databases/tables on the SQL Server side are split up based on ... derive the fully qualified table name, the Access app does, in fact, ... things on the SQL side, and by providing this abstraction layer, it is ...
    (comp.databases.ms-access)