cant 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 youMy 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|||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|||You'll need to tidy this up a bit, but I'd suggest something like: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: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