Return Autonumber after INSERT?

[Sahil Malik]

There are variables in SQL Server for exactly this job -

@@IDentity
@@Scope_Identity
Ident_Current('tablename')

So your command could be a batched SQL command

Insert into monkeytable values (....) ;
Select @@Identity into lastmonkeynumber ;

Hope that helped

- Sahil Malik
Independent Consultant
You can reach me thru my blog - http://dotnetjunkies.com/WebLog/sahilmalik/

 

[cosine... zero]

Hi!

Any great ways to return an Autonumber field that was set by an INSERT? I
really liked ADO's recordsets, getting the autonumber value was easy then...

Thanks!

Jim

 

[Sahil Malik]

Of course if this were oracle, you'd use currval .. which is why no matter
what people say .. I firmly believe it is not easy to switch databases in ur
project

- Sahil Malik
Independent Consultant
You can reach me thru my blog - http://dotnetjunkies.com/WebLog/sahilmalik/

 

[Sahil Malik]

We've had to do that long time back. We had to support both Access and SQL
server for some time and that was such a royal pain. True you can abstract
it in the data layer, but even that is so much work, not to mention - you
loose out on so much you could gain by going the SQL stored procedure route.

I would say dump access/jet .. and go to MSDE instead. Thats what we did and
never looked back since.

- Sahil Malik
Independent Consultant
You can reach me thru my blog - http://dotnetjunkies.com/WebLog/sahilmalik/

 

[cosine... zero]

Thanks for both; I'm really addressing your 'not easy to switch databases'
topic here - I need a solution for Access (hence Autonumber).

It's easy to switch databases as long as you isolate the data access logic
from the business logic... but yeah, it can definitely suck making that
abstraction work right.

Jim

 

[cosine... zero]

Sadly, using/not using Access is more a function of my clients individual
needs, not mine... Tho I have a small affinity for Access, spending almost 2
years in MS-PSS supporting it.

Jim