SCOPE_IDENTITY() problems

No matter how hard I look, I can't find a suitable answer to the many questions people tend to have about retriving an identity value after an SQL Insert command, particularly in C#. Everyone keeps going on about the advantages of Scope_Identity over @.@.IDENTITY but nobody seems to have actually explained properly how to use it ... hence my problems and frustrations.

I can use @.@.Identity fine since it returns a simple value (an int, I think). But the site I'm developing will apparently have heavy traffic (a similar site by the client uses 25Gb of bandwidth per month) so @.@.Identity is probably out of the question.

The problem I have is that Scope_Identity, along with the SqlCommand.ExecuteScalar() method returns an object, which is not what I want. I can't cast the object into an int.

I'm presently just using SqlConnection and SqlCommand classes to build and execute SQL queries (no data adapters in sight) so I need to know how to use the SCOPE_IDENTITY within that context.

Another question: I'm using the command builder to create the SQL commands. I've noticed some examples adding a SELECT @.thisId = SCOPE_IDENTITY(). However, the command builder doesn't like this syntax (?)Quick question to you, how would return any integer value? It's really no different. There is absolutley no difference (in terms of consuming it) between @.@.Identity and SCOPE_IDENTITY. Ones a variable that you can select back, and ones a function that you can select back.|||Well, here's the code I've been using:

On one part of the application, I'm using:

SqlCommand idCMD = new SqlCommand("SELECT @.@.IDENTITY",conTempProperties);
int prop_id = Int32.Parse(idCMD.ExecuteScalar().ToString());

and on another, I'm using:

SqlCommand sqlCMD = new SqlCommand("SELECT SCOPE_IDENTITY()",conClient);
client_id = Int32.Parse(sqlCMD.ExecuteScalar().ToString());

The first one works, while the second one gives me an 'Input string was not in a correct format' error. So scope_identity and @.@.IDENTITY obviously don't return the same data types (?).|||check out BOL for the xact differences. heres the cut/pasted info from BOL :

SCOPE_IDENTITY
Returns the last IDENTITY value inserted into an IDENTITY column in the same scope. A scope is a module -- a stored procedure, trigger, function, or batch. Thus, two statements are in the same scope if they are in the same stored procedure, function, or batch.
Syntax
SCOPE_IDENTITY( )
Return Types
sql_variant
Remarks
SCOPE_IDENTITY, IDENT_CURRENT, and @.@.IDENTITY are similar functions in that they return values inserted into IDENTITY columns.
IDENT_CURRENT is not limited by scope and session; it is limited to a specified table. IDENT_CURRENT returns the value generated for a specific table in any session and any scope. For more information, see IDENT_CURRENT.
SCOPE_IDENTITY and @.@.IDENTITY will return last identity values generated in any table in the current session. However, SCOPE_IDENTITY returns values inserted only within the current scope; @.@.IDENTITY is not limited to a specific scope.
For example, you have two tables, T1 and T2, and an INSERT trigger defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 as a result of the trigger.
Assuming that both T1 and T2 have IDENTITY columns, @.@.IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1.
@.@.IDENTITY will return the last IDENTITY column value inserted across any scope in the current session, which is the value inserted in T2.
SCOPE_IDENTITY() will return the IDENTITY value inserted in T1, which was the last INSERT that occurred in the same scope. The SCOPE_IDENTITY() function will return the NULL value if the function is invoked before any insert statements into an identity column occur in the scope.

hth|||I'd advise that you really want to put that code with insert/update. It's supposed to be in the same context as the batch/command you've just run. Simply selecting scope_identity without doing any work will return NULL.|||Thanks for that.

I'll try changing the way I've done the scope_identity(). I did come across someone using it with ExecuteNonQuery rather than ExecuteScalar and tried it. It seemed to work on that occasion but it returned a null - no doubt because I hadn't included it in the same batch as the INSERT command.|||I've been trying to add the scope_identity to the end of the INSERT statement with the command builder but I keep getting a parse error. How do I actually do it? What's the correct syntax?|||Take a look at the SQL the command builder...builds. Plus what syntax are you trying to use?|||Well, I'm trying something like:

INSERT INTO clients (business_name,address,town)
VALUES (@.business_name,@.address,@.town);
SELECT SCOPE_IDENTITY() AS ident

I have to admit I'm flying blind here. I've also tried the following:

INSERT INTO clients (business_name,address,town)
VALUES (@.business_name,@.address,@.town);
SELECT @.ident = SCOPE_IDENTITY()

Errr ...|||

INSERT INTO clients (business_name,address,town)
VALUES (@.business_name,@.address,@.town) SELECT @.ident = SCOPE_IDENTITY()

should work pretty good.

hth|||*Sigh* Still getting a parsing error. Maybe the command builder doesn't allow you to create complex commands?

Anyway, I'm going to try doing things programmatically to see if that works.|||are you using a stored proc ? can you post the relevant code ?|||You *need* to read this
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnadonet/html/manidcrisis.asp