none
Query error: Operand data type varchar is invalid for multiply operator RRS feed

  • Question

  •  I'm writing a software in c # that works on SQL Server 2014 (I'll quote below the C # and SqlServer of the query) then my problem is the following! I need to generate a report with microsoft report, which will include values ​​based on the selection of some checkboxes! But when I run the bold query I get the following error: --- Operand data type varchar is invalid for multiply operator ---  some of you know how to help me:

    Select.cs

    if (checkedListBoxCosaStampare.CheckedItems.Count > 0)
                        {
    
                            try
                            {
                                //contro i valori cekkati:
                                int contacheck = 0;
                                //Devo effettuare tutti i controlli
    
                                //Il ricarico non funziona correttamente con il punto quindi controllo se è presente il punto se c'è lo 
                                //sostituisco con la ,
                                if (textBoxRicarico.Text.Contains("."))
                                {
                                    textBoxRicarico.Text = textBoxRicarico.Text.Replace(".", ",");
                                }
    
                                float Ricarico = float.Parse(textBoxRicarico.Text);
    
                                //Setto la parte iniziale della query
                                String QueryTotali = "";
                                String QueryTotaleCantiere = "";
                                String Query = "SET LANGUAGE 'Italian' select Tipo, Data, GG, CodArt as 'CodArt',Descrizione,Quantita as 'Quantita',Prezzo,Totale from (  ";
                                int check = 0;
                                //Costruzione query di stampa
    
                                foreach (int indexChecked in checkedListBoxCosaStampare.CheckedIndices)
                                {
                                    //controllo se gli articoli sono selezionati
                                    if (indexChecked.ToString() == "0")
                                    {
                                        Query = Query + "Select 'A' as Tipo, CONVERT(VARCHAR(10), ArticoloCantiere.Data, 105) as Data, DATENAME(WEEKDAY, ArticoloCantiere.DataInserimento) as GG, ArticoloCantiere.CodArt, ArticoloCantiere.Descrizione, ArticoloCantiere.Quantita, (ArticoloCantiere.Prezzo+((ArticoloCantiere.Prezzo/100)*'" + Ricarico + "')) as Prezzo, (ArticoloCantiere.Prezzo+((ArticoloCantiere.Prezzo/100)*'" + Ricarico + "'))*Quantita as Totale  from Cantiere  inner join ArticoloCantiere  on Cantiere.IdCantiere = ArticoloCantiere.IdCantiere  where ArticoloCantiere.IdCantiere = '" + IdCantiere + "'  ";
                                        contacheck++;
    
                                    }
    
                                    //controllo le spese sostenute
                                    if (indexChecked.ToString() == "1")
                                    {
                                        //controllo se è stato precendentemente selezionato qualcosa
                                        if (contacheck > 0)
                                        {
                                            Query = Query + " union ";
                                        }
    
                                        Query = Query + " Select 'S' as Tipo, CONVERT(VARCHAR(10), SpeseSostenute.Data, 105) as Data, DATENAME(WEEKDAY, SpeseSostenute.Data) as GG, '' as CodArt, 'Causale: ' + SpeseSostenute.Causale + ' --- Descrizione: ' + DescrizioneLibera as Descrizione, 0 as Quantita, (SpeseSostenute.Costo+((SpeseSostenute.Costo/100)*'" + Ricarico + "')) as Prezzo, (SpeseSostenute.Costo+((SpeseSostenute.Costo/100)*'" + Ricarico + "')) as Totale  from SpeseSostenute  where IdCantiere = '" + IdCantiere + "'  ";
                                        contacheck++;
    
    
                                    }
    
                                    //controllo i ristoranti
                                    if (indexChecked.ToString() == "2")
                                    {
    
                                        //controllo se è stato precendentemente selezionato qualcosa
                                        if (contacheck > 0)
                                        {
                                            Query = Query + " union ";
                                        }
    
                                        Query = Query + "     select 'RS' as Tipo, CONVERT(VARCHAR(10), Ristorante.Data, 105) as Data, DATENAME(WEEKDAY, Ristorante.Data) as GG, '' as CodArt, Ristorante.RagioneSociale as Descrizione, 0 as Quantita, (Ristorante.Costo+((Ristorante.Costo/100)*'" + Ricarico + "')) as Prezzo, (Ristorante.Costo+((Ristorante.Costo/100)*'" + Ricarico + "')) as Totale  from Ristorante  where IdCantiere='" + IdCantiere + "' ";
                                        contacheck++;
                                    }
    
                                    //controllo noleggi
                                    if (indexChecked.ToString() == "3")
                                    {
    
                                        //controllo se è stato precendentemente selezionato qualcosa
                                        if (contacheck > 0)
                                        {
                                            Query = Query + " union ";
                                        }
    
                                        Query = Query + "  Select 'N' as Tipo, CONVERT(VARCHAR(10), Noleggio.DataInizioNoleggio, 105)  + ' -- ' + CONVERT(VARCHAR(10), Noleggio.DataTermineNoleggio, 105) as Data, DATENAME(WEEKDAY, Noleggio.DataInizioNoleggio) as GG, Noleggio.Matricola as CodArt, 'Fornitore: ' + Fornitore.Nome + ' ---  Tipo Mezzo: ' + Noleggio.TipoMezzo as Descrizione, 0 as Quantita, 0 as Prezzo, ((Noleggio.CostoNoleggio+((Noleggio.CostoNoleggio/100)*'" + Ricarico + "'))+(Noleggio.Trasporto+((Noleggio.Trasporto/100)*'" + Ricarico + "'))) as Totale  from Noleggio  inner join Fornitore    on Fornitore.IdFornitore = Noleggio.IdFornitore   where IdCantiere = '" + IdCantiere + "'   ";
                                        contacheck++;
                                    }
    
                                    //controllo risorse umane
                                    if (indexChecked.ToString() == "4")
                                    {
    
                                        //controllo se è stato precendentemente selezionato qualcosa
                                        if (contacheck > 0)
                                        {
                                            Query = Query + " union ";
                                        }
    
                                        //controllo se è stato selezionate una stampa diversa da costo interno utilizzo check, per controllare il tipo di stampa
    
                                        foreach (int indexChecked2 in checkedListBoxStampaCostoRisorse.CheckedIndices)
                                        {
                                            //controllo la seconda datagriview con il tipo di costo per utente
                                            if (indexChecked2.ToString() == "0")
                                            {
                                                check = 2;
    
                                            }
                                            //controllo la seconda datagriview con il tipo di costo per utente
    
                                            if (indexChecked2.ToString() == "1")
                                            {
                                                check = 3;
    
                                            }
    
    
                                        }
    
                                        //CASO 1: se nessuna delle due è selezionato prendo il prezzo interno, per il conto sulla risorsa
                                        if (check == 0)
                                        {
                                            Query = Query + " Select 'R' as Tipo, CONVERT(VARCHAR(10), RisorseUmane.Data, 105) as Data, DATENAME(WEEKDAY, RisorseUmane.Data) as GG,  '' + Utente.Nome + ' ' + Utente.Cognome as CodArt, RisorseUmane.Descrizione, PARSE(REPLACE(convert(varchar(5), Cast(convert(varchar(5), (OreFine - OreInizio), 108) as datetime) - CAST(REPLACE(Pausa, '.', ':') as datetime), 108),':',',') as money)   as Quantita, Utente.CostoInterno as Prezzo, (Utente.CostoInterno* Replace(convert(varchar(5), Cast(convert(varchar(5), (OreFine - OreInizio), 108) as datetime) - CAST(REPLACE(Pausa, '.', ':') as datetime), 108),':', ',')/100) as Totale  from RisorseUmane inner join Utente  on RisorseUmane.IdUtente = Utente.IdUtente  where IdCantiere = '" + IdCantiere + "' ";
                                           
                                        }
    
                                        //CASO 2: Stampo risorse con costo di fatturazione
                                        else if (check == 2)
                                        {
                                            Query = Query + " Select 'R' as Tipo, CONVERT(VARCHAR(10), RisorseUmane.Data, 105) as Data, DATENAME(WEEKDAY, RisorseUmane.Data) as GG,  '' + Utente.Nome + ' ' + Utente.Cognome as CodArt, RisorseUmane.Descrizione, PARSE(REPLACE(convert(varchar(5), Cast(convert(varchar(5), (OreFine - OreInizio), 108) as datetime) - CAST(REPLACE(Pausa, '.', ':') as datetime), 108),':',',') as money) as Quantita, Utente.CostoFatturazione as Prezzo, (Utente.CostoFatturazione* Replace(convert(varchar(5), Cast(convert(varchar(5), (OreFine - OreInizio), 108) as datetime) - CAST(REPLACE(Pausa, '.', ':') as datetime), 108),':', ',')/100) as Totale  from RisorseUmane inner join Utente  on RisorseUmane.IdUtente = Utente.IdUtente  where IdCantiere = '" + IdCantiere + "' ";
                                            
                                        }
    
                                        //Caso 3: Stampo risorse con costo fisso
                                        else if (check == 3)
                                        {
                                            Query = Query + " Select 'R' as Tipo, CONVERT(VARCHAR(10), RisorseUmane.Data, 105) as Data, DATENAME(WEEKDAY, RisorseUmane.Data) as GG,  '' + Utente.Nome + ' ' + Utente.Cognome as CodArt, RisorseUmane.Descrizione, PARSE(REPLACE(convert(varchar(5), Cast(convert(varchar(5), (OreFine - OreInizio), 108) as datetime) - CAST(REPLACE(Pausa, '.', ':') as datetime), 108),':',',') as money) as Quantita, '" + decimal.Parse(textBoxCostoUnicoRisorse.Text) + "'  as Prezzo, ('" + decimal.Parse(textBoxCostoUnicoRisorse.Text) + "'  * Replace(convert(varchar(5), Cast(convert(varchar(5), (OreFine - OreInizio), 108) as datetime) - CAST(REPLACE(Pausa, '.', ':') as datetime), 108),':', ',')/100) as Totale  from RisorseUmane inner join Utente  on RisorseUmane.IdUtente = Utente.IdUtente  where IdCantiere = '" + IdCantiere + "' ";
    
                                        }
    
    
                                        contacheck++;
    
                                    }
    
                                    //controllo kilometri
                                    if (indexChecked.ToString() == "5")
                                    {
                                        //controllo se è stato precendentemente selezionato qualcosa
                                        if (contacheck > 0)
                                        {
                                            Query = Query + " union ";
                                        }
    
                                        Query = Query + " Select 'K' as Tipo, CONVERT(VARCHAR(10), Kilometri.Data, 105) as Data, DATENAME(WEEKDAY, Kilometri.Data) as GG, '' as CodArt, 'Tipo Mezzo: ' + Kilometri.TipoMezzo + ' --- Targa:' + Kilometri.Targa as Descrizione, Kilometri.Kilometri as Quantita, 0 as Prezzo, ((Kilometri*CostoKilometrico)+DirittoChiamata)+((((Kilometri*CostoKilometrico)+DirittoChiamata)/100)*'" + Ricarico + "') as Totale  from Kilometri  where IdCantiere = '" + IdCantiere + "' ";
    
                                    }
    
                                    //--------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
                                    //Calcolo i totali in base a cosa era stato selezionate per le risorse uman
                                    if (check == 0)
                                    {                                  
                                        QueryTotali = " select (select (sum(ArticoloCantiere.Prezzo*ArticoloCantiere.Quantita)+((sum(ArticoloCantiere.Prezzo*ArticoloCantiere.Quantita)/100)*'" + Ricarico + "')) from ArticoloCantiere where IdCantiere='" + IdCantiere + "')as TotaleArticoli,SUM((REPLACE((left(CAST(REPLACE((convert(varchar(5), Cast(convert(varchar(5), (RisorseUmane.OreFine - RisorseUmane.OreInizio), 108) as datetime) - CAST(REPLACE(RisorseUmane.Pausa, '.', ':') as datetime), 108)),':',',')AS money)/100, 2) ),'.',''))+(((CAST('0,'+(RIGHT(CAST(REPLACE((convert(varchar(5), Cast(convert(varchar(5), (RisorseUmane.OreFine - RisorseUmane.OreInizio), 108) as datetime) - CAST(REPLACE(RisorseUmane.Pausa, '.', ':') as datetime), 108)),':',',')AS money)/100, 2) ) as money)/100)*100)/60)) as TotaleOreRisorse,SUM((Utente.CostoInterno * ((REPLACE((left(CAST(REPLACE((convert(varchar(5), Cast(convert(varchar(5), (RisorseUmane.OreFine - RisorseUmane.OreInizio), 108) as datetime) - CAST(REPLACE(RisorseUmane.Pausa, '.', ':') as datetime), 108)),':',',')AS money)/100, 2) ),'.',''))+(((CAST('0,'+(RIGHT(CAST(REPLACE((convert(varchar(5), Cast(convert(varchar(5), (RisorseUmane.OreFine - RisorseUmane.OreInizio), 108) as datetime) - CAST(REPLACE(RisorseUmane.Pausa, '.', ':') as datetime), 108)),':',',')AS money)/100, 2) ) as money)/100)*100)/60)))) as TotaleRisorse,(select SUM(Costo + ((Costo / 100) * '" + Ricarico + "')) from SpeseSostenute where IdCantiere = '" + IdCantiere + "')as TotaleSpese ,(select SUM(Costo + ((Costo / 100) * '" + Ricarico + "')) from Ristorante where IdCantiere = '" + IdCantiere + "' ) as TotaleRistorante,(select SUM((Kilometri * CostoKilometrico) + DirittoChiamata + ((((Kilometri * CostoKilometrico) + DirittoChiamata) / 100) * '" + Ricarico + "')) from Kilometri where IdCantiere = '" + IdCantiere + "') as TotaleKilometri,(select SUM((Noleggio.CostoNoleggio + Noleggio.Trasporto)) from Noleggio where IdCantiere = '" + IdCantiere + "') as TotaleNoleggi  from Cantiere inner join RisorseUmane on Cantiere.IdCantiere = RisorseUmane.IdCantiere  inner  join Utente on Utente.IdUtente = RisorseUmane.IdUtente where Cantiere.IdCantiere = '" + IdCantiere + "'";                                   
                                        QueryTotaleCantiere = " select  COALESCE((select (sum(ArticoloCantiere.Prezzo*ArticoloCantiere.Quantita)+((sum(ArticoloCantiere.Prezzo*ArticoloCantiere.Quantita)/100)*'" + Ricarico + "')) from ArticoloCantiere where IdCantiere='" + IdCantiere + "'),0) + COALESCE(SUM((Utente.CostoInterno * ((((REPLACE((left(CAST(REPLACE((convert(varchar(5), Cast(convert(varchar(5), (RisorseUmane.OreFine - RisorseUmane.OreInizio), 108) as datetime) - CAST(REPLACE(RisorseUmane.Pausa, '.', ':') as datetime), 108)),':',',')AS money)/100, 2) ),'.',''))+(((CAST('0,'+(RIGHT(CAST(REPLACE((convert(varchar(5), Cast(convert(varchar(5), (RisorseUmane.OreFine - RisorseUmane.OreInizio), 108) as datetime) - CAST(REPLACE(RisorseUmane.Pausa, '.', ':') as datetime), 108)),':',',')AS money)/100, 2) ) as money)/100)*100)/60)))))) , 0) + COALESCE((select SUM(Costo + ((Costo / 100) * '" + Ricarico + "')) from SpeseSostenute where IdCantiere = '" + IdCantiere + "'),0) +COALESCE((select SUM(Costo + ((Costo / 100) * '" + Ricarico + "')) from Ristorante where IdCantiere = '" + IdCantiere + "' ),0)  +COALESCE((select SUM((Kilometri * CostoKilometrico) + DirittoChiamata + ((((Kilometri * CostoKilometrico) + DirittoChiamata) / 100) * '" + Ricarico + "')) from Kilometri where IdCantiere = '" + IdCantiere + "'),0)  +COALESCE((select SUM((Noleggio.CostoNoleggio + Noleggio.Trasporto)) from Noleggio where IdCantiere = '" + IdCantiere + "'),0)  as Totale from Cantiere inner join RisorseUmane on Cantiere.IdCantiere = RisorseUmane.IdCantiere  inner join Utente on Utente.IdUtente = RisorseUmane.IdUtente where Cantiere.IdCantiere = '" + IdCantiere + "'";
                                    }
    
                                    //Calcolo i totali con le risorse con il costo di fatturazione
                                    else if (check == 2)
                                    {
    
                                        QueryTotali = " select (select (sum(ArticoloCantiere.Prezzo*ArticoloCantiere.Quantita)+((sum(ArticoloCantiere.Prezzo*ArticoloCantiere.Quantita)/100)*'" + Ricarico + "')) from ArticoloCantiere where IdCantiere='" + IdCantiere + "')as TotaleArticoli,SUM((REPLACE((left(CAST(REPLACE((convert(varchar(5), Cast(convert(varchar(5), (RisorseUmane.OreFine - RisorseUmane.OreInizio), 108) as datetime) - CAST(REPLACE(RisorseUmane.Pausa, '.', ':') as datetime), 108)),':',',')AS money)/100, 2) ),'.',''))+(((CAST('0,'+(RIGHT(CAST(REPLACE((convert(varchar(5), Cast(convert(varchar(5), (RisorseUmane.OreFine - RisorseUmane.OreInizio), 108) as datetime) - CAST(REPLACE(RisorseUmane.Pausa, '.', ':') as datetime), 108)),':',',')AS money)/100, 2) ) as money)/100)*100)/60)) as TotaleOreRisorse,SUM((Utente.CostoFatturazione * ((REPLACE((left(CAST(REPLACE((convert(varchar(5), Cast(convert(varchar(5), (RisorseUmane.OreFine - RisorseUmane.OreInizio), 108) as datetime) - CAST(REPLACE(RisorseUmane.Pausa, '.', ':') as datetime), 108)),':',',')AS money)/100, 2) ),'.',''))+(((CAST('0,'+(RIGHT(CAST(REPLACE((convert(varchar(5), Cast(convert(varchar(5), (RisorseUmane.OreFine - RisorseUmane.OreInizio), 108) as datetime) - CAST(REPLACE(RisorseUmane.Pausa, '.', ':') as datetime), 108)),':',',')AS money)/100, 2) ) as money)/100)*100)/60)))) as TotaleRisorse,(select SUM(Costo + ((Costo / 100) * '" + Ricarico + "')) from SpeseSostenute where IdCantiere = '" + IdCantiere + "')as TotaleSpese ,(select SUM(Costo + ((Costo / 100) * '" + Ricarico + "')) from Ristorante where IdCantiere = '" + IdCantiere + "' ) as TotaleRistorante,(select SUM((Kilometri * CostoKilometrico) + DirittoChiamata + ((((Kilometri * CostoKilometrico) + DirittoChiamata) / 100) * '" + Ricarico + "')) from Kilometri where IdCantiere = '" + IdCantiere + "') as TotaleKilometri,(select SUM((Noleggio.CostoNoleggio + Noleggio.Trasporto)) from Noleggio where IdCantiere = '" + IdCantiere + "') as TotaleNoleggi  from Cantiere inner join RisorseUmane on Cantiere.IdCantiere = RisorseUmane.IdCantiere  inner  join Utente on Utente.IdUtente = RisorseUmane.IdUtente where Cantiere.IdCantiere = '" + IdCantiere + "'";
                                        QueryTotaleCantiere = " select  COALESCE((select (sum(ArticoloCantiere.Prezzo*ArticoloCantiere.Quantita)+((sum(ArticoloCantiere.Prezzo*ArticoloCantiere.Quantita)/100)*'" + Ricarico + "')) from ArticoloCantiere where IdCantiere='" + IdCantiere + "'),0) + COALESCE(SUM((Utente.CostoFatturazione * ((((REPLACE((left(CAST(REPLACE((convert(varchar(5), Cast(convert(varchar(5), (RisorseUmane.OreFine - RisorseUmane.OreInizio), 108) as datetime) - CAST(REPLACE(RisorseUmane.Pausa, '.', ':') as datetime), 108)),':',',')AS money)/100, 2) ),'.',''))+(((CAST('0,'+(RIGHT(CAST(REPLACE((convert(varchar(5), Cast(convert(varchar(5), (RisorseUmane.OreFine - RisorseUmane.OreInizio), 108) as datetime) - CAST(REPLACE(RisorseUmane.Pausa, '.', ':') as datetime), 108)),':',',')AS money)/100, 2) ) as money)/100)*100)/60)))))) , 0) + COALESCE((select SUM(Costo + ((Costo / 100) * '" + Ricarico + "')) from SpeseSostenute where IdCantiere = '" + IdCantiere + "'),0) +COALESCE((select SUM(Costo + ((Costo / 100) * '" + Ricarico + "')) from Ristorante where IdCantiere = '" + IdCantiere + "' ),0)  +COALESCE((select SUM((Kilometri * CostoKilometrico) + DirittoChiamata + ((((Kilometri * CostoKilometrico) + DirittoChiamata) / 100) * '" + Ricarico + "')) from Kilometri where IdCantiere = '" + IdCantiere + "'),0)  +COALESCE((select SUM((Noleggio.CostoNoleggio + Noleggio.Trasporto)) from Noleggio where IdCantiere = '" + IdCantiere + "'),0)  as Totale from Cantiere inner join RisorseUmane on Cantiere.IdCantiere = RisorseUmane.IdCantiere  inner join Utente on Utente.IdUtente = RisorseUmane.IdUtente where Cantiere.IdCantiere = '" + IdCantiere + "'";
    
                                    }
    
                                    //Calcolo i totali con le risorse_umane con prezzo unico
                                    else if (check == 3)
                                    {
                                        QueryTotali = " select (select (sum(ArticoloCantiere.Prezzo*ArticoloCantiere.Quantita)+((sum(ArticoloCantiere.Prezzo*ArticoloCantiere.Quantita)/100)*'" + Ricarico + "')) from ArticoloCantiere where IdCantiere='" + IdCantiere + "')as TotaleArticoli,SUM((REPLACE((left(CAST(REPLACE((convert(varchar(5), Cast(convert(varchar(5), (RisorseUmane.OreFine - RisorseUmane.OreInizio), 108) as datetime) - CAST(REPLACE(RisorseUmane.Pausa, '.', ':') as datetime), 108)),':',',')AS money)/100, 2) ),'.',''))+(((CAST('0,'+(RIGHT(CAST(REPLACE((convert(varchar(5), Cast(convert(varchar(5), (RisorseUmane.OreFine - RisorseUmane.OreInizio), 108) as datetime) - CAST(REPLACE(RisorseUmane.Pausa, '.', ':') as datetime), 108)),':',',')AS money)/100, 2) ) as money)/100)*100)/60)) as TotaleOreRisorse,SUM(('" + decimal.Parse(textBoxCostoUnicoRisorse.Text) + "' * ((REPLACE((left(CAST(REPLACE((convert(varchar(5), Cast(convert(varchar(5), (RisorseUmane.OreFine - RisorseUmane.OreInizio), 108) as datetime) - CAST(REPLACE(RisorseUmane.Pausa, '.', ':') as datetime), 108)),':',',')AS money)/100, 2) ),'.',''))+(((CAST('0,'+(RIGHT(CAST(REPLACE((convert(varchar(5), Cast(convert(varchar(5), (RisorseUmane.OreFine - RisorseUmane.OreInizio), 108) as datetime) - CAST(REPLACE(RisorseUmane.Pausa, '.', ':') as datetime), 108)),':',',')AS money)/100, 2) ) as money)/100)*100)/60)))) as TotaleRisorse,(select SUM(Costo + ((Costo / 100) * '" + Ricarico + "')) from SpeseSostenute where IdCantiere = '" + IdCantiere + "')as TotaleSpese ,(select SUM(Costo + ((Costo / 100) * '" + Ricarico + "')) from Ristorante where IdCantiere = '" + IdCantiere + "' ) as TotaleRistorante,(select SUM((Kilometri * CostoKilometrico) + DirittoChiamata + ((((Kilometri * CostoKilometrico) + DirittoChiamata) / 100) * '" + Ricarico + "')) from Kilometri where IdCantiere = '" + IdCantiere + "') as TotaleKilometri,(select SUM((Noleggio.CostoNoleggio + Noleggio.Trasporto)) from Noleggio where IdCantiere = '" + IdCantiere + "') as TotaleNoleggi  from Cantiere inner join RisorseUmane on Cantiere.IdCantiere = RisorseUmane.IdCantiere  inner  join Utente on Utente.IdUtente = RisorseUmane.IdUtente where Cantiere.IdCantiere = '" + IdCantiere + "'";
                                        QueryTotaleCantiere = " select  COALESCE((select (sum(ArticoloCantiere.Prezzo*ArticoloCantiere.Quantita)+((sum(ArticoloCantiere.Prezzo*ArticoloCantiere.Quantita)/100)*'" + Ricarico + "')) from ArticoloCantiere where IdCantiere='" + IdCantiere + "'),0) + COALESCE(SUM(('" + decimal.Parse(textBoxCostoUnicoRisorse.Text) + "'  * ((((REPLACE((left(CAST(REPLACE((convert(varchar(5), Cast(convert(varchar(5), (RisorseUmane.OreFine - RisorseUmane.OreInizio), 108) as datetime) - CAST(REPLACE(RisorseUmane.Pausa, '.', ':') as datetime), 108)),':',',')AS money)/100, 2) ),'.',''))+(((CAST('0,'+(RIGHT(CAST(REPLACE((convert(varchar(5), Cast(convert(varchar(5), (RisorseUmane.OreFine - RisorseUmane.OreInizio), 108) as datetime) - CAST(REPLACE(RisorseUmane.Pausa, '.', ':') as datetime), 108)),':',',')AS money)/100, 2) ) as money)/100)*100)/60)))))) , 0) + COALESCE((select SUM(Costo + ((Costo / 100) * '" + Ricarico + "')) from SpeseSostenute where IdCantiere = '" + IdCantiere + "'),0) +COALESCE((select SUM(Costo + ((Costo / 100) * '" + Ricarico + "')) from Ristorante where IdCantiere = '" + IdCantiere + "' ),0)  +COALESCE((select SUM((Kilometri * CostoKilometrico) + DirittoChiamata + ((((Kilometri * CostoKilometrico) + DirittoChiamata) / 100) * '" + Ricarico + "')) from Kilometri where IdCantiere = '" + IdCantiere + "'),0)  +COALESCE((select SUM((Noleggio.CostoNoleggio + Noleggio.Trasporto)) from Noleggio where IdCantiere = '" + IdCantiere + "'),0)  as Totale from Cantiere inner join RisorseUmane on Cantiere.IdCantiere = RisorseUmane.IdCantiere  inner join Utente on Utente.IdUtente = RisorseUmane.IdUtente where Cantiere.IdCantiere = '" + IdCantiere + "'";
                                        //MessageBox.Show("Costo convertito " + decimal.Parse(textBoxCostoUnicoRisorse.Text));
                                    }
    
                                }
                                
    
                                    //setto la parte finale della query
                                    Query = Query + "	 )Q order by Data ";
    
                                    //eseguo la stampa
                                    StampaCompletaCantiereconPrezzo s = new StampaCompletaCantiereconPrezzo();
                                    s.db = db;
                                    s.IdCantiere = IdCantiere;
                                    s.Query = Query;
                                    s.QueryTotali = QueryTotali;
                                    s.QueryTotaleCantiere = QueryTotaleCantiere;
                                    s.Show();
                                    this.Close();
                             
                                
                            }

    StampaCompletaCantiereconPrezzo.cs
    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    using System.Data.Sql;
    using System.Data.SqlClient;
    using Microsoft.Reporting.WinForms;
    
    
    namespace Quote
    {
        public partial class StampaCompletaCantiereconPrezzo : Form
        {
            public Database db;
            public int IdCantiere;
            public String Query=null;
            public String QueryTotali = null;
            public String QueryTotaleCantiere = null;
    
    
            public StampaCompletaCantiereconPrezzo()
            {
                InitializeComponent();
            }
    
            private void StampaCompletaCantiereconPrezzo_Load(object sender, EventArgs e)
            {
                try
                { 
                    String QueryCliente = "select Cliente.RagioneSociale,Cantiere.NomeCantiere,Cantiere.DescrizioneEstesa,Cantiere.StatoCantiere,Utente.LogoAzienda from Cliente inner join Cantiere on Cantiere.IdCliente = Cliente.IdCliente inner join Utente on Utente.IdUtente = Cantiere.IdUtenteCreazioneCantiere   where IdCantiere = '" + IdCantiere + "' ";
                    SqlConnection conn = db.apriconnessione();
                    DataStampaCompletaCantiereConPrezzo d = new DataStampaCompletaCantiereConPrezzo();
                    SqlDataAdapter da = new SqlDataAdapter(Query, conn);
                    SqlDataAdapter da2 = new SqlDataAdapter(QueryCliente, conn);
                    SqlDataAdapter da3 = new SqlDataAdapter(QueryTotali, conn);
                    SqlDataAdapter da4 = new SqlDataAdapter(QueryTotaleCantiere, conn);
                    da.Fill(d, d.Tables[0].TableName);
                    da2.Fill(d, d.Tables[1].TableName);               
                    da3.Fill(d, d.Tables[2].TableName);
                    da4.Fill(d, d.Tables[3].TableName);
                    ReportDataSource rds = new ReportDataSource("DataSet1", d.Tables[0]);
                    ReportDataSource rds2 = new ReportDataSource("DataSet2", d.Tables[1]);
                    ReportDataSource rds3 = new ReportDataSource("DataSet3", d.Tables[2]);
                    ReportDataSource rds4 = new ReportDataSource("DataSet4", d.Tables[3]);
                    this.reportViewer1.LocalReport.EnableExternalImages = true;
                    this.reportViewer1.LocalReport.DataSources.Clear();
                    this.reportViewer1.LocalReport.DataSources.Add(rds);
                    this.reportViewer1.LocalReport.DataSources.Add(rds2);
                    this.reportViewer1.LocalReport.DataSources.Add(rds3);
                    this.reportViewer1.LocalReport.DataSources.Add(rds4);
                    this.reportViewer1.LocalReport.Refresh();
                    this.reportViewer1.RefreshReport();
                    this.reportViewer1.SetDisplayMode(DisplayMode.PrintLayout);
                    db.chiudiconnessione();
                    conn.Close();
    
                }
    
                catch (Exception ex)
                {
                    MessageBox.Show("Errore nella stampa: "+ex);
                }
            }
        }
    }

    I solved setting it as cast money

    I solved setting it as cast money
    I solved setting it as cast money
    I solved setting it as cast money
    • Edited by rikidev Thursday, October 19, 2017 8:42 AM Solved
    Wednesday, October 18, 2017 6:31 AM

Answers

  • Take a look in the following part:

    Prezzo*ArticoloCantiere.Quantita)/100)*'" + Ricarico + "'

    Assume Ricarico is 90, this part of query will look like this:

    Prezzo*ArticoloCantiere.Quantita)/100)*'90'

    See? The "90" becomes a string (i.e.: varchar in SQL) and multiplication won't work.

    • Proposed as answer by RJP1973 Thursday, October 19, 2017 7:01 AM
    • Marked as answer by cheong00Editor Monday, October 30, 2017 1:40 AM
    Thursday, October 19, 2017 6:54 AM
    Answerer
  • In theory, you just shouldn't need the single quotes:

     = "...Prezzo*ArticoloCantiere.Quantita)/100)*" + Ricarico + "...etc";

    So that your resulting string ends up as (notice, no single quotes around the 90):

      Prezzo*ArticoloCantiere.Quantita)/100) * 90 

    Although, really, you should be using parameterised queries

    • Proposed as answer by cheong00Editor Thursday, October 19, 2017 9:55 AM
    • Marked as answer by cheong00Editor Monday, October 30, 2017 1:40 AM
    Thursday, October 19, 2017 8:38 AM

