Jerry,

This would work only if you were using SCOPE_IDENTITY in a Stored Proc. From BOL:

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.

I think perhaps you might want to use IDENT_CURRENT instead, in this situation ... but that has pitfalls if you have multiple users inserting multiple rows in the same table concurrently. I'd recommend using a Stored Proc if possible.

~~Bonnie


>I am Inserting a record into a SQL Server table and I want to return the value created by the Identity field of the table.
>
>I am using the "SELECT SCOPE_IDENTITY() AS new_id" after the insert but cannot get this to work.
>
>My following code shows how I am Inserting the record and try to return the Identity value. The error I get is "Specified Cast is not valid".
>
>How can I correct my code or is there a better way.
>
>

>string lc_conn = "Integrated Security=SSPI;" +
>	"Initial Catalog=mycatalog;";
>
>SqlConnection myConnection = new SqlConnection(lc_conn);
>string myInsertQuery = "INSERT INTO roster (ro_name) Values('Name value')";
>SqlCommand myCommand = new SqlCommand(myInsertQuery);
>myCommand.Connection = myConnection;
>myConnection.Open();
>myCommand.ExecuteNonQuery();
>
>SqlCommand ocommand = new SqlCommand();
>ocommand.Connection = myConnection;
>ocommand.CommandText = "SELECT SCOPE_IDENTITY() AS new_id";
>ocommand.CommandType = CommandType.Text;
>int count = (int)ocommand.ExecuteScalar(); //"Specified Cast is not valid"
>
>myCommand.Connection.Close();
>
>

>
>Thanks,
>
>Jerry