expects parameter '@ID', which was not supplied?

Question

I am sending ID as outparameter but its giving error

System.Data.SqlClient.SqlException: Procedure or function 'usp_ClientHistoryItem' expects parameter '@ID', which was not supplied.

Code

 using (SqlCommand cmd = new SqlCommand("dbo.usp_ClientHistoryItem", conn))
 {
      SqlParameter parameterID = new SqlParameter("@ID", oReservation.Id);
      parameterID.Direction = ParameterDirection.Output;
      cmd.Parameters.Add(parameterID); 

      cmd.Parameters.Add(new SqlParameter("@PhoneNo", oReservation.ClientPhone));
      cmd.Parameters.Add(new SqlParameter("@UserId", oReservation.UserID));
      cmd.Parameters.Add(new SqlParameter("@Description", oReservation.Description));
      cmd.Parameters.Add(new SqlParameter("@TestId", oReservation.TestId));
      cmd.Parameters.Add(new SqlParameter("@StartDate", oReservation.StartDate));

      cmd.ExecuteNonQuery();

      returnValue = Convert.ToInt32(cmd.Parameters["@ID"].Value);

      return returnValue;
}

did you set the ID parameter as out when creating the stored procedure? (ON THE DATABASE; NOT THE CODE) — jclozano, Mar 21 '12 at 06:21

score 46 · Accepted Answer · answered Mar 21 '12 at 06:22

You seem to be calling a stored procedure - yet you've never defined your SqlCommand to be a stored procedure:

using (SqlCommand cmd = new SqlCommand("dbo.usp_ClientHistoryItem", conn))
{
    cmd.CommandType = CommandType.StoredProcedure;  // add this line to tell ADO.NET it's a stored procedure!!

If you forget that line, then ADO.NET will try to interpret your stuff as an ad-hoc SQL statement....

score 3 · Answer 2 · answered Feb 01 '14 at 09:40

3

this one solve my problem may be it may helpful

cmd.CommandType = CommandType.StoredProcedure;

answered Feb 01 '14 at 09:40

siva

67
5

score 2 · Answer 3 · answered Dec 11 '13 at 12:40

2

Hy guys.

You have to set the property CommandType for the Command to StoredProcedure if that's the case. Otherwise it woun't detect the parameters.

answered Dec 11 '13 at 12:40

Jorge Corradi

21
4

score 2 · Answer 4 · answered Mar 21 '12 at 06:50

2

Your ID parameter in the stored procedure must be set as OUTPUT parameter. You are just setting it in code not in stored procedure.

answered Mar 21 '12 at 06:50

Mujtaba Hassan

2,495
2
20
29

I did it in StoredProcedure :) – Azhar Mar 21 '12 at 07:18

score 0 · Answer 5 · edited Oct 01 '16 at 07:40

One other reason this error is thrown is when the variable names don't match in your stored procedure and code because the code fails to find the parameter to which the value must be passed. Make sure they match:

Stored procedure:

create procedure getEmployee
    @ID 
as
Begin
    select * 
    from emp 
    where id = @ID
End

Code:

SqlParameter p = new SqlParameter("@ID", id);
cmd.Parameter.Add(p);

The parameter @ID must match in both code and stored procedure

score 0 · Answer 6 · answered May 04 '20 at 10:35

If you use dapper, you can use this construction

int id = 1;

var parameters = new DynamicParameters();

parameters.Add("@id", id, DbType.Int32, ParameterDirection.Input);

string sqlQuery = "[dbo].[SomeStoredProcedure]";

using (IDbConnection db = new SqlConnection(ConnectionString))
{
    var result = await db.QueryAsync<SpResult>(sqlQuery, parameters, commandType: CommandType.StoredProcedure);  
}

expects parameter '@ID', which was not supplied?

6 Answers6

Linked