AutoNumber

[gmariog]

Does anyone know why I can retrieve the value of an AutoNumber field in Access before updating the recordset, but I can't do the same in SQL server.

Example:
************************
In Access this works:

rs.AddNew

iID = rs.Fields("ID&quot '*** AutoNumber field

rs.update
***********************

In SQL Server it returns a NULL. I have to update the recordset first.

 

[joe75]

Hi, gmariog,

Yes, In Access have a datatype(AutoNumber), but in SQL Server no this datatype. The simply way is: You can import table from Access(in access table, you need set PK and AutoNumber) to SQL Server and then set primay key in that field in the SQL server. You can test.

Jing

 

[tlbroadbent]

The IDENTITY value isn't available until after the update. SQL Server doesn't assign the value until the record is committed.

FYI: If you were to add records using SQL Insert statements rather than the AddNew method, you could return the identity value using a select statement.

Select @@identity As IDValue.

But you still wouldn't get the value until after the record was inserted. Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.

 

[gmariog]

Yes , I found out that you can retrieve the value of the IDENTITY variable by using the INSERT method and it gives you the most recently used IDENTITY value for the current connection. This must be the way to do it because the book that I'm getting this information from also states the following when talking about the Identity field value:

"Applications usually need this information because Identity fields are used as foreign keys into other tables."

That's exactly what I need!

So, I guess a Trigger would get the most recent value for the current connection and ensure that the correct value would inserted in the corresponding table.

 

[MikeTom]

If you want to add a trigger to the insert you donot need to supply the id. the way it works is on insert it creates a temp table called inserted wich contains the record inserted and all the relevant colums so all you put in your trigger would be 'select ID from inserted'.