Row ID after INSERT to database

[Huseyin Altindag]

Hi I think I've got something it can help you on www.codeproject.com/cs/database/DBManipulationADONET.asp Huseyin Altindag

[Abyss]

Well, it is a great idea to use "SELECT MAX(ID) from TableXXX". I suppose that if some record is removed from the table then the given gap is not filled by the new records - the ID is not reused. Always new ID is generated which is greater than the rest. Right? Thanks, Abyss

[Huseyin Altindag]

Hi Yes but remember always the new ID generated is greater than the last one you insert. I'll send you(maybe today) a complete application(C#) with SQLServer solution how to do it Huseyin

[Abyss]

Thanks in advance. Have you any idea why the original solution fail with "SELECT SCOPE_IDENTITY()" and "SELECT @@IDENTITY AS 'Ident'"? It is on the MSDN - how to obtain the ID of the inserted record. Is it an Access related problem? Anyway your solution is great I think that it will work in all cases. Abyss

[Mike Dimmick]

Those features are part of Microsoft SQL Server, not Access/Jet. Stability. What an interesting concept. -- Chris Maunder

[Jeff Martin]

If you are calling this in a separate query from the insert, I know SCOPE_IDENTITY() won't work. I'm not sure about @@IDENTITY. I write my stored procs that do the insert where they get the scope_identity() and return it. On the code side, I just get the return from teh stored proc. Jeff Martin My Blog

[Jeff Martin]

No, it is not a great idea. This only works if it is a single user database AND if the ID has not been reset AND SQL Server decides not to fill in gaps. Do not use select max(ID)... Jeff Martin My Blog

[Abyss]

I'm using an Access (MDB) database - via Access/Jet ODBC driver. I suppose that it is a single user database - maybe it is possible to access the database table from different applications at the same time... Will such database fill in gaps (reuse IDs) and when resets the server the ID? Mark told (see the thread) that "SELECT SCOPE_IDENTITY()" are part of SQL server not Access/Jet. Any other idea? I really appreciate your help :) Thanks, Abyss

[Abyss]

Uhh, it is out of my knowledge (I'm rather a novice). How does such stored procedure looks like? Something like this? INSERT INTO TableXXX (A, B) VALUES (3, 5); SELECT SCOPE_IDENTITY(); in one call. Is it possible at all? Thanks, Abyss

[Jeff Martin]

Sorry, no. I don't use Access. Jeff Martin My Blog

[Jeff Martin]

Basically, yes. It is basically a predefined collection of SQL statements stored on the database. But with Access, you can't use them. Jeff Martin My Blog