Procedure or function [sproc_name] expects parameter '@materials', which was not supplied

Question

This is driving me nuts ;)

I have this stored procedure ...

ALTER PROCEDURE [dbo].[sproc_FindFoundries] 
    (@materials varchar(1000),
     @capabilities varchar(1000))
AS
BEGIN

 /* insert SQL code here */

END

The procedure accepts two comma delimited strings. In my application I have the following code.

BCDataContext db = new BCDataContext();
SqlParameter prmMaterials = new SqlParameter("materials", SqlDbType.VarChar, 1000);
prmMaterials.Value = materialList;
SqlParameter prmCapability = new SqlParameter("capabilities", SqlDbType.VarChar, 1000);
prmCapability.Value = capabilityList;

SqlConnection cn = new SqlConnection(db.Connection.ConnectionString);
SqlCommand cmd = new SqlCommand("sproc_FindFoundries", cn);
cmd.Parameters.Add(prmMaterials);
cmd.Parameters.Add(prmCapability);

SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);

When I execute the code, I get the error

Procedure or function [sproc_name] expects parameter '@materials', which was not supplied.

when I try fill the dataset. When testing I have verified that both parameters contain data and are not null. Is there something I've missed? A second pair of eyes would be greatly appreciated.

Thanks.

Answer 1

Use @materials, @capabilities as parameters' name:

using (BCDataContext db = new BCDataContext())
using (SqlConnection connection =  new SqlConnection(db.Connection.ConnectionString))
using (SqlCommand command = connection.CreateCommand())
{
    command.CommandText = "sproc_FindFoundries";
    command.CommandType = CommandType.StoredProcedure;

    command.Parameters.Add("@materials", SqlDbType.VarChar, 1000).Value = materialList;
    command.Parameters.Add("@capabilities", SqlDbType.VarChar, 1000).Value =  capabilityList;

    DataSet ds = new DataSet();
    using (SqlDataAdapter da = new SqlDataAdapter(command))
    {
        da.Fill(ds);
    }
}

Answer 2

When naming your parameters you need to put the @ in

SqlParameter prmMaterials = new SqlParameter("@materials", SqlDbType.VarChar, 1000)

Answer 3

You are calling your parameters "materials" and "capabilities" instead of "@materials" and "@capabilities"

Answer 4

I tried like this, Its worked for me:

int deal_id = 25;
_dbContext.Database.ExecuteSqlCommand("exec sp_getdeal @deal_id={0}", deal_id);

My procedure is like this:

ALTER PROCEDURE [dbo].[sp_getdeal]
(
    @deal_id INTEGER
)
AS
BEGIN