Not getting return value from SQL Stored Procedure

Hi Experts,

I'm having trouble getting a return value back from a stored procedure.  I thought I previously had this working, and I don't understand why it's not now.  Attached is the relevant code.

Every time I run the code and step through the program I get an error in the attached database method on the following line of code:

result = command.ExecuteScalar().ToString();

Giving the error saying 'Object reference not set to an instance of an object.' - referring to the line of code trying to perform the ToString() functionality on a null object, because the ExecuteScalar() doesn't hold a value.

I know the stored procedure name and parameters I'm passing into the DB method are correct because the stored procedure is actually performing all the inserts correctly.  I just can't get it to return the value when it's done processing.

Can you see what I'm doing wrong?

/*** Stored Procedure ***/
ALTER PROCEDURE [dbo].[sp_NewProgram]
    -- Parameters:
    @SetID numeric(18,0),
    @Number int,
    @Name varchar(30),
    @Type varchar(10)
AS

DECLARE
    -- Local Variables:
    @ProgramID numeric(18,0)

BEGIN
	
    SET NOCOUNT ON;
	
    -- Insert new Program here:
    INSERT INTO [dbo].[Program]
    VALUES (@SetID, @Number, @Name, -1, -1, GETDATE(), '');

    -- Get the ID of the newly created program:
    -- SET @ProgramID = @@IDENTITY;
    SET @ProgramID =
    (
	SELECT [ID] FROM [dbo].[Program]
	WHERE [SetID] = @SetID
	AND [Number] = @Number
	AND [Name] = @Name
    );
	
    -- Insert 1 new stage with default values for the newly created program:
    INSERT INTO [dbo].[Stage]
    VALUES (@ProgramID, 1, @Type, '', 0, 0, 0, 0, 0, 'NONE', 0, 'AUTO', 0, 0, '', 0, '', '<0>', 0);
	
    RETURN @ProgramID;
	
    SET NOCOUNT OFF;
END


/*** C# Database Class Method ***/
public static String ExecuteScalar(String StoredProcedureName, SqlParameter[] Parameters)
{
    string result = string.Empty;
    try
    {
        connnection = new SqlConnection(connString);
        command = new SqlCommand(StoredProcedureName, connnection);
        command.CommandType = CommandType.StoredProcedure;

        foreach (SqlParameter parameter in Parameters)
            command.Parameters.Add(parameter);

        command.Connection.Open();
        result = command.ExecuteScalar().ToString();
    }
    catch (Exception ex)
    {
        string error = ex.ToString();
    }
    finally
    {
        command.Connection.Close();
        command.Dispose();
        connnection.Dispose();
    }
    return result;
}

Select allOpen in new window