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.

score 7 · Accepted Answer · answered Feb 04 '11 at 19:52

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

I've been using LINQ and Entity Framework way too long. I forgot to specify the commandtype. I'm an idiot, Lol. ;) — Andy Evans, Feb 04 '11 at 19:58

score 4 · Answer 2 · answered Feb 04 '11 at 19:52

4

When naming your parameters you need to put the @ in

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

answered Feb 04 '11 at 19:52

Matthew Steeples

7,858
4
34
49

score 2 · Answer 3 · answered Feb 04 '11 at 19:53

2

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

answered Feb 04 '11 at 19:53

Mark Avenius

13,679
6
42
50

score 1 · Answer 4 · edited Jun 06 '12 at 07:14

1

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

edited Jun 06 '12 at 07:14

Perception

79,279
19
185
195

answered Jun 06 '12 at 07:08

Nalan Madheswaran

10,136
1
57
42

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

4 Answers4