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

Question

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?

Have you tried putting the @ symbol in front of the variable name? — Chris Gessler
– Chris Gessler, Commented Apr 16, 2012 at 22:55

Chris Gessler · Accepted Answer · 2012-04-17 00:25:43Z

5

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);

edited Apr 17, 2012 at 0:25

answered Apr 17, 2012 at 0:08

Chris Gessler

23.2k8 gold badges59 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Eric K Over a year ago

I could only get the shortened answer to work here. Nothing else seemed to function. Also, when specifying parameters, I had to specify them all, even if there were defaults within the stored procedure.

Tuan · Accepted Answer · 2012-04-16 22:55:43Z

0

It's probably expecting the parameter values:

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

answered Apr 16, 2012 at 22:55

Tuan

5,4221 gold badge24 silver badges17 bronze badges

2 Comments

AnonyMouse Over a year ago

What would be the point of creating the parameters though if then you just pass the values. Doesn't work anyway.

Tuan Over a year ago

You're right, I just thought you got the syntax mixed up. Are you using the DAAB here?

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related