0

I'm trying to call the below stored procedure to display a list of products with a certain product type.

CREATE PROCEDURE filterListSP
    @productType varchar (25)
AS
BEGIN
    SET NOCOUNT ON;

    SELECT 
        ProductId, Description, Price 
    FROM
        tblProduct 
    WHERE
        ProductType = @productType
END
GO

And this is the code calling the SP:

dataGridView1.DataSource = naafiDbEntity.Database.SqlQuery<tblProductType>
    ("filterListSP @productType", cboFilter.SelectedValue).ToList();

However, when I run this code I get the following error:

Must declare the scalar variable "@productType"

Can anyone tell me what I'm missing?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
S edwards
  • 163
  • 1
  • 1
  • 8
  • 6
    There is a similar question here: http://stackoverflow.com/questions/4873607/how-to-use-dbcontext-database-sqlquerytelementsql-params-with-stored-proced – Marco Hurtado Nov 23 '15 at 22:59
  • 1
    It took me (and I guess @MarcoHurtado) about 10 seconds to Google this and reach the same question, how come you didn't find it? – DavidG Nov 23 '15 at 23:00
  • I apologise i'm new to coding and have tried searching for the answer on google, however from that other question I now receive this error: The data reader is incompatible with the specified 'NaafiDatabaseModel.tblProductType'. A member of the type, 'ProductType', does not have a corresponding column in the data reader with the same name. – S edwards Nov 23 '15 at 23:07
  • Well I suggest that your model class doesn't match the return type of the stored procedure. – DavidG Nov 23 '15 at 23:10

2 Answers2

0

define in a function in your dbcontext like this. then make the call through this way eg: dbcontext.SP_Insert(data,value); 

            public virtual int SP_Insert(string productType, ObjectParameter retValue)
            {
                var xmlDataParameter = xmlData != null ?
                    new ObjectParameter("productType", xmlData) :
                    new ObjectParameter("productType", typeof(string));

                return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction("SP_Insert", xmlDataParameter, retValue);
            }

Here the stored procedure is SP_Insert

Noxious Reptile
  • 838
  • 1
  • 7
  • 24
0

Thanks all I managed to work it out eventually.

This is what I amended my code to

    string result = cboFilter.SelectedValue.ToString();
    dataGridView1.DataSource = naafiDbEntity.filterListSP(result);
S edwards
  • 163
  • 1
  • 1
  • 8