Procedure or function ' ' expects parameter ' ' which is not supplied [duplicate]

Question

I've annoyingly got what appears to be a very 'popular' error. However, in my case I'm supplying the expected parameter and it definitely has a value, so I'm stumped. Here's my code:

public static DataTable MyDataTable(string pd, bool showAll)
{
    DataTable results = new DataTable("PD results");

    string Conn = ConfigurationManager.ConnectionStrings["MyConnectionString"].ConnectionString;

    using (SqlConnection connection = new SqlConnection(Conn))
    {
        SqlCommand cmd = new SqlCommand("dbo.MyProcedure", connection);

        SqlParameter pdParam = new SqlParameter("@i_user", SqlDbType.VarChar);
        pdParam.Value = pd;
        cmd.Parameters.Add(pdParam);

        if (showAll) 
            cmd.Parameters.AddWithValue("@i_ShowALL", (int)1);
        else 
            cmd.Parameters.AddWithValue("@i_ShowALL", (int)0);

        SqlDataAdapter da = new SqlDataAdapter(cmd);

        try
        {
            da.Fill(results);
        }
        catch (Exception ex) 
        {
            string error = ex.Message;
        }
     }

     return results;
}

I've stepped through the code and the error occurs with parameter @i_user. However, when I'm adding the parameter it has a value and I confirmed this when looking at the SqlDataAdapter > SelectCommand >Parameters > Non-Public members > items > [0] {@i_user} > base > value.

Despite having the parameter supplied with a varchar value I get the error

Procedure or function 'dbo.MyProcedure' expects parameter '@i_user' which is not supplied

What am I doing wrong?

Answer 1

The reason of the error is that SqlCommand class expects a plain text SQL. When assigning stored procedure name ("dbo.MyProcedure" in your case) add

  ... 
  // Do not forget to Dispose IDisposable instances
  using (SqlCommand cmd = new SqlCommand("dbo.MyProcedure", connection)) {
    // cmd.CommandText is a stored procedure name, not a plain text SQL 
    cmd.CommandType = CommandType.StoredProcedure;
     ...
  }