EF Core: Parameterized Query Expects the parameter '@responseMessage' which was not supplied

c# entity-framework-core

Question

I have the following stored procedure that I'm trying to call from my asp.net core MVC web application:

CREATE PROCEDURE [dbo].[uspLogin]
(
    @pUserName VARCHAR(150),
    @pPassword VARCHAR(150),
    @responseMessage NVARCHAR(250)='' OUTPUT
)
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON

    DECLARE @userID INT

    IF EXISTS (SELECT TOP 1 ID FROM dbo.[User] WHERE UserName = @pUserName)
    BEGIN
        SET @userID = (SELECT ID FROM dbo.[User] WHERE UserName = @pUserName AND PasswordHash=HASHBYTES('SHA2_512', @pPassword+CAST(Salt AS NVARCHAR(36))))

        IF(@userID IS NULL)
            SET @responseMessage='Incorrect password'
        ELSE
            SET @responseMessage='Success'
    END
    ELSE
        SET @responseMessage = 'Invalid Login'
END

I can call this procedure perfectly fine from within my DB, however I'm trying to call it with this code:

[HttpPost]
public IActionResult Index(User user)
{
    var userName = user.UserName;
    var password = user.Password;
    var message = new SqlParameter
    {
        ParameterName = "responseMessage",
        SqlDbType = SqlDbType.NVarChar,
        Size = 250,
        Direction = ParameterDirection.InputOutput,
    };
    _context.Database.ExecuteSqlCommand("dbo.uspLogin @p0, @p1, @responseMessage", userName, password, message);

    return RedirectToAction("Success", "Home", new { email = message.SqlValue });
}

When testing out this action, I get the following error:

SqlException: The parameterized query '(@p0 nvarchar(4000),@p1 nvarchar(4000),@responseMessage nvarchar' expects the parameter '@responseMessage', which was not supplied.

What is the right way to get the output of this procedure from EF Core?

1
1
1/24/2019 2:02:19 AM

Accepted Answer

Change param direction,

var message = new SqlParameter
{
    ParameterName = "responseMessage",
    SqlDbType = SqlDbType.NVarChar,
    Size = 250,
    Direction = ParameterDirection.Output,
};

And execution line as below and try,

_context.Database.ExecuteSqlCommand("dbo.uspLogin @p0, @p1, @responseMessage OUT", userName, password, message);
1
1/24/2019 1:15:02 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow