Why do I get an IndexOutOfRangeException exception when trying to read the count column from my query?

Viewed 26

I am getting the an System.IndexOutOfRangeException exception on "count". Any idea what I am doing wrong?

[HttpGet]
public IHttpActionResult shopnameandnoofcomp()
{
    SqlDataReader dataReader = null;
    SqlConnection myConnection = new SqlConnection();
    myConnection.ConnectionString = dbpath;
    myConnection.Open();
    SqlCommand sqlCmd = new SqlCommand();
    sqlCmd.CommandType = CommandType.Text;
    sqlCmd.CommandText = "selectshopname,count(shopname) from Complain group by shopname";
    sqlCmd.Connection = myConnection;
    
    List<Complain> cmp = new List<Complain>();
    dataReader = sqlCmd.ExecuteReader();
    while (dataReader.Read())
    {
        cmp.Add(new Complain
        {
            shopname= dataReader["shopname"].ToString(),
            Count = dataReader["count"].ToString(),
        });
    }
    myConnection.Close();
    return Ok(cmp);
}
1 Answers

Selecting aggregate functions do not produce a column name, so there is no column in your Data Reader called "count". Explicitly name the resulting column that.

select shopname, count(shopname) as count
from Complain
group by shopname
Related