INSERT Query

• Sandy-535251

  Newbie

  Points: 1

  More actions

  October 30, 2007 at 6:23 pm

  #199609

  Hello!

  Using ASP, with the RecordSet Object (objRS), I have a question

  When running the following query:

  "SELECT something FROM table"

  To get the value something, you would do:

  var = objRS("something").Value

  Now with the following scenario:

  Some tables have identy columns (that is set automatically when inserting row)

  Now I wonder, if I have the following table:

  [IDX] [int] IDENTITY (1, 1) NOT NULL,

  [something] [varchar] (32) NOT NULL

  If I would run the following query:

  "INSERT INTO table([something]) VALUES('sometext')"

  would I be able to do this:

  var = objRS("IDX").Value

  to get the value of the new IDX that was generated when creating this row in the database?

  Thanks,

• goodguy

  SSCertifiable

  Points: 5456

  More actions

  October 31, 2007 at 3:55 am

  #746968

  Use this statement immediately after committing your insert to extract the last identity value used:

  SELECT @@IDENTITY FROM Table

  ---

  Regards,

  goodguy

  Experience is a bad teacher whose exams precede its lessons
  

• Ramesh Saive

  SSC-Insane

  Points: 24275

  More actions

  October 31, 2007 at 4:20 am

  #746979

  Just a point to make...

  @@IDENTITY and SCOPE_IDENTITY return the last identity value generated in any table in the current session. However, SCOPE_IDENTITY returns the value only within the current scope; @@IDENTITY is not limited to a specific scope.

  --Ramesh
  

  ---

• goodguy

  SSCertifiable

  Points: 5456

  More actions

  October 31, 2007 at 5:03 am

  #746993

  Thanks, Ramesh.

  ---

  Regards,

  goodguy

  Experience is a bad teacher whose exams precede its lessons