none
Making a correct Datareader to read all occurrences on the Table in a period and make a sum RRS feed

  • Question

  • Hi folks good evening to everyone,

    I'm making a form where I need to read all the occurrences of an expense in a period to perform a report of Ernings and Expenses in a period, (Cash Flow System).

    I'm getting a Error, and its is not excuting the sum, Please I need your help with this code.

    I Want to know the Total of an expense "9" (expense Id - Rent)  in a period, typed in masked text box : mtbinicio and mtbfinal.

    Windows Form: 

    using System;
    using System.Collections.Generic;
    using System.ComponentModel;
    using System.Data;
    using System.Data.SqlClient;
    using System.Drawing;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    using System.Windows.Forms;
    
    namespace SGFRenaissance
    {
        public partial class Form_DRE_Exercicio : Form
        {
            SqlConnection conn = new SqlConnection("Data Source=DESKTOP-3O98051;Initial Catalog=SGFRenaissance;Integrated Security=True");
            SqlCommand cmd;
            SqlDataAdapter adapt;
            decimal totalrevenues = 0;
            decimal totalCMV = 0;
            decimal totalTaxes = 0;
            decimal comissions = 0;
            decimal RentTotal = 0;
            decimal RentTotalExpenses;
            DateTime dateinicial;
            DateTime datefinal;
    
            public Form_DRE_Exercicio()
            {
                InitializeComponent();
            }
    
            void SelectRentFromTable()
            {
                try
                {
    
                    String StrConn;
                    StrConn = @"Data Source=DESKTOP-3O98051;Initial Catalog=SGFRenaissance;Integrated Security=True";
                    SqlConnection sqlConnection = new SqlConnection(StrConn);
                    DateTime inicialdate;
                    DateTime finaldate;
                    inicialdate = Convert.ToDateTime(mtbinicio.Text);
                    finaldate = Convert.ToDateTime(mtbfinal.Text);
                    sqlConnection.Open();
                    string sql = string.Format("select  Cod_Despesa, Valor_Pago from Base_Parcelas_Pagas where Cod_Despesa = 9 and Data_Pagamento Between ='inicialdate'and 'finaldate'"); // Should I use mtbinicio.Text and mtbfinal.Text instead?
                    SqlCommand sqlComm = new SqlCommand(sql, sqlConnection);// it didn't work either with mtbfinal.Text
                    SqlDataReader reader = sqlComm.ExecuteReader();
                    while (reader.Read())
                    {
                        string dr = reader["Valor_Pago"].ToString(); // reading the ammount paid "Valor_Pago"
                        Convert.ToDecimal(dr);
                        RentTotalExpenses = RentTotalExpenses + Convert.ToDecimal(dr);
                        RentTotal = RentTotalExpenses;
                    }
    
                    sqlConnection.Close();
    
                }
                catch  (Exception Ex)
                {
                    MessageBox.Show("Error!");
    
                }
    
            }
    
    
            private void btn_fechar_Click(object sender, EventArgs e)
            {
                this.Close();
                this.Dispose();
            }
    
            private void button_execute_Click(object sender, EventArgs e)
            {
                SelectRentFromTable();
                textBoxRent.Text = Convert.ToString(RentTotal);
            }
           
        }
    }

    Here follows the result of a SQL on a table (I'm using SQL Server2012)

    Use SGFRenaissance
    Select Cod_Base_Titulos_Pagos, Cod_Despesa, Valor_Pago from Base_Parcelas_Pagas Where  Cod_Despesa = 9 and Data_Pagamento Between '2019-07-20'  and '2019-08-03' 
    
          Cod_Base_Titulos   Cod_Despesa        Valor_Pago
    
                35                9                   300.00
                35                9                  1000.00
                35                9                  1000.00
                38                9                  3000.00
                45                9                    90.25
                45                9                  4700.00
                78                9                  1515.95
                79                9                  1515.95
                80                9                  1015.95

    How it shou be ? Can someone help-me with this code?

    Thanks a lot for your effort. 

    Flavio Rocha


    Wednesday, November 20, 2019 2:23 AM

All replies

  • Maybe, you should rethink on the Sum using a Sum function.

    https://docs.microsoft.com/en-us/sql/t-sql/functions/sum-transact-sql?view=sql-server-ver15

    I suspect that it works with the MS SQL Server 2012.

    You should also think about using parametrized T-SQL to mitigate SQL Injection Attacks now and in future coding using inline T-SQL. 

    https://www.codementor.io/satyaarya/prevent-sql-injection-attacks-in-net-ocfxkhnyf

    https://docs.microsoft.com/en-us/dotnet/api/system.data.sqlclient.sqlcommand.parameters?view=netframework-4.8

    Then all the datareader has to do is read the results of the Sum total column returned.


    Wednesday, November 20, 2019 5:15 AM
  • DA924X,

    Thanks for your help. I'll check all your suggestion.

    After that, I will post the correct code.

    Best Regards.

    Friday, November 22, 2019 12:48 AM
  • DA924x good evening,

    I did the changes you suggested, only with the parameters, not yet with the Injection Atacks I have to review all my codes to prevent that.

    I'm getting an Error with the Conversion dates, can you help me with that please?

    "Error! Conversion failed When converting date and/or time from character string"

    I'm using a datetimepicker. 

    here goes the new code. 

     
     void SelectRentFromTable()
            {
                try
                {
                   
                    String StrConn;
                    StrConn = @"Data Source=DESKTOP-3O98051;Initial Catalog=SGFRenaissance;Integrated Security=True";
                    SqlConnection sqlConnection = new SqlConnection(StrConn);
                    sqlConnection.Open();
                    string sql = string.Format("select SUM(Valor_Pago) from Base_Parcelas_Pagas where (Cod_Despesa = 9) and Data_Pagamento Between '@inicialdate'and '@finaldate'");
                    SqlCommand sqlComm = new SqlCommand(sql, sqlConnection);
    
                    DateTime dateinicial = Convert.ToDateTime(dtpInicio.Value.ToShortDateString());
                    sqlComm.Parameters.AddWithValue("@inicialdate", dateinicial);
    
                    DateTime datefinal = Convert.ToDateTime(dtpFinal.Value.ToShortDateString());
                    sqlComm.Parameters.AddWithValue("@finaldate", datefinal);
    
                    float TotalValorPago = (float)sqlComm.ExecuteScalar();
                    Decimal RentTotal = Convert.ToDecimal(TotalValorPago);
                    sqlConnection.Close();
    
                }
                catch  (Exception Ex)
                {
                    MessageBox.Show("Error!" + Ex.Message);
    
                }
    
            }
    
    
            private void btn_fechar_Click(object sender, EventArgs e)
            {
                this.Close();
                this.Dispose();
            }
    
            private void button_execute_Click(object sender, EventArgs e)
            {
                SelectRentFromTable();
                textBoxRent.Text = Convert.ToString(RentTotal);
            }
    
            private void dtpInicio_ValueChanged(object sender, EventArgs e)
            {
                dtpInicio.CustomFormat = "dd/MM/yyyy";
            }
    
            private void dtpFinal_ValueChanged(object sender, EventArgs e)
            {
                dtpFinal.CustomFormat = "dd/MM/yyyy";
            }
    
            private void Form_DRE_Exercicio_Load(object sender, EventArgs e)
            {
                dtpInicio.CustomFormat = "   ";
                dtpFinal.CustomFormat = "   ";
                dtpInicio.CustomFormat = null;
                dtpFinal.CustomFormat =null;
    
    
            }
        }
    Thanks a lot for your attention.
    Tuesday, November 26, 2019 2:35 AM
  • I like to add parms this way.

    SqlCommand command = new SqlCommand(commandText, connection);
            command.Parameters.Add("@inicialdate", SqlDbType.DateTime);
            command.Parameters["@inicialdate"].Value = dateinicial;

    The DTP.value is already in a datetime format of mm/dd/yyyy hh:mm:ss with time being the current O/S time the date select was made from the DTP.

    So I don't know what a conversion to a ToshortDateString is supposed to be doing and then converting it to a datetime only to have it be mm/dd/yyyy 12:00:00 AM

    dtpFinal.CustomFormat = "dd/MM/yyyy" is not setting DTP.value to dd/mm/yyyy if that is what you are trying to accomplish.

    Now, to check if a date is greater than date and less than a date or a date is between two dates, the date must be in  yyyy/mm/dd format and even the T-SQL Between statment in working with dates is using the yyyymmdd format as string data.

    https://docs.microsoft.com/en-us/sql/t-sql/language-elements/between-transact-sql?view=sql-server-ver15

    So, you will probably need to change the  date column in the database table to be a string column  to hold a string date in the yyyymmdd format.  And the date parms need to be in yyyymmdd string parms. 

    You can do this.

    string holddate = dtp.value.ToString("yyyy/mm/dd");

    Then you will have to strip out the "/" out of the string date using an IndexOf() and Substring() statements. 

    You could go another way by not making table column change and data change too  is to use Linq and the ADO.NET Entity Framework with using true DateTime everywhere as you're trying to do and the Sum() aggregate.

    https://www.c-sharpcorner.com/article/aggregate-function-with-linq-in-c-sharp/

    var sumtotal = (from a in context.Base_Parcelas_Pagas
                     where a.Cod_Despesa == 9
                     &&  a.Data_Pagamento >= dpt1.Value 
                     &&  a.Data_Pagamento <= dpt2.Value  select a).Sum(a.Valor_Pago);
    Your other option could be that you make a parmterized stored procedure and manipulate true datetime dates in the sproc with the parm dates and the date in the table  doing the Sum().





    • Edited by DA924x Tuesday, November 26, 2019 7:58 AM
    Tuesday, November 26, 2019 6:46 AM
  • Hi F.oliveirarocha, 

    Thank you for posting here.

    According to your question, I try to make a test on my side, but I need more information.

    >>select SUM(Valor_Pago) from Base_Parcelas_Pagas where (Cod_Despesa = 9) and Data_Pagamento Between '@inicialdate'and '@finaldate'

    Could you provide some data in your database or some results of the query? It will help us to create a database and make a test.

    Could you provide more details about your exception with which line of the code thrown the exception? It will beneficial for us to analyze your problem.

    We are waiting for your update.

    Best Regards,

    Xingyu Zhao


    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.

    Tuesday, November 26, 2019 9:13 AM
    Moderator
  • Hi, goog evening Xingyu Zhao,

    Ok, thanks for trying to help me.

    I will provide everything you need. 

    1- The execution of the query on the SQL Server - it runs ok, and return the result. You don't need to create all fields, only the selected fields.

    2- The table and its contents 

    3 - The complete code and the line where it's breaking.

    (1)-Use SGFRenaissance
        Select SUM(Valor_Pago) as TotalValorPago from Base_Parcelas_Pagas Where  (Cod_Despesa = 9) and Data_Pagamento Between '2019-07-20'  and '2019-08-03'; 
    
        Return: 14138.10
    
    Cod_Base_Titulos_Pagos	int	Unchecked
    Cod_Titulo_a_Pagar	int	Unchecked
    Cod_Numero_Parc_a_Pagar	int	Unchecked
    Cod_Fornecedor	        int	Checked
    Numero_Documento	varchar(50)	Checked
    Numero_Parcela	        int	Checked
    Valor_Titulo	        decimal(18, 2)	Checked
    Data_Documento     	datetime	Checked
    Data_Pagamento	        datetime	Checked
    Data_Venc_Parcela	datetime	Checked
    Cod_Centro_Custos	int	Checked
    Cod_Despesa	        int	Checked
    Valor_Pago_a_Vista	decimal(18, 2)	Checked
    Valor_Pago	        decimal(18, 2)	Checked
    Numero_banco	        int	Checked
    Numero_cheque	        varchar(25)	Checked
    Acrescimos	        decimal(18, 2)	Checked
    Descontos	        decimal(18, 2)	Checked
    Saldo	                decimal(18, 2)	Checked
    StatusTitulo	        int	Checked
    Historico	        varchar(200)	Checked
    Historico_Titulo	varchar(200)	Checked
    
    

    Here goes the table.

    (2) Here goes the table content - I'm just selecting the Cod_Despesa "9":
    
    35	1030	56	9	123456TXP	1	2390,77	2019-07-13 00:00:00.000	2019-07-20 00:00:00.000	2019-07-30 00:00:00.000	1	9	90,77	300,00	2	20072019ABC	0,00	0,00	0,00	2	 Teste inserir 1ª parcela - 13/07/2019 - 12:51 - Pagamento OK	Teste inserir título - 13/07/2019 - 12:50 
    35	1030	57	9	123456TXP	2	2390,77	2019-07-13 00:00:00.000	2019-07-20 00:00:00.000	2019-07-31 00:00:00.000	1	9	90,77	1000,00	2	20072019ABC	0,00	0,00	0,00	2	Teste Pagar 2ª parcela - 20/07/2019 - 13:03 - Pagamento OK Parc. 2	Teste inserir título - 13/07/2019 - 12:50 
    35	1030	58	9	123456TXP	3	2390,77	2019-07-13 00:00:00.000	2019-07-20 00:00:00.000	2019-08-13 00:00:00.000	1	9	90,77	1000,00	2	20072019ABC	0,00	0,00	0,00	2	 Teste Pagar 3ª parcela - 20/07/2019 - 13:04 - Parc.03 - OK - Excluir Título 1030	Teste inserir título - 13/07/2019 - 12:50 
    36	1031	59	7	987654ABC	1	3570,00	2019-07-15 00:00:00.000	2019-07-20 00:00:00.000	2019-08-05 00:00:00.000	3	3	0,00	500,00	1	20072019XPTO	0,00	0,00	0,00	2	Teste Pagar 1ª parcela - 20/07/2019 - 13:17 Parc.01	Teste inserir título com 2 parcelas - 13/07/2019 - 12:56
    36	1031	60	7	987654ABC	2	3570,00	2019-07-15 00:00:00.000	2019-07-20 00:00:00.000	2019-08-06 00:00:00.000	3	3	0,00	3000,00	1	20072019XPTO	0,00	0,00	0,00	2	Teste Pagar  2ª parcela - 20/072019 - 13:18 e Excluir Título 1031	Teste inserir título com 2 parcelas - 13/07/2019 - 12:56
    37	1032	61	5	765705ABC	1	3590,77	2019-07-14 00:00:00.000	2019-07-20 00:00:00.000	2019-07-25 00:00:00.000	2	2	0,00	90,77	3	20072019xyz	0,00	0,00	0,00	2	 Teste Pagar Parcela - Cod.Tit.a Pagar - 1032 - 20/07/2019 - 13:50 - Parc.01	Teste Inserir Tít. 1032 - 17/07/2019 - 12:51
    38	1033	62	11	14072019XYZ	1	2790,77	2019-07-14 00:00:00.000	2019-07-20 00:00:00.000	2019-07-30 00:00:00.000	4	3	0,00	90,77	3	20072019AVG	0,00	0,00	0,00	2	Teste Pagar Parcela 1 - Tit.1033 - 20/07/2019 - 16:14	Teste Inserir Título Novo 1032 - 14/07/2019 - 14:20
    38	1033	63	11	14072019XYZ	2	2790,77	2019-07-14 00:00:00.000	2019-07-20 00:00:00.000	2019-07-31 00:00:00.000	4	3	0,00	700,00	3	20072019AVG	0,00	0,00	0,00	2	Teste Pagarr Parcela - 20/07/2019 - 16:15	Teste Inserir Título Novo 1032 - 14/07/2019 - 14:20
    38	1033	64	11	14072019XYZ	3	2790,77	2019-07-14 00:00:00.000	2019-07-20 00:00:00.000	2019-08-14 00:00:00.000	4	3	0,00	2000,00	3	20072019AVG	0,00	0,00	0,00	2	Teste Pagar Parcela 3 - Tit. 1033 - 20/07/2019 - 16:19	Teste Inserir Título Novo 1032 - 14/07/2019 - 14:20
    37	1032	65	5	765705ABC	2	3590,77	2019-07-14 00:00:00.000	2019-07-20 00:00:00.000	2019-08-12 00:00:00.000	2	2	0,00	590,77	3	20072019xyz	0,00	0,00	0,00	2	Teste Pagar Parcela 2 - Tit. 1032 - 20/07/2019 - 13:52 - Parc.02	Teste Inserir Tít. 1032 - 17/07/2019 - 12:51
    37	1032	66	5	765705ABC	3	3590,77	2019-07-14 00:00:00.000	2019-07-20 00:00:00.000	2019-08-15 00:00:00.000	2	2	0,00	3000,00	3	20072019xyz	0,00	0,00	0,00	2	Teste Pagar Parcela 3 - Tit. 1032 - 20/07/2019 - 13:55	Teste Inserir Tít. 1032 - 17/07/2019 - 12:51
    40	1034	67	6	111222333	1	5670,88	2019-07-14 00:00:00.000	2019-07-21 00:00:00.000	2019-08-09 00:00:00.000	1	3	0,00	670,00	3	21072019XYZ	0,00	0,00	0,00	2	Teste Inserir Parcela 1 - Tit. 1034 - 21/07/2019 - 10:28	Teste Inserir nova Parc. Tit. 1034 - 21/07/2019 - 10:06 - Parcela 03
    40	1034	68	6	111222333	2	5670,88	2019-07-14 00:00:00.000	2019-07-21 00:00:00.000	2019-08-22 00:00:00.000	1	3	0,00	5000,88	3	21072019XYZ	0,00	0,00	0,00	2	Teste Pagar Parcela - Tit. 1034 - 21/07/2019 - 10:29	Teste Inserir nova Parc. Tit. 1034 - 21/07/2019 - 10:06 - Parcela 03
    39	1037	69	7	1037ABC	1	2590,77	2019-07-20 00:00:00.000	2019-07-20 00:00:00.000	2019-08-07 00:00:00.000	1	7	0,00	1500,00	3	1525ABC	0,00	0,00	0,00	2	Teste Pagar Parcela - Parc. 01 - Tit.1037- 20/07/2019 - 16:25	Teste Inserir Parcela - 20/07/2019
    38	1038	72	11	20072019QXM	3	3590,25	2019-07-20 00:00:00.000	2019-07-20 00:00:00.000	2019-08-20 00:00:00.000	4	9	0,00	3000,00	3	20072019AVG	0,00	0,00	0,00	2	Teste Pagar 3ª Parcela - 1038 - 20/07/2019 - 16:18	Teste Inserir Parcelas - Tit. 1038 - 20/07/2019 - 15:54
    40	1034	73	6	111222333	3	5670,88	2019-07-14 00:00:00.000	2019-07-21 00:00:00.000	2019-08-12 00:00:00.000	1	3	0,00	1000,00	3	21072019XYZ	0,00	0,00	0,00	2	Teste Pagar Parcela - 03 - Tit. 1034 - 21/07/2019 - 10:30	Teste Inserir nova Parc. Tit. 1034 - 21/07/2019 - 10:06 - Parcela 03
    41	1039	74	3	22072019ABC	1	1590,77	2019-07-22 00:00:00.000	2019-07-22 00:00:00.000	2019-08-21 00:00:00.000	1	3	0,00	750,00	1	123456PLM	0,00	0,00	0,00	2	Teste Inserir parcela	Teste Inserir parcela 
    41	1039	75	3	22072019ABC	2	1590,77	2019-07-22 00:00:00.000	2019-07-22 00:00:00.000	2019-08-24 00:00:00.000	1	3	0,00	840,77	1	123456PLM	0,00	0,00	0,00	2	Teste inserir parcela - 22/07/2019 - 23:24	Teste Inserir parcela 
    42	1040	76	7	123456ABCD	1	1492,55	2019-07-22 00:00:00.000	2019-07-30 00:00:00.000	2019-07-31 00:00:00.000	2	4	0,00	92,55	3	234567QAX	0,00	0,00	0,00	2	 Teste Pagar  Parcela - 01 23/07/2019 - 00:05 - Cod.Tit. 1040	Teste Incluir novo Título - 1040 - 22/07/2019 - 23:39
    42	1040	77	7	123456ABCD	2	1492,55	2019-07-22 00:00:00.000	2019-07-30 00:00:00.000	2019-08-21 00:00:00.000	2	4	0,00	1400,00	3	234567QAX	0,00	0,00	0,00	2	Teste Pagar nova Parcela (2) - 1040 - 23/07/2019 - 00:06 	Teste Incluir novo Título - 1040 - 22/07/2019 - 23:39
    43	1041	78	9	789512QXZ	1	5890,13	2019-07-29 00:00:00.000	2019-07-23 00:00:00.000	2019-08-05 00:00:00.000	3	8	0,00	90,13	3	254783POT	0,00	0,00	0,00	2	Teste Salvar Parcela - 1041 - parc.01	Teste Inserir 3 parcelas - 1041 - 22/07/2019 - 23:45
    43	1041	79	9	789512QXZ	2	5890,13	2019-07-29 00:00:00.000	2019-07-23 00:00:00.000	2019-08-10 00:00:00.000	3	8	0,00	800,00	3	254783POT	0,00	0,00	0,00	2	Teste Inserir parcela 02 - 22/07/2019 - 23:47	Teste Inserir 3 parcelas - 1041 - 22/07/2019 - 23:45
    44	1041	80	9	789512QXZ	3	5890,13	2019-07-29 00:00:00.000	2019-07-25 00:00:00.000	2019-08-15 00:00:00.000	3	8	0,00	4900,00	1	25072019ABC	0,00	100,00	-200,00	3	Teste inserir 3ª parcela - 22/07/2019 	Teste Inserir 3 parcelas - 1041 - 22/07/2019 - 23:45
    44	1042	81	6	895741MNB	1	5675,25	2019-07-31 00:00:00.000	2019-07-25 00:00:00.000	2019-08-14 00:00:00.000	5	8	0,00	1000,00	1	25072019ABC	120,00	70,00	50,00	2	Teste inserir parcela 1042 	Teste Inserir novo Título - 1042 - 23:07
    44	1042	82	6	895741MNB	2	5675,25	2019-07-31 00:00:00.000	2019-07-25 00:00:00.000	2019-08-16 00:00:00.000	5	8	0,00	4900,00	1	25072019ABC	0,00	100,00	-200,00	3	Teste inserir parcela 1042 - parc. 02	Teste Inserir novo Título - 1042 - 23:07
    45	1043	83	11	25072019ABC	1	4790,25	2019-07-25 00:00:00.000	2019-07-25 00:00:00.000	2019-07-31 00:00:00.000	5	9	0,00	90,25	3	25072019ABC	0,00	0,00	0,00	2	Teste pagar parcela - 45  - 25/07/2019 - 21:58 	Teste Inserir novo Título - 1043 - 25/07/2019 - 21:21 
    45	1043	84	11	25072019ABC	2	4790,25	2019-07-25 00:00:00.000	2019-07-25 00:00:00.000	2019-08-08 00:00:00.000	5	9	0,00	4700,00	3	25072019ABC	0,00	0,00	0,00	2	Teste Pagar 2ª parcela - 45 - 25/07/2019 - 22:01	Teste Inserir novo Título - 1043 - 25/07/2019 - 21:21 
    46	1044	85	6	25072019ABV	1	3570,12	2019-07-25 00:00:00.000	2019-07-25 00:00:00.000	2019-08-13 00:00:00.000	4	6	0,00	75,89	3	25072019TSE	5,00	0,00	-10,77	2	Teste Inseir parcela - 1044 - 25/07/2019 - 21:52	Teste Inserir Titulo novo 1044 - 25/07/2019 - 21:48
    47	1044	86	6	25072019ABV	2	3570,12	2019-07-25 00:00:00.000	2019-07-25 00:00:00.000	2019-08-15 00:00:00.000	4	6	0,00	3500,00	2	25072019QAC	12,53	100,00	-112,53	2	 Teste Pagar Parcela - 02 - 25/07/2019 - 22:35 - Cod.Base.Tit. 47	Teste Inserir Titulo novo 1044 - 25/07/2019 - 21:48
    48	1045	87	4	25072019AVC	1	3578,93	2019-07-25 00:00:00.000	2019-07-25 00:00:00.000	2019-08-13 00:00:00.000	2	7	0,00	70,00	2	25072019ZWY	11,93	8,93	11,93	3	Teste Pagar Parc. 01 - 1045 - 25/07/2019 - 22:53	Teste  Inserir Título - 1045 - 25/07/2019 - 22:30
    49	1045	88	4	25072019AVC	2	3578,93	2019-07-25 00:00:00.000	2019-07-25 00:00:00.000	2019-08-15 00:00:00.000	2	7	0,00	3500,00	2	25072019ABY	25,19	25,19	50,38	2	Teste Inserir Parc. 02 - 25/07/2019 -22:32	Teste  Inserir Título - 1045 - 25/07/2019 - 22:30
    50	1046	89	4	25072019WXY	1	5790,33	2019-07-25 00:00:00.000	2019-07-25 00:00:00.000	2019-08-22 00:00:00.000	2	3	0,00	795,33	1	2507QWETR	5,93	10,93	21,86	2	 Teste Inserir 1ª Parc. 1046 - 25/07/2019 	Teste Inserir Título - 1046 - 25/07/2019 - 22:46
    51	1046	90	4	25072019WXY	2	5790,33	2019-07-25 00:00:00.000	2019-07-25 00:00:00.000	2019-08-23 00:00:00.000	2	3	0,00	2752,50	2	25072019KYL	0,00	2752,50	0,00	2	Teste Inserir 2ª parcela - Cod.Tit. 1046 - 25/07/2019 - 22:49	Teste Inserir Título - 1046 - 25/07/2019 - 22:46
    66	1046	91	4	25072019WXY	3	5790,33	2019-07-25 00:00:00.000	2019-07-26 00:00:00.000	2019-08-24 00:00:00.000	2	3	0,00	2507,00	2	123456puo	7,00	13,00	20,00	2	Teste Inserir Parc. 3ª parc. - Cod.Tit.1046 - 25/07/2019 - 22:50	Teste Inserir Título - 1046 - 25/07/2019 - 22:46
    73	1047	92	6	10472507KYG	1	7588,75	2019-07-25 00:00:00.000	2019-07-26 00:00:00.000	2019-07-31 00:00:00.000	3	6	0,00	581,75	2	45632178ACV	1,75	7,00	0,00	2	Teste Inserir nova Parc. 01 - Tit; 1047 - 23:44	Teste Inserir - Tit. 1047 - 25/07/2019 - 23:43
    76	1047	93	6	10472507KYG	2	7588,75	2019-07-25 00:00:00.000	2019-07-26 00:00:00.000	2019-08-02 00:00:00.000	3	6	0,00	3550,00	2	1234566tyu	150,00	50,00	100,00	2	Teste Inserir Parc. 02 - Tit. 1047 - 25/07 	Teste Inserir - Tit. 1047 - 25/07/2019 - 23:43
    77	1047	94	6	10472507KYG	3	7588,75	2019-07-25 00:00:00.000	2019-07-27 00:00:00.000	2019-09-07 00:00:00.000	3	6	0,00	2041,10	2	123458AVC	20,55	500,00	-958,90	2	Teste Pagar Parc. 03 Tit. 1047 - 27/07 - 10:38 	Teste Inserir - Tit. 1047 - 25/07/2019 - 23:43
    78	1048	95	11	27062019ABC	1	4500,00	2019-07-27 00:00:00.000	2019-07-29 00:00:00.000	2019-08-13 00:00:00.000	2	9	0,00	1515,95	2	1234562707ABC	15,95	500,00	515,95	2	Teste pagar parcela 1048 - 27/07 -10:50 	TEste inserir título 1048 - 27/07
    79	1048	96	11	27062019ABC	2	4500,00	2019-07-27 00:00:00.000	2019-07-29 00:00:00.000	2019-08-16 00:00:00.000	2	9	0,00	1515,95	3	270780AVC	15,95	500,00	515,95	2	TEste Pagar  2ª parcela - 1048 - 27/07 - 10:10	TEste inserir título 1048 - 27/07
    80	1048	97	11	27062019ABC	3	4500,00	2019-07-27 00:00:00.000	2019-07-30 00:00:00.000	2019-08-20 00:00:00.000	2	9	0,00	1015,95	2	8027072019AVC	15,95	500,00	15,95	2	Teste Pagar  parcela 1048 - 3ª parcela - 27/07 - 11:16 	TEste inserir título 1048 - 27/07
    81	1049	99	6	10492107QER	2	3590,33	2019-07-30 00:00:00.000	2019-07-29 00:00:00.000	2019-08-02 00:00:00.000	2	8	0,00	411,90	2	10492707ABCD	11,90	0,00	-88,10	3	Teste inserir 2ª parcela - 1049 - 27/07/2019 - 10:17	Teste Inserir Titulo - 1049 - 27/07/2019 - 10:15
    82	1050	102	5	105027072019TXY	2	4500,00	2019-07-31 00:00:00.000	2019-07-29 00:00:00.000	2019-08-03 11:53:41.000	2	5	0,00	2011,25	2	123456789QAZ	11,25	0,00	11,25	2	Teste Inserir 2ª parcela - 11:59 - 27/07/2019	Teste Inserir novo Título - 1050 - 27/07/2019 - 11:55
    83	1051	105	7	105127072019PBM	2	5500,91	2019-07-29 00:00:00.000	2019-07-31 00:00:00.000	2019-08-16 11:53:41.000	3	6	0,00	2512,90	3	8327072019AB	12,90	0,00	12,90	2	Teste Pagar  2ª parcela - 1051 - 27/07 - 14:08	Teste Inserir novo Título 1051 - 27/07/2019 - 12:02
    84	1051	107	7	105127072019PBM	4	5500,91	2019-07-29 00:00:00.000	2019-07-30 00:00:00.000	2019-08-20 11:53:41.000	3	6	0,00	1513,50	3	8427072019ACB	13,50	0,00	-486,50	3	Teste Pagar parc. 04 - Tit. 1051 - 27/07 - 14:39	Teste Inserir novo Título 1051 - 27/07/2019 - 12:02
    84	1052	108	9	270720191052AB	1	7592,25	2019-07-29 00:00:00.000	2019-07-30 00:00:00.000	2019-07-30 13:45:02.000	4	6	0,00	101,50	3	8427072019ACB	9,25	0,00	9,25	2	Teste Pagar  parc. 01 - Tit. 1052 - 27/07/2019 - 14:41 	Teste Inserir Título - 1052 - 27/07/2019 - 13:47
    84	1052	109	9	270720191052AB	2	7592,25	2019-07-29 00:00:00.000	2019-07-30 00:00:00.000	2019-08-08 13:45:02.000	4	6	0,00	462,95	3	8427072019ACB	12,95	50,00	12,95	2	Teste pagar 2ª parcela - Tit. 1052 - 27/07 - 14:44 	Teste Inserir Título - 1052 - 27/07/2019 - 13:47
    85	1052	110	9	270720191052AB	3	7592,25	2019-07-29 00:00:00.000	2019-07-31 00:00:00.000	2019-08-20 13:45:02.000	4	6	0,00	7000,00	1	8527072019AGF	0,00	0,00	0,00	2	Teste Inserir 3ª parcela - Tit. 1052 - 27/07/2019 - 13:50	Teste Inserir Título - 1052 - 27/07/2019 - 13:47
    85	1053	111	5	10522707ACB	1	5650,99	2019-07-25 00:00:00.000	2019-07-31 00:00:00.000	2019-07-31 14:53:38.000	3	3	0,00	653,79	1	8527072019AGF	2,80	0,00	2,80	2	Teste Pagar Parc. 01 Tit 1053 - 27/07  - 15:07	Teste Inserir novo Tit. 1053 - 27/07/2019 - 14:55
    86	1053	112	5	10522707ACB	2	5650,99	2019-07-25 00:00:00.000	2019-07-29 00:00:00.000	2019-08-13 14:53:38.000	3	3	0,00	2000,00	2	8627072019PYT	0,00	0,00	-500,00	3	Teste Inserir Parc. 02 - Tit. 1053 - 27/07 - 14:57	Teste Inserir novo Tit. 1053 - 27/07/2019 - 14:55
    86	1053	113	5	10522707ACB	3	5650,99	2019-07-25 00:00:00.000	2019-07-29 00:00:00.000	2019-08-15 14:53:38.000	3	3	0,00	2513,85	2	8627072019PYT	13,85	0,00	13,85	2	Teste inserir 3ª parcela - Tit. 1053 - 27/07 - 14:58	Teste Inserir novo Tit. 1053 - 27/07/2019 - 14:55
    87	1054	114	10	10542707ASDF	1	8925,82	2019-07-29 00:00:00.000	2019-07-31 00:00:00.000	2019-08-15 14:53:38.000	2	7	0,00	930,10	1	87270719AGB	4,28	0,00	4,28	2	Teste Pagar  Parc. 01 - Tit. 1054 - 27/07 - 15:35	Teste Inserir Tit. 1054 - 27/07/2019 - 15:01 
    87	1054	115	10	10542707ASDF	2	8925,82	2019-07-29 00:00:00.000	2019-07-31 00:00:00.000	2019-08-20 14:53:38.000	2	7	0,00	8000,00	1	87270719AGB	0,00	0,00	0,00	2	Teste Pagar 2ª parcela - Tit. 1054 - 27/07 - 15:35	Teste Inserir Tit. 1054 - 27/07/2019 - 15:01 
    88	1055	116	9	105527072019AS	1	5640,88	2019-07-29 00:00:00.000	2019-07-30 00:00:00.000	2019-08-09 15:18:36.000	3	2	0,00	642,00	2	8827072019ABC	1,12	0,00	1,12	2	Teste Pagar  Parc. 01 - Tit. 1055 - 27/07 - 15:43	Teste Inserir Tit. 1055 - 27/07/2019 - 15:20
    88	1055	117	9	105527072019AS	2	5640,88	2019-07-29 00:00:00.000	2019-07-30 00:00:00.000	2019-08-21 15:18:36.000	3	2	0,00	2500,00	2	8827072019ABC	0,00	0,00	0,00	2	Teste Pagar parc. 02 - 27/07/2019 - 15:44	Teste Inserir Tit. 1055 - 27/07/2019 - 15:20
    89	1055	118	9	105527072019AS	3	5640,88	2019-07-29 00:00:00.000	2019-07-31 00:00:00.000	2019-08-24 15:18:36.000	3	2	0,00	2515,58	1	892707QWR	15,58	0,00	15,58	2	Teste Pagarr parc. 03 - Tit. 1055 - 27/07/2019 - 15:23	Teste Inserir Tit. 1055 - 27/07/2019 - 15:20
    89	1056	119	4	1056270719AET	1	3789,21	2019-07-25 00:00:00.000	2019-07-31 00:00:00.000	2019-08-13 15:23:21.000	5	6	0,00	790,00	1	892707QWR	0,79	0,00	0,79	2	Teste Pagar  Par. 01 - 27/07/2019 - 15:47 	Teste Inserir Tit. 1056 - 27/07/2019 - 15:25
    90	1056	120	4	1056270719AET	2	3789,21	2019-07-25 00:00:00.000	2019-07-31 00:00:00.000	2019-08-24 15:23:21.000	5	6	0,00	3000,00	3	892707RTY	0,00	0,00	0,00	2	Teste Pagar parc. 02 - Tit. 1056 - 27/07 -  16:06 	Teste Inserir Tit. 1056 - 27/07/2019 - 15:25
    90	1057	121	11	105727072019	1	2589,99	2019-07-30 00:00:00.000	2019-07-31 00:00:00.000	2019-08-21 15:57:10.000	2	1	0,00	89,99	3	892707RTY	0,00	0,00	0,00	2	Teste Pagar  Parc. 01 - Tit. 1057 - 27/07 - 16:07 	Teste 1057 - 27/07/2019 - 15:58
    91	1057	122	11	105727072019	2	2589,99	2019-07-30 00:00:00.000	2019-08-01 00:00:00.000	2019-08-22 15:57:10.000	2	1	0,00	515,89	1	9127072019QWC	15,89	0,00	15,89	2	Teste Pagar Parc. 02 - Tit. 1057 - 27/07 - 16:00	Teste 1057 - 27/07/2019 - 15:58
    91	1057	123	11	105727072019	3	2589,99	2019-07-30 00:00:00.000	2019-08-01 00:00:00.000	2019-08-29 15:57:10.000	2	1	0,00	2100,87	1	9127072019QWC	100,87	0,00	100,87	2	Teste pagar Parc. 03 - Tit. 1057 - 27/07 - 16:11	Teste 1057 - 27/07/2019 - 15:58
    92	1058	124	3	105827072019TUV	1	5897,23	2019-07-31 00:00:00.000	2019-08-03 00:00:00.000	2019-08-21 15:57:10.000	1	5	0,00	897,23	2	03082019ADF	0,00	0,00	0,00	2	Teste inserir Parc. 01 - Tit. 1058 - 16:03	Teste Inserir Tit. 1058 - 27/07 /2019 - 16:02
    NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL	NULL

    using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Data.SqlClient; using System.Drawing; using System.Linq; using System.Text; using System.Threading.Tasks; using System.Windows.Forms; using System.Globalization; namespace SGFRenaissance { public partial class Form_DRE_Exercicio : Form { SqlConnection conn = new SqlConnection("Data Source=DESKTOP-3O98051;Initial Catalog=SGFRenaissance;Integrated Security=True"); SqlCommand cmd; SqlDataAdapter adapt; decimal totalrevenues = 0; decimal totalCMV = 0; decimal totalTaxes = 0; decimal comissions = 0; decimal RentTotal = 0; decimal RentTotalExpenses; public Form_DRE_Exercicio() { InitializeComponent(); } void SelectRentFromTable() { try { String StrConn; StrConn = @"Data Source=DESKTOP-3O98051;Initial Catalog=SGFRenaissance;Integrated Security=True"; SqlConnection sqlConnection = new SqlConnection(StrConn); sqlConnection.Open(); string sql = string.Format("select SUM(Valor_Pago) from Base_Parcelas_Pagas where Cod_Despesa = 9 and Data_Pagamento Between @inicialdate and @finaldate"); SqlCommand sqlComm = new SqlCommand(sql, sqlConnection); DateTime dateinicial = Convert.ToDateTime(dtpInicio.Value.ToShortDateString()); sqlComm.Parameters.AddWithValue("@inicialdate", dateinicial); DateTime datefinal = Convert.ToDateTime(dtpFinal.Value.ToShortDateString()); sqlComm.Parameters.AddWithValue("@finaldate", datefinal); float TotalValorPago = (float)sqlComm.ExecuteScalar(); Decimal RentTotal = Convert.ToDecimal(TotalValorPago); // after ExecuteScalar an Error message shows: "Error! Specified Conversion is not Valid" System breaks here.

    // I translated the Error message. It was in Portuguese. sqlConnection.Close(); } catch (Exception Ex) { MessageBox.Show("Error!" + Ex.Message); } } private void btn_fechar_Click(object sender, EventArgs e) { this.Close(); this.Dispose(); } private void button_execute_Click(object sender, EventArgs e) { SelectRentFromTable(); textBoxRent.Text = Convert.ToString(RentTotal); } private void dtpInicio_ValueChanged(object sender, EventArgs e) { dtpInicio.CustomFormat = "dd/MM/yyyy"; } private void dtpFinal_ValueChanged(object sender, EventArgs e) { dtpFinal.CustomFormat = "dd/MM/yyyy"; } private void Form_DRE_Exercicio_Load(object sender, EventArgs e) { dtpInicio.CustomFormat = " "; dtpFinal.CustomFormat = " "; dtpInicio.CustomFormat = null; dtpFinal.CustomFormat =null; } } }

    Thanks for your help. I really appreciate your kindness.

    Best Regards,

    Flávio Rocha.

    Wednesday, November 27, 2019 2:11 AM
  • Hi F.oliveirarocha, 

    Thanks for your feedback.

    I use your information to make a test on my side, and I suggest you not cast result of the query to float.

                    //float TotalValorPago = (float)sqlComm.ExecuteScalar();
                    var TotalValorPago = sqlComm.ExecuteScalar();
                    RentTotal = Convert.ToDecimal(TotalValorPago);
    

    My database(include five data):

    My table designer:

    Code:

            decimal RentTotal = 0;
            void SelectRentFromTable()
            {
                try
                {
                    string StrConn;
                    StrConn = @"Data Source=… ";
                    SqlConnection sqlConnection = new SqlConnection(StrConn);
                    sqlConnection.Open();
                    string sql = string.Format("select SUM(Valor_Pago) from TableTest where Data_Pagamento Between @inicialdate and @finaldate");
                    SqlCommand sqlComm = new SqlCommand(sql, sqlConnection);
    
                    DateTime dateinicial = Convert.ToDateTime(dtpInicio.Value.ToShortDateString());
                    sqlComm.Parameters.AddWithValue("@inicialdate", dateinicial);
    
                    DateTime datefinal = Convert.ToDateTime(dtpFinal.Value.ToShortDateString());
                    sqlComm.Parameters.AddWithValue("@finaldate", datefinal);
    
                    //float TotalValorPago = (float)sqlComm.ExecuteScalar();
                    var TotalValorPago = sqlComm.ExecuteScalar();
                    RentTotal = Convert.ToDecimal(TotalValorPago);
                    sqlConnection.Close();
                }
               catch (Exception Ex)
                {
                    MessageBox.Show("Error!" + Ex.Message);
                }
            }
    

    Result of my test:

    Hope it can help you.

    Best Regards,

    Xingyu Zhao



    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.

    Wednesday, November 27, 2019 7:57 AM
    Moderator
  • I suspect that the problem has to why an exception was thrown possibly is that the date parameters need to be string  values representing the date and not true datetime values.

    Because after all, the statement being formatted is string and you are trying to use parms that are true datetime values. The exception I think is not being thrown until you try to execute the ExecuteScalar

     string sql = string.Format("select SUM(Valor_Pago) from Base_Parcelas_Pagas where (Cod_Despesa = 9) and Data_Pagamento Between '@inicialdate'and '@finaldate'");

    https://www.afterhoursprogramming.com/tutorial/sql/date-and-datetime/

    I don't think the above inline T-SQL is going to work using the true datetime or as passing parms into the inline T-SQL as true datetime.

    I think the passing of the parms a true datetime will work if you were using a parmterized stored procedure.

    https://support.microsoft.com/en-us/help/310070/how-to-call-a-parameterized-stored-procedure-by-using-ado-net-and-visu

    You can also ask questions at the Transact T-SQL forum. I think your on the right path, but you have to figure out how to use the dates.

    They know C# in the forum too, and they should be able to help you.

    https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?forum=transactsql

    Replace the code in the private Form_Load event with the following code:SQL Client
    SqlConnection PubsConn = new SqlConnection 
    ("Data Source=server;integrated " + 
    "Security=sspi;initial catalog=pubs;");
    SqlCommand testCMD = new SqlCommand 
    ("TestProcedure", PubsConn);
    
    testCMD.CommandType = CommandType.StoredProcedure;
    
    SqlParameter RetVal = testCMD.Parameters.Add 
       ("RetVal", SqlDbType.Int);
    RetVal.Direction = ParameterDirection.ReturnValue;
    SqlParameter IdIn = testCMD.Parameters.Add 
      ("@au_idIN", SqlDbType.VarChar, 11);
    IdIn.Direction = ParameterDirection.Input;
    SqlParameter NumTitles = testCMD.Parameters.Add 
       ("@numtitlesout", SqlDbType.VarChar, 11);
    NumTitles.Direction = ParameterDirection.Output ;
            
    IdIn.Value = "213-46-8915";
    PubsConn.Open();
    
    SqlDataReader myReader = testCMD.ExecuteReader();
    Console.WriteLine ("Book Titles for this Author:");
    while (myReader.Read()) 
       {
         Console.WriteLine ("{0}", myReader.GetString (2));
       };
    myReader.Close() ;
    Console.WriteLine("Number of Rows: " + NumTitles.Value );
    Console.WriteLine("Return Value: " + RetVal.Value);



    • Edited by DA924x Wednesday, November 27, 2019 9:36 AM
    Wednesday, November 27, 2019 9:33 AM
  • Good morning Xingyu Zhao,

    I made your suggestion changing the float to a var and the sum came up to a "0" (zero).

    It does not make an error message, but the sum does not execute.

    Shows zero! 

    What coul be now?

    Best regards and thanks for your help.

    Wednesday, November 27, 2019 2:10 PM
  • Good afternoon DA924x,

    I'm making the test you suggested with the expression Ling-to-SQL

    Something is not right is not compiling.

    Can you try again to help-me please.

     private void btn_Execute_Test_Click(object sender, EventArgs e)
            {
                var sumtotal = (from a in context.Base_Parcelas_Pagas
                                where a.Cod_Despesa == 9
                                && a.Data_Pagamento >= dtpInicio.Value
                                && a.Data_Pagamento <= dtpFinal.Value
                                select a).Sum(a.Valor_Pago);
            }
    
    // the word context does not execute.
    // the Sum(a.Valor_Pago) "a" does not execute.
    

    Let's see if it will work please explain what should be in these words what it means

    variable, table or what else?

    Thanks and best regards.

    Wednesday, November 27, 2019 3:39 PM
  • This is a simple Linq-2-SQL tutorial that will show you how to correctly use Linq-2-SQL and do the Ling query.

    https://www.codeproject.com/Articles/26657/Simple-LINQ-to-SQL-in-C

    Wednesday, November 27, 2019 4:27 PM