Problem with functions

Question

Problem with functions

Navigation

#1 by (0 votes)

-1

Hello, I have the following problem, I have a view in the database that makes a group by , and I have another table GraficoCor , I want to make every time that While of the first function makes a increment, it passes the counter value to the IdCor variable of the second function, the second function will query the value of the IdCor by returning the color in hexadecimal for the first function. How would you do that?

Function GetFaturamentoIVEL

public static FatoFaturamentoIVELBO[] GetFaturamentoIVEL(string Operacao, Connection Cn)
        {
            var RsFaturamento = new Recordset();
            int Cont = 0;
            try
            {
                RsFaturamento.Open(String.Format("SELECT Operacao, AnoMes, TradeMarketing, SUM(ValorNF)AS ValorTotal FROM dbo.FatoFaturamentoIVEL WHERE TradeMarketing = 0  and AnoMes = '2016/04' GROUP BY Operacao, AnoMes, TradeMarketing ORDER BY SUM(ValorNF) ASC", Operacao), Cn, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockReadOnly);
                var ArrayRetorno = new FatoFaturamentoIVELBO[RsFaturamento.RecordCount];
                while (!RsFaturamento.EOF)
                {
                    FatoFaturamentoIVELBO Faturamento = new FatoFaturamentoIVELBO();
                    Faturamento.Operacao = RsFaturamento.Fields["Operacao"].Value.ToString();
                    Faturamento.AnoMes = RsFaturamento.Fields["AnoMes"].Value.ToString();
                    Faturamento.ValorNF = decimal.Parse(RsFaturamento.Fields["ValorTotal"].Value.ToString());
                    ArrayRetorno[Cont] = Faturamento;
                    Cont++;
                    RsFaturamento.MoveNext();
                }
                RsFaturamento.Close();
                return ArrayRetorno;
            }
            catch (Exception ex)
            {
                throw new Exception("Erro: " + ex.Message);
            }
        }

Function GetCor

public  static FatoFaturamentoIVELBO GetCor(int IdCor, Connection Cn)
        {
            var Cor = new FatoFaturamentoIVELBO();
            var RsCor = new Recordset();
            try
            {
                RsCor.Open(String.Format("SELECT IdCor, CodHex from dbo.GraficoCor  where IdCor = " + IdCor), Cn, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockReadOnly);
                if (!RsCor.EOF)
                {
                    Cor.CodHex = RsCor.Fields["CodHex"].Value.ToString();
                }
                return Cor;
            }
            catch (Exception ex)
            {
                throw new Exception("Erro :" + ex.Message);
            }
        }

sql c#

asked by anonymous 18.04.2016 / 20:12

1 answer

DynamicReports chart: comparing sales for years Error in query: 'SQLSTATE [42000]

score 0 · Answer 1

I got the help from @jbueno

public static FatoFaturamentoIVELBO[] GetFaturamentoIVEL(string Operacao, Connection Cn)
        {

            var RsFaturamento = new Recordset();
            int Cont = 0;
            int CountCor = 0;
            try
            {
                RsFaturamento.Open(String.Format("SELECT Operacao, AnoMes, TradeMarketing, SUM(ValorNF)AS ValorTotal FROM dbo.FatoFaturamentoIVEL WHERE TradeMarketing = 0  and AnoMes = '2016/04' GROUP BY Operacao, AnoMes, TradeMarketing ORDER BY SUM(ValorNF) ASC", Operacao), Cn, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockReadOnly);
                var ArrayRetorno = new FatoFaturamentoIVELBO[RsFaturamento.RecordCount];
                while (!RsFaturamento.EOF)
                {
                    FatoFaturamentoIVELBO Faturamento = new FatoFaturamentoIVELBO();
                    Faturamento.Operacao = RsFaturamento.Fields["Operacao"].Value.ToString();
                    Faturamento.AnoMes = RsFaturamento.Fields["AnoMes"].Value.ToString();
                    Faturamento.ValorNF = decimal.Parse(RsFaturamento.Fields["ValorTotal"].Value.ToString());
                    Faturamento.CodHex = GetCor(CountCor++, ref Cn);

                    ArrayRetorno[Cont] = Faturamento;

                    Cont++;
                    RsFaturamento.MoveNext();
                }

                RsFaturamento.Close();

                return ArrayRetorno;
            }
            catch (Exception ex)
            {
                throw new Exception("Erro: " + ex.Message);
            }
        }





        private static string GetCor(int IdCor, ref Connection Cn)
        {
            var RsCor = new Recordset();

            try
            {
                RsCor.Open(String.Format("SELECT IdCor, CodHex from dbo.GraficoCor  where IdCor = {0}", IdCor), Cn, CursorTypeEnum.adOpenStatic, LockTypeEnum.adLockReadOnly);
                if (!RsCor.EOF)
                {
                    return RsCor.Fields["CodHex"].Value.ToString();
                }
                else
                {
                    //nao encontrou a cor vc faz o que?????? retorna pink! uhahuahuhuahuahuahua
                    return "#FFFFFF";

                }
            }
            catch (Exception ex)
            {
                throw new Exception("Erro :" + ex.Message);
            }
        }