Calculate grand total from Dataset

Question

User340377605 posted

Hello,

I want to get (total)  column from DB into Dataset,, and the calculate the grand total , and show result into Textbox !!

This what i am doing to get total into Dataset: what should i do next to calculate the Grand total ?? and show it in TextBox ??

DataSet ds = new DataSet();
using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["connect"].ConnectionString))
        {
            con.Open();
            SqlDataAdapter cmd = new SqlDataAdapter("Select total FROM books WHERE quid = '" + (Label1.Text) + "'", con);
            cmd.Fill(ds, "table");
        }

Answer 1

User1224194097 posted

Retrieve Total from the DataTable like this

TextBox1.Text=ds.Tables[0].Rows[0]["total"].ToString();

Modify the command to return 0 for NULL values.

 SqlDataAdapter cmd = new SqlDataAdapter("Select ISNULL(total,0) total FROM books WHERE quid = '" + (Label1.Text) + "'", con); 

Answer 2

User340377605 posted

I am not just trying to retrieve total ,, I am trying to get the Grand Total which is all total added together

i.e. total1 + total2+ total3  + ....etc = Grand Total

total1 , total2, total3 ... etc are the Dataset I am getting from DB ,, How can i add them ??

Answer 3

User1224194097 posted

If you want to get grand total, you can get it in the query itself. No need to loop through DataTable to add totals.

SqlDataAdapter cmd = new SqlDataAdapter("Select ISNULL(SUM(total),0) total FROM books WHERE quid = '"+(Label1.Text)+"'", con);

Answer 4

User-691245060 posted

Alternatively if you want to do it on the DataTable itself...then go for LINQ - 

http://social.msdn.microsoft.com/Forums/en-US/csharplanguage/thread/b5184aca-6264-4079-8434-7a0cf43baf2c

Thanks.

Answer 5

User340377605 posted

If you want to get grand total, you can get it in the query itself. No need to loop through DataTable to add totals.

1. SqlDataAdapter cmd = new SqlDataAdapter("Select ISNULL(SUM(total),0) total FROM books WHERE quid = '"+(Label1.Text)+"'", con);  

SqlDataAdapter cmd = new SqlDataAdapter("Select ISNULL(SUM(total),0) total FROM books WHERE quid = '"+(Label1.Text)+"'", con);

Thank you ,, can i show the grand total in a textBox like this:

cmd.Fill(ds, "table");
          TextBox1.Text = ds.ToString();

Answer 6

User-1485051499 posted

To show the grand total use

TextBox1.Text = ds.Tables[0].Rows[0]["total"].ToString();

Answer 7

User340377605 posted

Thank you so much ,, it is working fine :D