CODEWITHSUNDEEP
c#sql-server
saadat rahimi
saadat rahimi

Reputation: 147

sql command error in C#

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

Answers (2)

jasv
jasv

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

Tim Schmelter
Tim Schmelter

Reputation: 460298

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

Related Questions