CODEWITHSUNDEEP
c#stored-proceduressql-server-2008-r2
user2709726
user2709726

Reputation:

SQL Server stored procedure expects parameter even though the parameter is provided

I am using MS Visual Studio 2010 and SQL Server 2008 R2.

In C# app I am trying to use a stored procedure but came accross a strange error.

Here is table definition:

create table bed_allotment
(
    bill_id bigint,
    bed_category varchar(50),
    for_days int
)

Stored procedure:

create procedure AddBed
(
    @bill_id bigint,
    @bed_category varchar(50),
    @for_days int
)
as
begin
    insert into bed_allotment 
    values (@bill_id, @bed_category, @for_days)
end

Code on button click:

private void button1_Click(object sender, EventArgs e)
{
    SqlConnection con = new SqlConnection(conString);
    SqlCommand AddBedCommand = new SqlCommand("AddBed", con);

    AddBedCommand.Parameters.AddWithValue("@bill_id", 1330);
    AddBedCommand.Parameters.AddWithValue("@bed_category", "ICCU");
    AddBedCommand.Parameters.AddWithValue("@for_days", 6);

    con.Open();
    AddBedCommand.ExecuteNonQuery();
    con.Close();
}

When I execute this, error occurs. It says

SQL procedure expects parameter '@bill_id' which was not provided

What's the mistake? Any suggestions will be a great help. Thank you!

Upvotes: 1

Views: 173

Answers (2)

AlokBhatt
AlokBhatt

Reputation: 515

Try this

AddBedCommand.CommandType = CommandType.StoredProcedure;
AddBedCommand.Parameters.Add("@bill_id", SqlDbType.Int).Value = 1330;
AddBedCommand.Parameters.Add("@bed_category", SqlDbType.VarChar).Value = "ICCU";
AddBedCommand.Parameters.Add("@for_days", SqlDbType.Int).Value = 6;

Upvotes: 0

Remus Rusanu
Remus Rusanu

Reputation: 294277

Change the AddBedCommand.CommandType to CommandType.StoredProcedure so that the request is an RPC request.

Upvotes: 1

Related Questions