Error while passing SqlParameter

Navigation
0

I have a method to return a query , but it is giving error.

Class Data.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;
            }
        }

UserID.cs class 

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

    
asked by anonymous 08.08.2014 / 18:08

3 answers

2

The commandType information needs to be passed inside the retornaQuery method 

comando.CommandType = query.CommandType;
    
08.08.2014 / 20:04
2

Dude, this error is because you can not pass a SqlParameter collection in the SqlCommand Add method.

You could change and put a foreach of type: 

            if (query.Parameters.Count > 0)
            {

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

            }

But it will make another mistake for you. Because the parameters can not belong to another SqlCommand, to do the method the way you need it, suggested that you do it another way, 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 something 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, it was necessary to add the @ in the parameter strings, so: 

     private const String PARAM_USU_ID = "@ID";
     private const String PARAM_USU_USUARIO = "@USUARIO";
     private const String PARAM_USU_SENHA = "@SENHA";
    
08.08.2014 / 19:35
1

What a weird code this is yours.

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

On this line 

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

You have to put a dictonary like this 

 parametros.add(@IDProduto, IDProduto);


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

If you do not understand I can put another code.

    
08.08.2014 / 19:35
How to check WHERE clause with apostrophe in a DELETE operation? Insert NIB in the database