@@Identity returns a decimal?

After doing an insert, I am using a System.Data.SqlClient.SqlDataReader to fetch the identity of the inserted row with the following code:

dbCommand.CommandText = "SELECT @.@.IDENTITY As LogID SET NOCOUNT OFF"
Dim dataReader As System.Data.SqlClient.SqlDataReader = dbCommand.ExecuteReader
dataReader.Read
dataReader.GetInt32(0)
dataReader.Close

The problem is that I'm getting an invalid cast exception. If I use dataReader.GetDataTypeName(0), it tells me that value is a decimal? This is odd because the identity column in the SQL table is an int (size: 4). When I use very similar code for another database on the same server, it works.
Anybody have any ideas?

According to the SQL Books Online @.@.Identity is of type numeric (not integer). This would explain why it is returned as decimal instead of an integer.
Brian

|||@.@.Identity doesnt return the ID from the same scope. You should use SCOPE_IDENTITY and furthermore, you should use a stored procedure, which does the insert AND returns the new ID.|||

Hi,
I'm adding a simple stored procedure to show a sample for SonuKapoor's note

CREATE PROCEDURE SiteCreate
(
@.SiteName as nvarchar(100)
)

AS

INSERT INTO Site SELECT @.SiteName, 1

SELECT SCOPE_IDENTITY()

GO

Eralper
http://www.kodyaz.com

|||If we're going down the, "You don't want to do it like that route" then you shouldn't 'SELECT' the value back, you should use an OUTPUT param, and also include the SET NoCount ON, oh oh oh ;) use INSERT ([cols]) VALUES ([values]) syntax, don't select into the insert like that.