How to fix executescalar not having an output

Question

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

Without the stored procedure code is a bit difficult to say what's wrong — Steve, Jun 23 '19 at 20:45
Possible duplicate of [Execute Insert command and return inserted Id in Sql](https://stackoverflow.com/questions/18373461/execute-insert-command-and-return-inserted-id-in-sql) — blenderfreaky, Jun 23 '19 at 20:48
@blenderfreaky already looked at that, could not find what was different about my code — Jesse Oosterwijk, Jun 23 '19 at 20:55
From what I can see, I'd guess it's the query. Have you tried the third answer? It assigns a parameter in the query. — blenderfreaky, Jun 23 '19 at 21:00
The first parameter required by the SP is a NVARCHAR but you pass a NCHAR. — Steve, Jun 23 '19 at 21:05
Also. AddWithValue second parameter is not the type of the parameter but the Value. Better use Add instead of AddWithValue — Steve, Jun 23 '19 at 21:06

How to fix executescalar not having an output

0 Answers0