//total points of each child id that is searched. Join tables child and points. Display the fileNumber, childName, childSurname, and sum(Points)
protected void SearchForChildTotalPoints_Click(Object sender, EventArgs e)
{
try
{
ClearTotalPointsGridView();
sqlConnection = dbConn.MakeConnection(sqlConnection);
sqlConnection.Open();
string sql = "select c.fileNumber, c.childName,c.childSurname, sum(allocatedPointValue) as 'Total Points' from tbl_Child c, tbl_Points p where p.fileNumber = c.fileNumber and p.fileNumber ='" + TotalPointsTxtBox.Text + "'group by c.fileNumber, c.childName, c.childSurname";
SqlDataAdapter sqlCmd = new SqlDataAdapter(sql, sqlConnection);
sqlCmd.Fill(dtblTotalPoints);
if (dtblTotalPoints.Rows.Count > 0)
{
TotalPointsGridView.DataSource = dtblTotalPoints;
TotalPointsGridView.DataBind();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
finally
{ //close connection
sqlConnection.Close();
}
}
//clear total points gridView
protected void ClearTotalPointsGridView()
{
TotalPointsGridView.DataSource = null;
TotalPointsGridView.DataBind();
}
