Ado.net ExecuteScalar() returning null

Question

I am executing a stored procedure in c# (through vs2008) using ado.net with an ExecuteScalar command. The stored proc returns the pkey of the new record entered, but ExecuteScalar is returning null. I look in the database and a record has indeed been added. I could use an output parameter to get the value, but then I won't know why this didn't work.

When I execute the sp in ssms, the pkey is returned.

What am I doing wrong?

Here is the C# code:

  public int SaveNewPerson(EPerson ePerson)
    {
        int newPersonPkey;
        SqlConnection cn = new SqlConnection(cnn.PersonData);
        using (cn)
        {
            try
            {
                SqlCommand cmd = new SqlCommand();
                cmd.Connection = cn;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "People.dbo.AddNewPerson"; 
                cmd.Parameters.Add("@LastName", SqlDbType.VarChar, 150).Value = ePerson.LastName;
                cmd.Parameters.Add("@FirstName", SqlDbType.VarChar, 150).Value = ePerson.FirstName;
                cn.Open();
                object result = cmd.ExecuteScalar(); 
                newPersonPkey = int.Parse(result.ToString());
                cn.Close();
            }
            catch (Exception e)
            {
                // call error method
                throw new Exception(e.Message + " save new Person error ");
            }
        }
        return newPersonPkey;
    }

And this is the sp:

 PROCEDURE [dbo].[AddNewPerson]
            @FirstName varchar(50)
           ,@LastName varchar(50) 
AS
BEGIN
    SET NOCOUNT ON;

INSERT INTO [People].[dbo].[Persons]
           (
            [FirstName]
           ,[LastName]
            )
     VALUES
           (
            @FirstName 
           ,@LastName 
           ) 
declare @persons_PKey int 
set @persons_PKey = @@IDENTITY 
return @persons_PKey

end 

Answer 1

The ExecuteScalar method returns the first field of the first record of the result, but as your query doesn't produce a result, it will return null.

You can either select the value instead of returning it from the stored procedure, or add a parameter with the direction set to ParameterDirection.ReturnValue to catch what the stored procedure returns.

Answer 2

Try changing the Stored Procedure to use a Select Statement to return the identity instead of using a return like this:

SELECT CAST(scope_identity() AS int)

Thus changing your procedure to:

 PROCEDURE [dbo].[AddNewPerson]
            @FirstName varchar(50)
           ,@LastName varchar(50) 
AS
BEGIN
    SET NOCOUNT ON;

INSERT INTO [People].[dbo].[Persons]
           (
            [FirstName]
           ,[LastName]
            )
     VALUES
           (
            @FirstName 
           ,@LastName 
           ) 
SELECT CAST(scope_identity() AS int)
end

From the documentation of the ExecuteScalar() on MSDN it says that it will return the first column of the first row in the result set or null otherwise if the result set is empty.