All replies

  • You should write the query out to the output window, run that query via a text file in your project with an extension of .sql rather than .txt.

    See also for conversion:

    https://msdn.microsoft.com/en-us/library/hh230993%28v=sql.110%29.aspx?f=255&MSPPError=-2147217396


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Wednesday, October 18, 2017 11:45 AM
    Moderator
  • Hi rikidev,

    Based on your description, you want to change your query code by check checklistbox. Can you please tell us where you encounter this issue, please give a specific location.

    By the way, your query is very complicated, you can add break point in your code and show your every query code, then run in the SQL query to look if these are all works fine.

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, October 19, 2017 6:01 AM
  • Take a look in the following part:

    Prezzo*ArticoloCantiere.Quantita)/100)*'" + Ricarico + "'

    Assume Ricarico is 90, this part of query will look like this:

    Prezzo*ArticoloCantiere.Quantita)/100)*'90'

    See? The "90" becomes a string (i.e.: varchar in SQL) and multiplication won't work.

    • Proposed as answer by RJP1973 Thursday, October 19, 2017 7:01 AM
    • Marked as answer by cheong00Editor Monday, October 30, 2017 1:40 AM
    Thursday, October 19, 2017 6:54 AM
    Answerer
  • Then I also tried to put the cast on them but I still have the same mistake @cheong00

    • Edited by rikidev Thursday, October 19, 2017 8:08 AM
    Thursday, October 19, 2017 8:07 AM
  • In theory, you just shouldn't need the single quotes:

     = "...Prezzo*ArticoloCantiere.Quantita)/100)*" + Ricarico + "...etc";

    So that your resulting string ends up as (notice, no single quotes around the 90):

      Prezzo*ArticoloCantiere.Quantita)/100) * 90 

    Although, really, you should be using parameterised queries

    • Proposed as answer by cheong00Editor Thursday, October 19, 2017 9:55 AM
    • Marked as answer by cheong00Editor Monday, October 30, 2017 1:40 AM
    Thursday, October 19, 2017 8:38 AM
  • Yup. The quick fix is to locate all "*" in that SQL statement, and remove single quotes before and after the value near that. Say:

    SUM(('" + decimal.Parse(textBoxCostoUnicoRisorse.Text) + "' *

    But the true fix is to change it into parameterized query, so it'll add single quotes for you as needed so you need not worry about it, also it'll guard your application against SQL injection attack so you won't risk accidental data leakage and/or have your data deleted by malicious users.



    Thursday, October 19, 2017 10:21 AM
    Answerer