CODEWITHSUNDEEP
c#.netdatabasesqlcommand
Azhar
Azhar

Reputation: 20680

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

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

Upvotes: 21

Views: 35279

Answers (6)

Евгений Елисеев
Евгений Елисеев

Reputation: 510

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

Upvotes: 0

Altamash Shaikh
Altamash Shaikh

Reputation: 1

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

Upvotes: 0

siva
siva

Reputation: 67

this one solve my problem may be it may helpful

cmd.CommandType = CommandType.StoredProcedure;

Upvotes: 3

Jorge Corradi
Jorge Corradi

Reputation: 41

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.

Upvotes: 2

Mujtaba Hassan
Mujtaba Hassan

Reputation: 2493

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

Upvotes: 2

marc_s
marc_s

Reputation: 754973

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

Upvotes: 47

Related Questions