SQL Server Scope_identity stored procedure returning null in C#

Question

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?

Answer 1

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

1. 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()

2. 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.

Answer 2

I believe this may be my issue:

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)

The select DirectorId subquery is returning multiple values as is the RatingId select statement which would explain the exception.

I still need to test this theory, though

UPDATE...

So it turns out my theory was correct

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

adding TOP 1 to the select statement only returns 1 value. This may not be the best in terms of coding best practices, but with a very basic understanding of SQL its the best solution I have right now.

Answer 3

After you have called ExecuteNonQuery() method, you then need to get the value of the ouput parameter and read it like this:

int dvdID = 
     Convert.ToInt32(cmd.Parameters["@DvdId"].Value);

Or assign it to dvdItem.DvdId = dvdId;.

By the way your test is an integration test not a unit test. Even for an integration test it is very fragile because the dvd id will not always be 4 so it will fail. Nonetheless it is better than manual testing using the debugger.

Answer 4

If I remember correctly, if you're trying to return a value via a parameter then you need to use an output parameter in your SqlCommand and read it after executing the query. If you want to use ExecuteScalar, then the last line of your SQL needs to just be "SELECT SCOPE_IDENTITY()" THEN THAT WOULD BE IN OUR "object a". I've not run this up to try it, so please do let me know if that does the trick.