CODEWITHSUNDEEP
c#.netwinformsdata-bindingdatatable
michael john
michael john

Reputation: 95

Show total Sum of values of a Column of a DataTable

I want to show sum of total Price of items. I am facing 2 issues:

You can check issue in the image for a clear explanation.

Here is my code:

tDisplay.Text = "Return/" + "Receipt No:" + Return_Form.setalueforText011;
label1.Text = Return_Form.setalueforText011;

OleDbConnection VCON = new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Restaurant.accdb");
DataSet dsa = new DataSet();
DataTable dt = new DataTable();
dsa.Tables.Add(dt);

OleDbDataAdapter da = new OleDbDataAdapter();
da = new OleDbDataAdapter("SELECT [Column1],[Column2],[Column3] from [Total] Where [Receipt No] =  " + label1.Text + "", VCON);
da.Fill(dt);
//dataGridView1.DataSource = dt;

for (int i = 0; i < dt.Rows.Count; i++)
{
    products.Add(new tblProduct() { productName = dt.Rows[i]["Column2"].ToString(),productPrice = Convert.ToDecimal(Math.Round(Convert.ToDecimal(dt.Rows[i]["Column1"].ToString())))});
    label3.Text = dt.Rows[i]["Column3"].ToString();
    textBox59.Text = "Rs: "+String.Format("{0:}", Total);
    tblProduct selected = (tblProduct)(listBox60.SelectedItem);
    Total += (decimal)selected.productPrice;
}
VCON.Close();

Upvotes: 1

Views: 2425

Answers (2)

Reza Aghaei
Reza Aghaei

Reputation: 125197

Sum

Instead of using for loop, You can simply use Compute method of data table and pass an expression to compute. For example:

var total = yourDataTable.Compute("SUM(Column1)", "");

Format

Also to format total to show 2 digits after decimal place, you can use either of these options:

Update Sum on changes of data

Also to be able to show the sum automatically in a TextBox event when new items added or some items removed or some values changed, handle ListChanged event of DataTable.DefaultView and set the result as Text of the text box.

Example

// Define data table
var dt = new DataTable();
dt.Columns.Add("Name");
dt.Columns.Add("Price", typeof(int));

// Fill data
dt.Rows.Add("Product 1", 100);
dt.Rows.Add("Product 2", 200);

// Set data source of data grid view
this.dataGridView1.DataSource = dt;

// Automatically update text box, by SUM of price
textBox1.Text = $"{dt.Compute("SUM(Price)", ""):F2}";
dt.DefaultView.ListChanged += (obj, args) =>
    textBox1.Text = $"{dt.Compute("SUM(Price)", ""):F2}";

Upvotes: 1

Steve
Steve

Reputation: 216293

In your loop you add to the total always the SelectedItem row. This is always the first item so you end up doubling the value of the first item.

for (int i = 0; i < dt.Rows.Count; i++)
{
    // Create and initialize a new tblProduct from the datatable row
    tblProduct current = new tblProduct();
    current.ProductName = dt.Rows[i]["Column2"].ToString();
    current.productPrice = Convert.ToDecimal(Math.Round(Convert.ToDecimal(dt.Rows[i]["Column1"].ToString())));

    // Add to your list of products
    products.Add(current);

    // This line is wrong because you overwrite the value at each loop
    label3.Text = dt.Rows[i]["Column3"].ToString();

    // Sum the price of the current tblProduct
    Total += (decimal)current.productPrice;
}
// Outside the loop update your total label
textBox59.Text = "Rs: "+String.Format("{0:0.00}", Total);

If you allow me to give an advice. Do not name your controls that way. They are unreadable and not easily recognizable. Looking at this code some day from now you will have a lot of problems to remember which control is textBox59 or listBox60.

Upvotes: 1

Related Questions