Error while passing Sqlparameter

Question

I have a method to return a query, but it’s going wrong.

Data Class.Cs:

public static SqlDataReader retornaQuery(SqlCommand query, List<SqlParameter> parameters)
        {
            try
            {
                //Instância o sqlcommand com a query sql que será executada e a conexão.
                SqlCommand comando = new SqlCommand(query.CommandText, connection());

                if (parameters != null && parameters.Count > 0)
                {
                    comando.Parameters.AddRange(parameters.ToArray());
                }
                //Executa a query sql.
                var retornaQuery = comando.ExecuteReader(); //Procedure or function 'SP_AUTENTICAR_USUARIOS' expects parameter '@USUARIO', which was not supplied.
                //Fecha a conexão.
                connection().Close();
                //Retorna o dataReader com o resultado
                return retornaQuery;
            }
            catch (SqlException ex)
            {
                throw ex;
            }
        }

User class.Cs

        #region Parâmetros
         private const String PARAM_USU_ID = "ID";
         private const String PARAM_USU_USUARIO = "USUARIO";
         private const String PARAM_USU_SENHA = "SENHA";
        #endregion
        #region Procedures
         private const String PROCEDURE_SP_LISTAR_USUARIOS = "SP_LISTAR_USUARIOS";
         private const String PROCEDURE_SP_AUTENTICAR_USUARIOS = "SP_AUTENTICAR_USUARIOS";
        #endregion

public Boolean Autenticar(Usuario usuario)
        {
            try
            {
                var cmd = new SqlCommand();
                var parameters = new List<SqlParameter>();
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = PROCEDURE_SP_AUTENTICAR_USUARIOS;
                parameters.Add(new SqlParameter() { ParameterName = PARAM_USU_SENHA, Value = usuario.UsuSenha, SqlDbType = SqlDbType.VarChar});
                parameters.Add(new SqlParameter() { ParameterName = PARAM_USU_USUARIO, Value = usuario.UsuUsuario, SqlDbType = SqlDbType.VarChar });
                using (IDataReader idr = Dados.retornaQuery(cmd, parameters))
                {
                    while (idr.Read())
                    {
                        if (idr["MSG"].Equals("1"))
                        {
                            return true;
                        }
                        return false;
                    }
                }
            }
            catch (Exception ex)
            {
                throw new Exception(ex.Message);
            }
            return false;
        }

Error: Procedure or Function 'SP_AUTENTICAR_USUARIOS' expects Parameter '@USUARIO', which was not supplied.

Answer 1

Dude, this is your mistake because you can’t pass a collection of Sqlparameter in the Sqlcommand Add method.

You could change and put a foreach like:

            if (query.Parameters.Count > 0)
            {

                foreach (var item in query.Parameters)
                {
                    query.Parameters.Add(item);
                }

            }

But it’ll make another mistake for you. Because the parameters can not belong to another Sqlcommand, to do the method the way you need, I would suggest that you do it differently, like this:

public static SqlDataReader retornaQuery(String query, List<SqlParameter> parameters)
    {

        try
        {
            //Instância o sqlcommand com a query sql que será executada e a conexão.
            SqlCommand comando = new SqlCommand(query, connection());

            if (parameters != null && parameters.Count > 0)
            {
                comando.Parameters.AddRange(parameters.ToArray());
            }


            //Executa a query sql.
            var retornaQuery = comando.ExecuteReader();

            //Fecha a conexão.
            connection().Close();

            //Retorna o dataReader com o resultado
            return retornaQuery;

        }
        catch (SqlException ex)
        {
            throw ex;
        }
    }

To use you could do more or less like this:

List<SqlParameter> parameters = new List<SqlParameter>();
        parameters.Add(new SqlParameter() { ParameterName = "ID"});
        parameters.Add(new SqlParameter() { ParameterName = "Descricao" });

        Question28302.retornaQuery("query", parameters);

I hope it helps.

---

Dude, missed to add @ in parameter strings, so do:

     private const String PARAM_USU_ID = "@ID";
     private const String PARAM_USU_USUARIO = "@USUARIO";
     private const String PARAM_USU_SENHA = "@SENHA";

Answer 2

Missing pass the information of commandType within the method retornaQuery

comando.CommandType = query.CommandType;

Answer 3

Boy what a strange code your.

In this line you must pass a value of a dictonary

comando.Parameters.Add(query.Parameters.);

For example I have a query like this.

Select * from Produtos where IDProduto = @IDProduto

Along that line of yours

comando.Parameters.Add(query.Parameters.);

you have to put so a dictonary

 parametros.add(@IDProduto, IDProduto);


foreach (KeyValuePair<string, Object> parametro in parametros)
{
   cmd.Parameters.AddWithValue(parametro.Key, parametro.Value);
}

if you don’t understand I can put another code.