WHERE failure when associating a query column in a view

Asked

Viewed 67 times

0

I am passing some parameters to a view where I need to receive the return of this query. When setting the filters in the Where of my query, the return of the query informs the following error:

Invalid competition column name.

I know it’s my coding failure, but I declare in my Where the columns for consultation

      public RelatorioDTO relatorio(DateTime compInicial)
    {
        // vw_relatorio: View 

        string query = "select cpfCnpj from vw_relatorio where competencia <= @compInicial";

        string myConnectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ToString();

        SqlConnection connection = new SqlConnection(myConnectionString);

        SqlCommand dbCommand = new SqlCommand(query, connection);

        SqlParameter parameter = dbCommand.CreateParameter();
        parameter.ParameterName = "@compInicial";
        parameter.Value = compInicial;
        parameter.DbType = DbType.DateTime;
        dbCommand.Parameters.Add(parameter);

        dbCommand.Connection.Open();
        dbCommand.ExecuteReader();
        // retornar a consulta
    var retornoLista = new List<RelatorioDTO>();

        if (((SqlDataReader)(myReader)).HasRows)
        {
            while (myReader.Read())
            {
                retornoLista .Add(FiltroRelatorio(myReader));
            }
        }
        else
        {
            return null;
        }

        return retornoLista;
  }

Returning item by item:

private RelatorioDTO FiltroRelatorio(IDataReader myReader)
    {
        var result = new RelatorioDTO ();
        result.CpfCnpj = myReader["CpfCnpj"].ToString().ToUpper();

        return result;
    }
  • Consider using dbCommand.Parameters.Addwithvalue(Parameter) or simply. dbCommand.Addwithvalue("@compInicial", compInitial ? (Object)Dbnull.Value);

  • @Start will always have a value defined when performing the query.

  • that column exists in the view? there is no typo in it? include the view query code in the question

No answers

Browser other questions tagged

You are not signed in. Login or sign up in order to post.