CODEWITHSUNDEEP
c#asp.net-mvcstored-procedures
AnonyMouse
AnonyMouse

Reputation: 18630

MVC C# calling stored procedure with parameters the first parameter is not being recognised

Can someone please it explain this to me? I have this stored proc:

ALTER proc [dbo].[Invoice_GetHomePageInvoices] (
      @AreaIdList varchar(max)
,      @FinancialYearStartDate datetime = null
,      @FinancialYearEndDate datetime = null
) as

In code I'm trying to call it like this:

var areasString = new SqlParameter("AreaIdList", "1,2");
var startDate = new SqlParameter("FinancialYearStartDate", financialYear.StartDate);
var endDate = new SqlParameter("FinancialYearEndDate", financialYear.EndDate);

return _db.Database.SqlQuery<HomePageInvoice>("Invoice_GetHomePageInvoices", areasString, startDate, endDate);

I'm getting this error:

Procedure or function 'Invoice_GetHomePageInvoices' expects parameter '@AreaIdList', which was not supplied.

Say what? I've provided that parameter. Can someone please give me some insight into how to fix?

Upvotes: 1

Views: 11317

Answers (2)

Chris Gessler
Chris Gessler

Reputation: 23113

You need to call it like so:

var areasString = new SqlParameter("AreaIdList", "1,2");             
var startDate = new SqlParameter("FinancialYearStartDate", financialYear.StartDate);             
var endDate = new SqlParameter("FinancialYearEndDate", financialYear.EndDate);              
return _db.Database.SqlQuery<HomePageInvoice>("EXEC Invoice_GetHomePageInvoices @AreaIdList, @FinancialYearStartDate, @FinancialYearEndDate", areasString, startDate, endDate);

I believe you could shorten it up a bit too, like:

return _db.Database.SqlQuery<HomePageInvoice>("EXEC Invoice_GetHomePageInvoices {0}, {1}, {2}", "1,2", financialYear.StartDate, financialYear.EndDate);

Upvotes: 5

Tuan
Tuan

Reputation: 5412

It's probably expecting the parameter values: 

return _db.Database.SqlQuery<HomePageInvoice>("Invoice_GetHomePageInvoices", areasString.Value, startDate.Value, endDate.Value)

Upvotes: 0

Related Questions