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