INSERT, SCOPE_IDENTITY, and parameters...

I have a simple table Person (PersonID, PersonName, and PersonAge). PersonID is the primary key and it's also an identity field. Let me paste a sample code and I'll explain at the bottom what's happening.

 SqlConnection conn =new SqlConnection(@."Server=.\SQLEXPRESS;Initial Catalog=Test;Trusted_Connection=True"); conn.Open();try { SqlCommand cmd =new SqlCommand(); cmd.Connection = conn;// delete all rows cmd.CommandText ="DELETE FROM Person"; cmd.ExecuteNonQuery(); Response.Write("start... <br><br>");// ad-hoc insert cmd.CommandText ="SET IDENTITY_INSERT Person ON"; cmd.ExecuteNonQuery(); cmd.CommandText ="INSERT INTO Person(PersonID, PersonName, PersonAge) VALUES (5, 'John Smith', 20)"; cmd.ExecuteNonQuery(); cmd.CommandText ="SELECT SCOPE_IDENTITY()"; Response.Write("ID = "); Response.Write(cmd.ExecuteScalar()); Response.Write("<br>"); cmd.CommandText ="SET IDENTITY_INSERT Person OFF"; cmd.ExecuteNonQuery();// parameter insert cmd.CommandText ="SET IDENTITY_INSERT Person ON"; cmd.ExecuteNonQuery(); cmd.CommandText ="INSERT INTO Person(PersonID, PersonName, PersonAge) VALUES (@.PersonID, @.PersonName, @.PersonAge)"; p =new SqlParameter("@.PersonID", 11); p.Direction = ParameterDirection.Input; cmd.Parameters.Add(p); p =new SqlParameter("@.PersonName","Jon Doe2"); p.Direction = ParameterDirection.Input; cmd.Parameters.Add(p); p =new SqlParameter("@.PersonAge", 21); p.Direction = ParameterDirection.Input; cmd.Parameters.Add(p); cmd.ExecuteNonQuery(); cmd.Parameters.Clear(); cmd.CommandText ="SELECT SCOPE_IDENTITY()"; Response.Write("ID = "); Response.Write(cmd.ExecuteScalar()); Response.Write("<br>"); cmd.CommandText ="SET IDENTITY_INSERT Person OFF"; cmd.ExecuteNonQuery(); Response.Write("<br>end."); }finally { conn.Close(); }

I'm basically trying to insert rows in the table in two ways: one is ad-hoc (hardcoded sql statement) and another using parameters. Using the ad-hoc method everything is OK. Whenever I use the "parameter insert" method I can not get back the ID using SCOPE_IDENTITY (I always get back a DbNull value, the data gets into the table just fine). I'm rather new to using parameters, so it's gotta be something very easy that I'm missing...

Thank you.

If I add "; SELECT SCOPE_IDENTITY()" to the INSERT statement and use ExecuteScalar instead of ExecuteNonQuery I get the identity back. The question is, why do I have to do this? If I use the SQL Server Profiler I don't see any difference between the two methods. Any thoughts?

Thank you.

|||Your SCOPE_IDENTITY() should be in the same batch as your INSERT. You are making 3 separate calls each with a different T-SQL statement and each one is treated independent of other. Better yet, use a stored proc and do it all in one place.|||

What exactly defines a batch? And if CommandText = "INSET INTO Table (fields..) VALUES (values); SELECT SCOPE_IDENTITY()" is a single batch when using parameters, why does SCOPE_IDENTITY work in two separate "batches" when not using parameters? I guess, what I'm trying to figure out is why are how are they different? Thanks.

|||

Hi,

A batch is guaranteed to be executed together. If you execute the command in 2 batches, there is possibility that a concurrent user may be also inserting into that table. Then your SELECT SCOPE_IDNETITY() query might return the incorrect number.

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and reply. Thank you!

sql