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

Morello

did you set the ID parameter as out when creating the stored procedure? (ON THE DATABASE; NOT THE CODE) – Lallation

Answer 1

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....

Excise

Answer 2

this one solve my problem may be it may helpful

cmd.CommandType = CommandType.StoredProcedure;

Witha

Answer 3

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

Novelette

I did it in StoredProcedure :) – Morello

Answer 4

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.

Layout

Answer 5

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

Hesta

Answer 6

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

Nashville