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

[F.oliveirarocha]

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

Continue reading...