C# SQL SUM value to a label

Question

I currently have a DataGridView which displays all the item. I would like to sum all the prices in the price column and then reflect the total in a label, 'TotalValueLabel'. What's wrong with my statement?

        string query = "SELECT SUM (Price) FROM Bill";
        OleDbDataAdapter dAdapter = new OleDbDataAdapter(query, DBconn);
        DataTable source = new DataTable();
        dAdapter.Fill(source);
        TotalValueLabel.Text = source.ToString();

Solution 1

Your source is a DataTable so "source.ToString()" will not give you your result,

Try "source.Rows[0][0].ToString();".

DataTable object contains a list of DataRow objects which hold values for each row of your query result.

In your case however you might not need this. If you are looking for a single value you should use IDbCommand and call ExecuteScalar(). This will return the first value of the first row of your results.

Also try calling Dispose() on objects that implement IDisposable (like dbadapter, command, connection).

string query = "SELECT SUM (Price) FROM Bill";
using (System.Data.IDbCommand command = new System.Data.OleDb.OleDbCommand(query, DBconn))
{
   object result = command.ExecuteScalar();
   TotalValueLabel.Text = Convert.ToString(result);
}

Solution 2

DataTable is overkill for single value retrieval, besides your not even accessing the value correctly, better way is to use execute scalar:

var query = "SELECT SUM (Price) FROM Bill";
using (var cmd = new OleDbCommand(query, DBcon))
{
    TotalValueLabel.Text = cmd.ExecuteScalar().ToString();
}

Comments

[pacheco]

Thanks, it works as Paul also suggested earlier. I have a question though, how can i display the result in currency format?

[pacheco]

Hi, thanks for the input. May i know the difference between using a datatable and executescalar?

[Goran]

Then first convert the result to int and use msdn.microsoft.com/en-us/library/dwhawy9k.aspx .

[Kris Ivanov]

nigelwhitworth.com/VB/…

[pacheco]

TotalValueLabel.Text = source.Rows[0][0].ToString("C"); doesn't seem to be working

[Goran]

source.Rows[0][0] returns an object. ToString with formatting parameter works on numeric data such as int, decimal, etc. So convert source.Rows[0][0] object to int, then call ToString("C").