Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2005
    Posts
    115

    Unanswered: can't get @@IDENTITY

    I am trying to get the last ID auto in MS SQL 2000

    SELECT @@IDENTITY as ident FROM users

    and i get nothing

    i am using

    SELECT MAX(id) as ident from users

    what is the correct way to be shure to get at once the real last ID ?

    thank you

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My guess is that you aren't doing the SELECT @@identity from the same context (database connection, aka spid), so it can't retrieve the identity value for you. Can you post your code from the INSERT to the SELECT of the identity for us?

    -PatP

  3. #3
    Join Date
    Jun 2005
    Posts
    115
    i am working with vb net / asp net
    I run a custom command

    dim _id as int32
    RunCommand("INSERT INTO user (num) value (0)")
    id_ = ExecuteScalar("SELECT @@IDENTITY as ident FROM user")

    and i dont get it

    this code works for access 2000 perfectly

    ---------------------------------------

    I found this kind of code somewhere

    strSQL = "Set Nocount on "
    strSQL = strSQL + " Insert Topics (TopicName, SortOrder) VALUES ('X', -1) "
    strSQL = strSQL + " select LastID=@@identity"
    strSQL = strSQL + " set nocount off"

    thank you

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You'll need to tidy this up a bit, but I'd suggest something like:
    Code:
    id_ = ExecuteScalar("INSERT INTO user (num) value (0)
    SELECT @@IDENTITY as ident FROM user")
    This makes the INSERT and the SELECT operate in the same SQL context, so it is possible to retrieve the correct identity value.

    The problem comes from the difference in how Jet (the database engine that lies underneath MS-Access) and MS-SQL look at connectivity. Jet is single user, MS-SQL is multi-user. There's a whole different set of problems to solve.

    The other code that you posted:
    Code:
    strSQL = "Set Nocount on "
    strSQL = strSQL + " Insert Topics (TopicName, SortOrder) VALUES ('X', -1) " 
    strSQL = strSQL + " select LastID=@@identity"
    strSQL = strSQL + " set nocount off"
    ...does the same thing in a slgihtly different way.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •