none
summing from a datagrid RRS feed

  • Question

  • Hy, 

    here's the problem. I got a table and what I want to do is to make a sum, but only for a single person in that table, not all of them. etc.

    Table

    ID             First Name             FamilyName                  Amount

    ------------------------------------------------------------------------------

    1 John Smith 500

    1 John Smith 250

    2 Alison  White 1000

    3 Andrew Kazinsky 300

     

    With my code:

     

    decimal zbroj = 0;
                foreach (DataGridViewRow row in this.dataGridView1.Rows)
                {
                    DataGridViewCell cell = row.Cells[5];
                    {
                        if (cell.Value != null)
                        {
                            zbroj += Convert.ToDecimal(cell.Value);                       
                        }
                    }
                }
                tbIznosBrutoDodatkaOdbitka.Text = zbroj.ToString();

    I get a sum of 2050, and what I need is to get 750 (only for John Smith).

     

    Thx for any help.

    Monday, October 3, 2011 9:21 AM

Answers

  • Hi davor,

    Try this:

    DataGridViewTextBoxColumn Id, FirstName, LastName, Amount;
            DataTable dt;
            object zbroj;
            private void Form1_Load(object sender, EventArgs e)
            {
                Id = new DataGridViewTextBoxColumn();
                Id.Name = "Id";
                Id.DataPropertyName = "Id";
                dataGridView1.Columns.Add(Id);
    
                FirstName = new DataGridViewTextBoxColumn();
                FirstName.Name = "FirstName";
                FirstName.DataPropertyName = "FirstName";
                dataGridView1.Columns.Add(FirstName);
    
                LastName = new DataGridViewTextBoxColumn();
                LastName.Name = "FamilyName";
                LastName.DataPropertyName = "FamilyName";
                dataGridView1.Columns.Add(LastName);
    
                Amount = new DataGridViewTextBoxColumn();
                Amount.Name = "Amount";
                Amount.DataPropertyName = "Amount";
                dataGridView1.Columns.Add(Amount);
             
                loadPersons();
                dataGridView1.CellEnter += new DataGridViewCellEventHandler(dataGridView1_CellEnter);
            }
    
            private void dataGridView1_CellEnter(object sender, DataGridViewCellEventArgs e)
            {
                zbroj = dt.Compute("Sum(Amount)", "Id = '" + dataGridView1.CurrentRow.Cells[Id.Name].Value.ToString() + "' ");
                textBox1.Text = zbroj.ToString();
            }
            private void loadPersons()
            {
                SqlConnection conConnect = new SqlConnection("Data Source= (local); Database='Persons'; Integrated Security=true");
                SqlDataAdapter dAdapter = new SqlDataAdapter("Select * From Persons;", conConnect);
                DataSet DS = new DataSet();
                dAdapter.Fill(DS, "dsPersons");
                dataGridView1.AutoGenerateColumns = false;
                dt = DS.Tables["dsPersons"];
                dataGridView1.DataSource = dt;
            }
    
    Hope it helps.

    Monday, October 3, 2011 12:35 PM
  • GridControl of devepxress have this functions!
    Any fool can know. The point is to understand.(Albert Einstein)
    Monday, October 3, 2011 10:35 PM

