Procedure or function expects a parameter that is not supplied
I got annoyed with what seems to be a very "popular" bug. However, in my case I am supplying the expected parameter and it definitely matters, so I am 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 went through the code and got an error with the parameter @i_user
. However, when I add a parameter, it does matter, and I confirmed this by looking at SqlDataAdapter> SelectCommand> Parameters> Non-Public Items> Items> [0] {@i_user}> Base Value>.
Even though the parameter is supplied with a value varchar
, I get the error
Procedure or function 'dbo.MyProcedure' expects parameter '@i_user' which is not supplied
What am I doing wrong?
source to share
The reason for the error is that the class SqlCommand
expects plain textual SQL. When assigning a name to the stored procedure ( "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;
...
}
source to share