SQL Server Scope_identity stored procedure returning null in C#

I have a stored procedure called DvdInsert which looks like this:

IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.ROUTINES
          WHERE ROUTINE_NAME = 'DvdInsert')
    DROP PROCEDURE DvdInsert
GO

CREATE PROCEDURE DvdInsert 
     (@RatingName char(10),
      @FName nvarchar(30),
      @LName nvarchar(30),
      @Title nvarchar(125),
      @ReleaseYear int,
      @Notes nvarchar(150),
      @DvdId int OUTPUT)
AS
BEGIN
    INSERT INTO Director (FName, LName)
    VALUES (@FName, @LName)

    INSERT INTO Dvd (DirectorId, RatingId, Title, ReleaseYear, Notes)
    VALUES ((SELECT DirectorId
             FROM Director
             WHERE FName = @FName AND LName = @LName),
            (SELECT RatingId
             FROM Rating
             WHERE RatingName = @RatingName), @Title, @ReleaseYear, @Notes)

    SET @DvdId = CAST(SCOPE_IDENTITY() AS INT);
END
GO

It should return the id number, but in Visual Studio 2017 I have the code:

public int Insert(DvdItem dvdItem)
{
    using (var cn = new SqlConnection(Settings.GetConnectionString()))
    {
        SqlCommand cmd = new SqlCommand("DvdInsert", cn);
        cmd.CommandType = CommandType.StoredProcedure;

        SqlParameter param = new SqlParameter("@DvdId", SqlDbType.Int);
        param.Direction = ParameterDirection.Output;
        cmd.Parameters.Add(param);

        string[] names = dvdItem.Director.ToString().Trim().Split(new char[] 
        { ' ' }, 2);

        if (names.Length == 1)
        {
            cmd.Parameters.AddWithValue("FName", "");
            cmd.Parameters.AddWithValue("LName", names[0]);
        }
        else
        {
            cmd.Parameters.AddWithValue("FName", names[0]);
            cmd.Parameters.AddWithValue("LName", names[1]);
        }

        cmd.Parameters.AddWithValue("RatingName", dvdItem.Rating);
        cmd.Parameters.AddWithValue("Title", dvdItem.Title);
        cmd.Parameters.AddWithValue("ReleaseYear", dvdItem.RealeaseYear);
        cmd.Parameters.AddWithValue("Notes", dvdItem.Notes);

        cn.Open();

        int i = 0;
        object a = cmd.ExecuteScalar();

        if (a != null)
            i = (int)a;

        if (cn.State == System.Data.ConnectionState.Open)
            cn.Close();

        return i;
    }
}

I have an Nunit test to verify the functionality but in debug mode I get returned value 0 instead of 4

My test code:

[Test]
public void CanAddDvd()
{
        DvdItem dvdItem = new DvdItem();
        var repo = new DvdRepositoryADO();

        dvdItem.Rating = "R";
        dvdItem.Director = "Hello";
        dvdItem.Title = "World";
        dvdItem.RealeaseYear = "2004";
        dvdItem.Notes = "TESTING";

        repo.Insert(dvdItem);

        Assert.AreEqual(4, dvdItem.DvdId);
}

Before I added:

int i = 0;
object a = cmd.ExecuteScalar();

if (a != null)
    i = (int)a;

if (cn.State == System.Data.ConnectionState.Open)
    cn.Close();

I was getting a null reference exception here:

object a = cmd.ExecuteScalar();

My table in SQL Server looks as follows:

CREATE TABLE Dvd 
(
    DvdId INT NOT NULL IDENTITY(1,1),
    DirectorId INT NOT NULL,
    RatingId INT NOT NULL,
    Title NVARCHAR(125) NOT NULL,
    ReleaseYear int NOT NULL,
    Notes VARCHAR(150) NULL,

    CONSTRAINT PK_Dvd_DvdId PRIMARY KEY (DvdId),
    CONSTRAINT FK_Dvd_DirectorId
        FOREIGN KEY (DirectorId) REFERENCES Director(DirectorId),
    CONSTRAINT FK_Dvd_RatingId
        FOREIGN KEY (RatingId) REFERENCES Rating(RatingId)
)

I don't see why I am not getting the return value from the stored procedure. Any ideas? I am a beginner, so if willing, please break down your explanation.

Thank you in advance for helping.

I have a screenshot of the error I receive in postman if that helps click here

My POST code:

[Route("dvd/")]
[AcceptVerbs("POST")]
public IHttpActionResult Add(DvdItem dvdItem)
{
        repo.Insert(dvdItem);
        return Created($"dvd/{dvdItem.DvdId}", dvdItem);
}

I came across an error message while debugging that says, "System.Data.SqlClient.SqlException: 'Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. The statement has been terminated.'"

here is the image of my VS in debug mode:

This is just a shot in the dark, but could my issue be with my subquery in which I am inserting a new director?

Solution

Sounds like your problem is a little deeper that just the exception and more about your method.

You're inserting into the Director table without first checking if that direct already exists. This can create duplicates which means your first sub query could return multiple results. Maybe it's appropriate to change the query to check if the direct exists, if it does, then use the ID, else insert and get it from scope_identity()
Instead of passing a rating name to the procedure, it would be much better to pass the rating ID. This means the 2nd subquery would not be needed and by looking up by ID instead of the rating name, would also be a lot more efficient.

So it looks like you have a combination of issues, my first answer will address the return value not being returned and this answer should help you solve the exception and also help you create a much more efficient solution.