Must declare the scalar variable "@employeeid" in stored procedure insert in ASP.NET MVC 5

Question

I want to insert data into a database using a stored procedure, Entity Framework in ASP.NET MVC 5. It works in SQL Server, but when I execute the procedure but in Visual Studio while inserting, I get that error.

My controller code is:

[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult SaveEntitlement(Entitlement entment)
{
    if (!ModelState.IsValid)
    {
        var viewmodel = new EntitlementViewModel(entment);
        return View("EntitlementIndex", viewmodel);
    }

    if (entment.EntitlementId == 0)
    {
        var courseList = _dbContext.Entitlement.SqlQuery ("exec APPLIEDDAYS @employeeid,@LeaveTypeId,@LeavePeriodId,@startdate,@enddate", entment.EmployeeId,entment.LeaveTypeId,entment.LeavePeriodId,entment.FromDate.ToShortDateString(),entment.UptoDate.ToShortDateString()).ToList<Entitlement>();
        ////_dbContext.Entitlement.Add(entment);
        _dbContext.SaveChanges();

        TempData["msg"] = "Record Saved Successfully!";
    }

    return RedirectToAction("EntitlementIndex", "LeaveSetup");
}

Error is:

(OT) I hope your `@startdate` and `@enddate` are dates. Then supply a DateTime value, not a string. — Hans Keﬆing, Oct 25 '17 at 15:05
Wild guess: the [MSDN example](https://msdn.microsoft.com/en-us/library/jj592907.aspx) has `@p0` as parameter. Try with `@p0`, `@p1` etc as param names, — Hans Keﬆing, Oct 25 '17 at 15:09
See also https://stackoverflow.com/questions/4873607/how-to-use-dbcontext-database-sqlquerytelementsql-params-with-stored-proced — Hans Keﬆing, Oct 25 '17 at 15:11

score 0 · Answer 1 · answered Oct 25 '17 at 14:14

I believe that happens when one of the parameters you are using (employeeid in this case) is null, not "null" as in string. Before calling the procedure you can assign proper values to your parameters. So assuming entment.EmployeeId is a type of int?:

  if (entment.EntitlementId == 0)
    {
        var param1 = !entment.EmployeeId.HasValue ? "null" : entment.EmployeeId.ToString();

        var courseList = _dbContext.Entitlement.SqlQuery ("exec APPLIEDDAYS @employeeid,@LeaveTypeId,@LeavePeriodId,@startdate,@enddate",
 param1,entment.LeaveTypeId,entment.LeavePeriodId,entment.FromDate.ToShortDateString(),entment.UptoDate.ToShortDateString()).ToList<Entitlement>();
        ////_dbContext.Entitlement.Add(entment);
        _dbContext.SaveChanges();

        TempData["msg"] = "Record Saved Successfully!";
    }

PS: Not sure if this is the right way to do it but it should work.

This would cause the sproc to treat `"null"` as a string, not as `null`. — , Oct 25 '17 at 14:20
Ye my bad i thought it was the same as in using a _dbContext.Database.Connection.CreateCommand(), this is not a valid answer then. — etuncoz, Oct 25 '17 at 14:34
You have the right idea. Just need to use a `SqlParameter` with the value `DbNull.Value` See https://stackoverflow.com/questions/4555935/assign-null-to-a-sqlparameter — , Oct 25 '17 at 14:37

score 0 · Answer 2 · answered Apr 19 '20 at 11:17

I think you should declare parameters for each parameter in your stored procedure like below:

SqlParameter param1 = new SqlParameter("@employeeid", entment.EmployeeId);
SqlParameter param2 = new SqlParameter("@LeaveTypeId", entment.LeaveTypeId);
SqlParameter param3 = new SqlParameter("@LeavePeriodId", entment.LeavePeriodId);
SqlParameter param4 = new SqlParameter("@startdate", entment.FromDate);
SqlParameter param5 = new SqlParameter("@enddate", entment.UptoDate);

and then

var courseList = _dbContext.Entitlement.SqlQuery ("exec APPLIEDDAYS @employeeid,@LeaveTypeId,@LeavePeriodId,@startdate,@enddate", param1, param2, param3, param4, param5).ToList<Entitlement>();

score 0 · Answer 3 · answered Feb 21 '23 at 01:09

You should use Entitlement in front of SqlQuery not ToList()

var courseList = _dbContext.Entitlement.SqlQuery<Entitlement>("sql").ToList()

This is better that you use db.Database.SqlQueryRaw

using (var db = new _dbContext())
    return db.Database.SqlQueryRaw<Entitlement>($"select top 1 username from tbl_people where id = '{userID}'").ToList().FirstOrDefault();

Must declare the scalar variable "@employeeid" in stored procedure insert in ASP.NET MVC 5

3 Answers3