I'm having the issue where @@identity returns null.
I've seen several responses that @@identity is a session variable.
I'm coding directly with ado.net(sqlserverce)
I execute my insert and then do a select for @@identity.
I don't see the connection state changing to closed between the calls.
In addition I get the exact same behavior if I run the query analyzer.
Most posts I see mention the query analyzer works for them with @@identity.
Is there som property of the engine I need to set or something?
I'm running sqlserverce 4 swith visual studio 2011.
oops visual studio 2010.
here's the code:
sqlcecommand.CommandText() ="insert into clients (client_name) values('"+ clientname + "')"
recordcount = sqlcecommand.ExecuteNonQuery()
sqlcecommand.CommandText = "select @@identity"
Dim test As Object = sqlcecommand.ExecuteScalar
Regarding to your description, the @@IDENTITY is an internal variable which is a session based. Assuming when connection close then you will lost the current session so my next request with a new connection object is in fact a new session.
The @@IDENTITY returns a decimal value so that it needs to be type casted to an integer. Meanwhile make sure the Insert statement executed success.
For more information, please refer to the last reply in this thread.
Regards, Amber zhang
What I don't understand is why my connection is being closed. I explicitly open the connection at the beginning of my application and don't close it until the end.
If I query the state of the connection before or after each command execution ...it is open.
Actually my code is quite similair to what is the last reply on your suggested thread.
What i'm confused about is why two commands executed back to back without closing the connection are in different sessions.
Perhaps there 's some extended parameter of either the connection or command object I don't know about?