Field with Identity - How to know the written value

[TorrediPisa]

Good Day to everyone.

When I write a record in a Table (where there is a numeric field with identity (counter)), how is possible to know the value that will be written in that field before or after the updating?
For Example my Table has three fields: ID(with identity), Field1 and Field2
If I do like this:
rsADO.Addnew
rsADO!Field1 = "Value1"
rsADO!Field2 = "Value2"
rsADO.Update
Where can I know the value of rs!ID?

(In Access we can do that)

Thanks for yr help
Regards
TdP

 

[SQLDenis]

in SQL server @@IDENTITY or SCOPE_IDENTITY()
This works only for inserts
Don't know how you would get it from ADO
Convert you code to a stored procedure and return the identity value by using @@IDENTITY or SCOPE_IDENTITY()

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[jbenson001]

You can get the ID after the insert, with @@identity or Scope_Identity().

You can't really know what it is ahead of time. You could query the table and get a MAX() value on the id column. But that may not be valid by the time you execute the Insert. I am not sure why you would want to know what is ahead fo time.

Jim

 

[TorrediPisa]

Thank you both.
If I want to know this value after the Update, you say I have to use the @@Identity or Scope_Identity.
Could you kindly show me how to do that in my posted code?

Thanks a lot
TdP

 

[TorrediPisa]

Sorry I forgot to say that I need to do that from a Visual Basic Project connected with ADO.
Thks

 

[jbenson001]

@@Identity AND Scope_Identity are only valid on an INSERT.

 

[druer]

Torre - You should already know the value of the identity when you do the update since the record would already exist, and you would have selected it to know which record you wanted to edit and make the change to. Otherwise, how would you know which record to do the UPDATE for?

 

[TorrediPisa]

Thank you druer.
I do not know the value of the identity field because I have just created it by an Addnew statement.

 

[jbenson001]

I think you are confusing the what is meant by Update and Insert. When the row is inserted, a new identity is created, that is what you obtain with @@identity or scope_identity. After that the ID exists and is never changed. To update and existing row, you need to know that ID as druer has stated.

 

[SQLDenis]

rsADO.Addnew
rsADO!Field1 = "Value1"
rsADO!Field2 = "Value2"
saveKey = rs.Fields("CustNo").Value
rsADO.Update


and of course CustNo would be your identity column, let me know if it works




Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[TorrediPisa]

SQLDenis,
saveKey always contains zero if it is put in that position.
Bye

 

[SQLDenis]

Try this
rsADO.Addnew
rsADO!Field1 = "Value1"
rsADO!Field2 = "Value2"
rsADO.Update
saveKey = rs.Fields("CustNo").Value

Denis The SQL Menace
SQL blog:

http://sqlservercode.blogspot.com/

Personal Blog:

http://otherthingsnow.blogspot.com/

 

[gmmastros]

I found some interesting info by doing a google search on

site:tek-tips.com .addnew identity



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[danvlas]

Acces generates the ID before saving the record.
SQL Server does that after that.

scope_identity() should be the output parameter of your insert procedure.


HTH


Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[SQLSister]

Make sure you use scope_identity as @@identity will create data integrity problems if you ever add a trigger to the table which inserts to a differnt table with an indentity column. @@identiy should never be used for the purpose of finding the key you just inserted.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief