How to fix executescalar not having an output

46 Views Asked by At

I'm using asp.net MVC to make an app which calculates if multiple political parties can work together to form a legitimate coalition. (Have a majority). When i make a coalition i need to return the id of the coalition, which is an auto increment. I'm trying to use the ExecuteScalar()-function to output the ID. In a stored procedure i scope out the Identity.

Also tried Output Inserted.ID

Code in which i try to return the ID:

public int AddNewCoalition(Coalition coalition)
{
    try
    {
        _conn.Open();
        using (SqlCommand cmd = new SqlCommand("MakeCoalition", _conn))
        {
            cmd.CommandType = CommandType.StoredProcedure;
            cmd.Parameters.AddWithValue("@CoalitionName", SqlDbType.NChar).Value = coalition.CoalitionName;
            cmd.Parameters.AddWithValue("@DateOfFormation", SqlDbType.DateTime).Value = coalition.DateOfFormation;
            Int32 result = (Int32)cmd.ExecuteScalar();
            return (int)result;
        }
    }
    catch (Exception)
    {

        throw;
    }
    finally
    {
        _conn.Close();
    }
}

Code where I try to execute the method:

    [HttpPost]
    [ValidateAntiForgeryToken]
    public ActionResult CreateNewCoalition(CoalitionViewModel coalition)
        {
            try
            {
                _coalitionLogic.AddNewCoalition(new Coalition(coalition.CoalitionName, coalition.DateOfFormation));

                return RedirectToAction("AddPoliticalPartiesToCoalition", coalition);
            }
            catch (Exception)
            {
                return View("../Shared/Error");
            }
        }

Stored Procedure to scope out the identity:

CREATE PROCEDURE [dbo].[MakeCoalition]
    @CoalitionName NVarchar(255),
    @DateOfFormation DateTime
AS
BEGIN
INSERT INTO [Coalition]
([Name], [DateOfFormation])
VALUES
(@CoalitionName, @DateOfFormation)
SELECT CAST(scope_identity() AS int)
END

Coalition Table:

CREATE TABLE [dbo].[Coalition] (
    [CoalitionId]     INT            IDENTITY (1, 1) NOT NULL,
    [Name]            NVARCHAR (255) NOT NULL,
    [PrimeMinisterId] INT            NULL,
    [Seats]           INT            NULL,
    [DateOfFormation] DATETIME       NOT NULL,
    [EnoughSeats]     BIT            NULL,
    PRIMARY KEY CLUSTERED ([CoalitionId] ASC)
);

Expected the output to be Id of the newly made coalition, but keeps returning 0

0

There are 0 best solutions below