sql command error in C#

Question

I have written this code to retrieve some information from two tables in database. But when I run it I get this error

Column 'Eaten_food.Cardserial' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

Code:

 private void button8_Click(object sender, EventArgs e)
 {
        using (SqlConnection con = new SqlConnection(WF_AbsPres_Food.Properties.Settings.Default.DbConnectionString))
        {
            con.Open();
            SqlDataAdapter a = new SqlDataAdapter("SELECT Eaten_food.Cardserial , Eaten_food.Date , Eaten_food.Turn , Avb_food_count , Reserve_count from Reserve inner join Eaten_food on Reserve.Cardserial = Eaten_food.Cardserial group by Eaten_food.Date", con);
            SqlCommandBuilder comdBuilder = new SqlCommandBuilder(a);
            DataTable t = new DataTable();
            //t.Locale = System.Globalization.CultureInfo.InvariantCulture;
            a.Fill(t);
            bindingSource3.DataSource = t;

            /// bind the grid view with binding source
           Reserve_dataGridView.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCellsExceptHeader);
            Reserve_dataGridView.ReadOnly = true;
            Reserve_dataGridView.DataSource = bindingSource3;

            Reserve_dataGridView.DataSource = t;
            con.Close();
        }
    }

How can I solve it?

What would you expect the GROUP BY part of the query to achieve? — Joachim Isaksson
– Joachim Isaksson, Commented Jul 21, 2013 at 20:38

Tim Schmelter · Accepted Answer · 2013-07-21 20:40:54Z

The problem is your sql query. If you use Group By you cannot select columns that are not grouped by or that are not aggregated(f.e. by using Min/Max/Avg/Count).

So you could make it working for example in this way, change your old query here:

SELECT eaten_food.cardserial, 
       eaten_food.date, 
       eaten_food.turn, 
       avb_food_count, 
       reserve_count 
FROM   reserve 
       INNER JOIN eaten_food 
               ON reserve.cardserial = eaten_food.cardserial 
GROUP  BY eaten_food.date

to:

SELECT MIN(eaten_food.cardserial)AS Cardserial, 
       eaten_food.date, 
       MIN(eaten_food.turn) AS Turn, 
       SUM(avb_food_count) AS SumFoodCount, 
       SUM(reserve_count) AS SumReserveCount 
FROM   reserve 
       INNER JOIN eaten_food 
               ON reserve.cardserial = eaten_food.cardserial 
GROUP  BY eaten_food.date

jasv · Accepted Answer · 2013-07-21 20:44:42Z

You have a problem with you SQL statement itself. I would copy it to SQL management studio and debug it.

The group function is to create summary rows and because of that your select columns would either need to be part of the group by clause or a summary type of statmet like sum(x) or count(x)

The following might work..

SELECT  Eaten_food.Cardserial ,
        Eaten_food.Date ,
        Count(Eaten_food.Turn) ,
        sum(Avb_food_count) ,
        sum(Reserve_count)
FROM    Reserve
        INNER JOIN Eaten_food ON Reserve.Cardserial = Eaten_food.Cardserial
GROUP BY Eaten_food.Date, Eaten_food.CardSerial

Collectives™ on Stack Overflow

sql command error in C#

2 Answers 2

Comments

Comments

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Related