计算每个id和每个期间的行

我想显示一个DataGridView，其中包含Compte Name以及每月和每年Recette和Depense的总和。我有一个DateTimePicker，它是按月份和年份定制的。Winforms的图像和错误

示例：如果我选择日期=11/2015（2015年11月），它将显示为

Compte  Total   
BTK     633,000
Biat    987,888
Caisse  988,875

如果我选择日期=06/2016（2016年6月），它将显示为

Compte  Total   
BTK     0
Biat    653,256
Caisse  88,145

我的代码：

public partial class ComptesMois : Form
{
    SqlConnection conn;
    SqlCommand comm;
    SqlDataReader dreader;
    string connstring = "Data Source=.;Initial Catalog=Tresorerie;Integrated Security=True";
    public ComptesMois()
    {
        InitializeComponent();
        //Set Columns Count
        DataGridViewCompteMois.ColumnCount = 2;
        //Hide the last blank line
        DataGridViewCompteMois.AllowUserToAddRows = false;
        //Add Columns
        DataGridViewCompteMois.Columns[0].Name = "NomCom";
        DataGridViewCompteMois.Columns[0].HeaderText = "Nom de Compte";
        DataGridViewCompteMois.Columns[0].DataPropertyName = "NomCom";
        DataGridViewCompteMois.Columns[0].Width = 100;
        //Add Columns
        DataGridViewCompteMois.Columns[1].Name = "Total";
        DataGridViewCompteMois.Columns[1].HeaderText = "Total";
        DataGridViewCompteMois.Columns[1].DataPropertyName = "Total";
        DataGridViewCompteMois.Columns[1].Width = 100;
        this.BindGrid();
        CompteMoisdateTimePicker.Format = DateTimePickerFormat.Custom;
        CompteMoisdateTimePicker.CustomFormat = "MM yyyy";
        //CompteMoisdateTimePicker.ShowUpDown = true; // to prevent the calendar from being displayed
    }
    private void BindGrid()
    {
        DataGridViewCompteMois.DataSource = null;
        using (SqlConnection con = new SqlConnection(connstring))
        {
            using (SqlCommand cmd = new SqlCommand("select DISTINCT c.NomCom from Tresorerie t join Compte c on t.IdCom=c.IdCom", con))
            {
                cmd.CommandType = CommandType.Text;
                using (SqlDataAdapter sda = new SqlDataAdapter(cmd))
                {
                    using (DataTable dt = new DataTable())
                    {
                        sda.Fill(dt);
                        DataGridViewCompteMois.DataSource = dt;
                    }
                }
            }
        }
    }

    private void DataGridViewCompteMois_CellContentClick(object sender, DataGridViewCellEventArgs e)
    {
        string month = CompteMoisdateTimePicker.Value.Month.ToString();
        string year = CompteMoisdateTimePicker.Value.Year.ToString();
        //select * from < table > where month(searchDate) = Month_from_box and Year(searchDate) = Year_from_box
        // select sum(recette + depence) from Tresorerie where 
        String total;
        for (int i = 0; i < DataGridViewCompteMois.Rows.Count; i++)
        {
            conn = new SqlConnection(connstring);
            conn.Open();
            String NomCompteDataGrid = Convert.ToString(DataGridViewCompteMois.Rows[i].Cells[0].Value);
            String IdCompteValue = "select IdCom from Compte where NomCom='" + NomCompteDataGrid + "'";
            comm = new SqlCommand(IdCompteValue, conn);
            int IdComValue = Convert.ToInt32(comm.ExecuteScalar());
            conn.Close();
            using (SqlConnection con = new SqlConnection(connstring))
            {
                using (SqlCommand cmd = new SqlCommand("select sum(Recette + Depense) as Total from Tresorerie where IdCom=@IdCom GROUP BY Recette,Depense", con))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.AddWithValue("@IdCom", IdComValue);
                    con.Open();
                    total = Convert.ToString(cmd.ExecuteNonQuery());
                    con.Close();
                }
            }
            DataGridViewCompteMois.Rows[i].Cells[1].Value = total;
        }
    }
}

但是有一个错误，结果显示每个Compte 为-1

更新1:多亏了@Soner_Gönül，我修改了查询语句，并在sql上测试了它，它有效，但当我将结果添加到网格单元格时，问题是它显示为空，但我试图用messgeae框显示它，它也有效，我的代码：

 String total;
    private void CompteMoisdateTimePicker_ValueChanged(object sender, EventArgs e)
    {
        string month = CompteMoisdateTimePicker.Value.Month.ToString();
        string year = CompteMoisdateTimePicker.Value.Year.ToString();

        for (int i = 0; i < DataGridViewCompteMois.Rows.Count; i++)
        {
            conn = new SqlConnection(connstring);
            conn.Open();
            String NomCompteDataGrid = Convert.ToString(DataGridViewCompteMois.Rows[i].Cells[0].Value);
            String IdCompteValue = "select IdCom from Compte where NomCom='" + NomCompteDataGrid + "'";
            comm = new SqlCommand(IdCompteValue, conn);
            int IdComValue = Convert.ToInt32(comm.ExecuteScalar());
            conn.Close();
            using (SqlConnection con = new SqlConnection(connstring))
            {
                using (SqlCommand cmd = new SqlCommand("select SUM( Depense + Recette ) from Tresorerie where IdCom=@IdCom and MONTH(DateTre) = @MONTH AND YEAR(DateTre) = @YEAR GROUP BY IdCom", con))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.AddWithValue("@IdCom", IdComValue);
                    cmd.Parameters.AddWithValue("@MONTH", month);
                    cmd.Parameters.AddWithValue("@YEAR", year);
                    con.Open();
                    total = Convert.ToString(cmd.ExecuteScalar());
                    con.Close();
                }
            }
            DataGridViewCompteMois.Rows[i].Cells["Total"].Value = total;
            //MessageBox.Show(total.ToString());
        }
    }

更新解决方案：看，我选择了哪个单元格有问题，此代码有效

private void CompteMoisdateTimePicker_ValueChanged(object sender, EventArgs e)
    {
        string month = CompteMoisdateTimePicker.Value.Month.ToString();
        string year = CompteMoisdateTimePicker.Value.Year.ToString();
        for (int i = 0; i < DataGridViewCompteMois.Rows.Count; i++)
        {
            conn = new SqlConnection(connstring);
            conn.Open();
            String NomCompteDataGrid = Convert.ToString(DataGridViewCompteMois.Rows[i].Cells[1].Value);
            String IdCompteValue = "select IdCom from Compte where NomCom='" + NomCompteDataGrid + "'";
            comm = new SqlCommand(IdCompteValue, conn);
            int IdComValue = Convert.ToInt32(comm.ExecuteScalar());
            conn.Close();
            using (SqlConnection con = new SqlConnection(connstring))
            {
                using (SqlCommand cmd = new SqlCommand("select SUM( Depense + Recette ) from Tresorerie where IdCom=@IdCom and MONTH(DateTre) = @MONTH AND YEAR(DateTre) = @YEAR GROUP BY IdCom", con))
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.Parameters.AddWithValue("@IdCom", IdComValue);
                    cmd.Parameters.AddWithValue("@MONTH", month);
                    cmd.Parameters.AddWithValue("@YEAR", year);
                    con.Open();
                    //MessageBox.Show(Convert.ToString(cmd.ExecuteScalar()));
                    DataGridViewCompteMois.Rows[i].Cells["Total"].Value = Convert.ToString(cmd.ExecuteScalar());
                    con.Close();
                }
            }
        }
    }