SQL Server Sum Sum Error on DAO Layer

Question

SQL Server Sum Sum Error on DAO Layer

Navigation

#1 by (2 votes)

0

I'm trying to show the result of SUM in the DAO layer, the query after the expected result, but in the field where the sum is shown, does not show an error.

A procedure in Sql Server

ALTER PROCEDURE [dbo].[RelatorioDcv] @dateInicio date, @dataFim date
as
begin
select ve.Placa, sum(m.CombustivelAbastecido), count(v.VrId)
from Vr v inner join Mv m on
v.IdMv = m.Id inner join Veiculo ve on
v.IdVeiculo = ve.Id inner join Combustivel c on
ve.IdCombustivel = c.IdCombustivel
where convert(Date,v.DataEHoraServico) between @dateInicio and @dataFim
group by ve.Placa
end

DAO Code:

public IList<Vr> Dcv(DateTime? dataInicio, DateTime? dataFim)
        {
            SqlCommand comando = new SqlCommand();
            comando.CommandType = CommandType.Text;
            comando.CommandText =
                @"
               EXECUTE RelatorioDcv @dateInicio=@dataInicio, @dataFim=@dataFim
                ";
            comando.Parameters.AddWithValue("@dataInicio", dataInicio);
            comando.Parameters.AddWithValue("@dataFim", dataFim);
            SqlDataReader dr = Conexao.Selecionar(comando);

            IList<Vr> lista = new List<Vr>();
            MvDAO mdao = new MvDAO();
            if (dr.HasRows)
            {
                while (dr.Read())
                {

                    Combustivel comb = new Combustivel();
                    Vr vr = new Vr();
                    vr.Veiculo = new Veiculo();
                    vr.Mv = new Mv();
                    vr.Veiculo.Placa = Convert.ToString(dr["Placa"]);
                    vr.Mv.CombustivelAbastecido = Convert.ToDecimal(dr["CombustivelAbastecido"]);

                    lista.Add(vr);
                }
            }
            else
            {
                lista = null;
            }
            dr.Close();
            return lista;
        }

The problem is precisely in this piece that theoretically was to show the sum

 vr.Mv.CombustivelAbastecido = Convert.ToDecimal(dr["CombustivelAbastecido"]);

I have not tried to display Count ().

sql-server c# asp.net-mvc

asked by anonymous 21.08.2018 / 16:56

1 answer

Problems with If Else Statement chained in C language How to capture windows status (suspend / hibernate) via Delphi code

score 2 · Answer 1

Looking at this, a possible error in the code is that at the time of creating your Combustive () object, it is not encountering the BurnedFilter field. Try to run the procedure on the bank and check if all columns are named correctly, usually in Sum () the column comes as "(No column name)". Try adding a name to the sum column in the select, it would look like this:

    ALTER PROCEDURE [dbo].[RelatorioDcv] @dateInicio date, @dataFim date
    as
    begin
    select ve.Placa, sum(m.CombustivelAbastecido) as CombustivelAbastecido, 
    count(v.VrId)
    from Vr v inner join Mv m on
    v.IdMv = m.Id inner join Veiculo ve on
    v.IdVeiculo = ve.Id inner join Combustivel c on
    ve.IdCombustivel = c.IdCombustivel
    where convert(Date,v.DataEHoraServico) between @dateInicio and @dataFim
    group by ve.Placa
    end