'Procedure Has Too Many Arguments' Error Although I Have Only Two

Question

I checked the other posts related to this problem but could not find any answers.

I have a post action method for my hotel api and in the Hotel Repository I have this CreateHotel method below. I just enter two arguments and there's two arguments in the stored procedure too but I get this error: System.Data.SqlClient.SqlException (0x80131904): Procedure or function CreateHotel has too many arguments specified.

public async Task<Hotel> CreateHotel(Hotel hotel)
    {
        var sql = "CreateHotel";

        var newHotel = new Hotel()
        {
            Name = hotel.Name,
            City = hotel.City
        };

        using (var connection = new SqlConnection(CONNECTION_STRING))
        {
            return await connection
                .QueryFirstAsync<Hotel>(sql, newHotel, commandType: CommandType.StoredProcedure);
        }
    }

here is the Hotel entity in case you may want to see it:

 public class Hotel
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

    [StringLength(50)]
    [Required]
    public string Name { get; set; }

    [StringLength(50)]
    [Required]
    public string City { get; set; }
}

And that's the CreateHotel SP:

CREATE PROCEDURE CreateHotel @Name CHAR(50), @City CHAR(50)
AS
INSERT INTO Hotels (Name, City)
OUTPUT inserted.*
VALUES (@Name, @City)
GO

One interesting thing I can tell you is that the Name in 'INSERT INTO Hotels (Name, City)' is automatically grayed out after I type it. But it is detected that it's the name column when I hover over it.

Do you know what causes the error?

Answer 1

Your Hotel entity has an Id property, and that will automatically be initialized to 0, and thus you're passing 3 parameters to Dapper and the Stored Procedure.

Instead, you can use an anonymous arguments to pass your parameters.

using (var connection = new SqlConnection(CONNECTION_STRING))
{
    return await connection
            .QueryFirstAsync<Hotel>(sql, new { Name = hotel.Name, City = hotel.City} , commandType: CommandType.StoredProcedure);
}