Reputation: 147
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?
Upvotes: 2
Views: 261
Reputation: 106
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
Upvotes: 1
Reputation: 460288
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
Upvotes: 7