All replies

  • Try this way!

     

    DataTable yourDatatabale= //your table containing values
               
                DataTable distinctNames= yourDatatabale.DefaultView.ToTable(true,"First Name ");
    
                for (int i = 0; i < distinctNames.Rows.Count; i++)
                {
                    object sum = yourDatatabale.Compute("Sum(amount)", "name = '" + distinctNames.Rows[i]["name"] + "'");
     }
    Or for case of John Smith

    object sum = yourDatatabale.Compute("Sum(amount)", "name = 'John Smith'");

     


    • Edited by Zain_Ali Monday, October 3, 2011 10:08 AM
    Monday, October 3, 2011 10:06 AM
  • I'm afraid this doesn't work. Is this supposed to be solved over indexes?  
    Monday, October 3, 2011 10:52 AM
  • Do you try this.?What exception you are getting?
    For loop in above code will give you complete sum group by first name.You can store them in list or any where.
    Monday, October 3, 2011 10:56 AM
  • Try below code,

    var amounts = from person in Person.AsEnumerable()
                    where Convert.ToInt32(person["Id"]) == 1
                    select Convert.ToDecimal(person["Amount"]);
    
    decimal sumJohnSmith = amounts.Sum();
    


    I have assumed your table name is Person.


    Please mark this post as answer if it solved your problem. Happy Programming!
    Monday, October 3, 2011 11:06 AM
  • No, my table is filled from SQL View (3 tables combined). Probably I haven't make myself clear enough, I do not need sum only for John Smith, but for each person, even if that person is placed in multiple rows. 

    Something like this:

    Table

    ID FirstName FamilyName Amount

    -----------------------------------------------------------------------------------------

    1 a b 100

    1 a b 150

    1 a b 115

    2 c d 150

    2 c d 100

    3 e f 200

    3 e f 175

     

    So, what I need is, when I click on person with ID = 1 to see sum in the textBox, BUT only where ID = 1,  sum = 365, and if I click etc. on person with ID = 2 to see sum = 250.

     

    Thx!

    Monday, October 3, 2011 11:19 AM
  • That can help.

     

    object sum = yourDatatabale.Compute("Sum(amount)", "ID= '"+YourDataGridView.SelectedRows[0].Cells[" ID
    "].Value.ToString();+"'");
    Monday, October 3, 2011 11:29 AM
  • "Index was out of range. Must be non-negative and less than the size of the collection.
    Parameter name: index"
    Monday, October 3, 2011 11:34 AM
  • Try this code

     

    decimal zbroj = 0;
    string sFirstName="John";
    string sFamilyName="Smith";
    
                foreach (DataGridViewRow row in this.dataGridView1.Rows)
                {
                    DataGridViewCell cell = row.Cells[5];
                    {
    if(sFirstName=row.Cells[3].Value && sFamilyName=row.Cells[4].Value)
    {
                        if (cell.Value != null)
                        {
                            zbroj += Convert.ToDecimal(cell.Value);                       
                        }
    }
                    }
                }
                tbIznosBrutoDodatkaOdbitka.Text = zbroj.ToString();
    
    
    



    Happy Coding, RDRaja
    Monday, October 3, 2011 12:29 PM
  • Hi davor,

    Try this:

    DataGridViewTextBoxColumn Id, FirstName, LastName, Amount;
            DataTable dt;
            object zbroj;
            private void Form1_Load(object sender, EventArgs e)
            {
                Id = new DataGridViewTextBoxColumn();
                Id.Name = "Id";
                Id.DataPropertyName = "Id";
                dataGridView1.Columns.Add(Id);
    
                FirstName = new DataGridViewTextBoxColumn();
                FirstName.Name = "FirstName";
                FirstName.DataPropertyName = "FirstName";
                dataGridView1.Columns.Add(FirstName);
    
                LastName = new DataGridViewTextBoxColumn();
                LastName.Name = "FamilyName";
                LastName.DataPropertyName = "FamilyName";
                dataGridView1.Columns.Add(LastName);
    
                Amount = new DataGridViewTextBoxColumn();
                Amount.Name = "Amount";
                Amount.DataPropertyName = "Amount";
                dataGridView1.Columns.Add(Amount);
             
                loadPersons();
                dataGridView1.CellEnter += new DataGridViewCellEventHandler(dataGridView1_CellEnter);
            }
    
            private void dataGridView1_CellEnter(object sender, DataGridViewCellEventArgs e)
            {
                zbroj = dt.Compute("Sum(Amount)", "Id = '" + dataGridView1.CurrentRow.Cells[Id.Name].Value.ToString() + "' ");
                textBox1.Text = zbroj.ToString();
            }
            private void loadPersons()
            {
                SqlConnection conConnect = new SqlConnection("Data Source= (local); Database='Persons'; Integrated Security=true");
                SqlDataAdapter dAdapter = new SqlDataAdapter("Select * From Persons;", conConnect);
                DataSet DS = new DataSet();
                dAdapter.Fill(DS, "dsPersons");
                dataGridView1.AutoGenerateColumns = false;
                dt = DS.Tables["dsPersons"];
                dataGridView1.DataSource = dt;
            }
    
    Hope it helps.

    Monday, October 3, 2011 12:35 PM
  • GridControl of devepxress have this functions!
    Any fool can know. The point is to understand.(Albert Einstein)
    Monday, October 3, 2011 10:35 PM