SqlParameter Direction

[Jon G]

Is it possible to have a SqlParameter setup as an Output, and get it to return @@Identity without using a stored procedure? I have an INSERT statement, written as TEXT in my code (at the time being, I cannot create stored procedures). I'm trying to find out how to return the created IDENTITY that was generated. Can someone please explain to me how this works? Thanks. Jon G www.Gizmocoder.com

[Mike Ellison]

Hi Jon. Immediately following the execution of your INSERT SqlCommand object, execute a SqlCommand object with "Select @@Identitiy" as the CommandText. Something like this (cmd is a SqlCommand object and insertedID is an int):

cmd.CommandText = "SELECT @@Identity";
cmd.CommandType = CommandType.Text;
cmd.Parameters.Clear();
insertedID = (int)cmd.ExecuteScalar();

[Murugavel S]

Instead of selecting @@Identity use SCOPE_IDENTITY(). Eventhough, it returns values inserted into IDENTITY columns, SCOPE_IDENTITY will return the value of scope on which executed. For further reference, Visit SQL books online on MSDN Cheers ;)

[Jon G]

Thanks for your input guys. I got it working great. Jon G www.Gizmocoder.com