CODEWITHSUNDEEP
c#databasedatagridviewgroup-bysum
michael john
michael john

Reputation: 95

Sum and show in datagridview database value

I want to sum and show value in datagridview using Group By

string connStr = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\Restaurant.accdb;Persist Security Info=False;";
string query = "SELECT Column2,SUM(Column1),Receipt No,Date From Total Group By[Column2]";
//SELECT Column2 FROM Total GROUP BY [Column2]
using (OleDbConnection conn = new OleDbConnection(connStr))
{
    using (OleDbDataAdapter adapter = new OleDbDataAdapter(query, conn))
    {
        DataSet ds = new DataSet();
        if (ds != null)
        {
            adapter.Fill(ds);

            dataGridView1.DataSource = ds.Tables[0];

when this form load it's close automatically nothing shows anything in datagrdview

Upvotes: 0

Views: 118

Answers (2)

apomene
apomene

Reputation: 14389

1st fix your query:

string query = "SELECT Column2,SUM(Column1),[Receipt No],Date From Total Group By [Column2],[Receipt No],Date";

and 2nd open connection:

using (OleDbConnection conn = new OleDbConnection(connStr))
            {
                using (OleDbDataAdapter adapter = new OleDbDataAdapter(query, conn))
                { 
                  conn.Open();
                   ...

EDIT: your 2nd query should be:

"SELECT Column3 As [Table No],SUM(Column1) As Price,[Receipt No],Date From Total Where [Receipt No] = '" + textBox1.Text + "' Group By [Column3],[Receipt No],[Date]";

Upvotes: 1

Abhishek
Abhishek

Reputation: 170

Please check conn.Open(); seems missing.

 using (OleDbDataAdapter adapter = new OleDbDataAdapter(query, conn))
 { 
     conn.Open();
     DataSet ds = new DataSet();
     if (ds != null)
     {
          adapter.Fill(ds);

          dataGridView1.DataSource = ds.Tables[0];
     ......

Hope this helps!

Upvotes: 0

Related Questions