gridview InnerJoin query without using datasource controls

Question

I am trying to display the following table from MySQL workbench to display on asp.net

To display the table on asp.net I am using grid view, here is the following code:

<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" OnRowCommand="GridView1_RowCommand" DataKeyNames ="child_id" OnSelectedIndexChanged="GridView1_SelectedIndexChanged" AllowPaging="True" OnPageIndexChanging="GridView1_PageIndexChanging">
        <Columns>
            <asp:TemplateField>
                <ItemTemplate>
                    <asp:LinkButton ID="LinkButton1" runat="server" CommandName="SelectFullDetails">Select</asp:LinkButton>
                </ItemTemplate>
            </asp:TemplateField>
            <asp:BoundField DataField="child_id" HeaderText="Id" />
            <asp:BoundField DataField="child_firstname" HeaderText="FirstName" />
            <asp:BoundField DataField="parent_surname" HeaderText="Surname" />
            <asp:BoundField DataField="club_name" HeaderText="Club" />
        </Columns>
    </asp:GridView>



        protected void Page_Load(object sender, EventArgs e)
    {
        if (!IsPostBack)
        {
            GridViewDataBind();
        }
    }

    private void GridViewDataBind()
    {
        GridView1.DataSource = DetailsAccessLayer.GetAllChildBasicDetails();
        GridView1.DataBind();
    }
 public class ChildBasic
{

    public int child_id { get; set; }
    public string child_firstname { get; set; }
    public string parent_surname { get; set; }
    public string club_name { get; set; }

}
 public class DetailsAccessLayer
{
    public static List<ChildBasic> GetAllChildBasicDetails()
    {
        List<ChildBasic> listChild = new List<ChildBasic>();
        string CS = ConfigurationManager.ConnectionStrings["dbyouthworkConnectionString"].ConnectionString;
        using (MySqlConnection con = new MySqlConnection(CS))
        {
            var InnerJoinClub = "SELECT child_details.child_id, child_details.child_firstname,child_details.parent_surname, enrolment_details.club_name FROM child_details INNER JOIN enrolment_details ON child_details.child_id = enrolment_details.child_id";
            MySqlCommand cmd = new MySqlCommand(innerJoinClub, con);
            con.Open();
            MySqlDataReader rdr = cmd.ExecuteReader();
            while (rdr.Read())
            {
                ChildBasic childbasic = new ChildBasic();
                childbasic.child_id = Convert.ToInt32(rdr["child_id"]);
                childbasic.child_firstname = rdr["child_firstname"].ToString();
                childbasic.parent_surname = rdr["parent_surname"].ToString();
                childbasic.club_name = rdr["club_name"].ToString();

                listChild.Add(childbasic);
            }
        }
        return listChild;
    }

Yet every time I run the program, I get the error: An exception of type 'System.Web.HttpException' occurred in System.Web.dll but was not handled in user code

Additional information: A field or property with the name 'club_name' was not found on the selected data source.

Can anyone show me the mistake in the code and how to correct it ?

Many Thanks

edit : This is where the error occurs

private void GridViewDataBind()
    {
        GridView1.DataSource = DetailsAccessLayer.GetAllChildBasicDetails();
        GridView1.DataBind();        }

at GridView1.DataBind()

Answer 1

In your SQL query, you need to provide an alias for enrolment_details.club_name, like enrolment_details.club_name AS club_name

Answer 2

Try like that:

public DataTable ConvertToDatatable(List<ChildBasic> list)
{
    DataTable dt = new DataTable();

    dt.Columns.Add("child_id");
    dt.Columns.Add("child_firstname");
    dt.Columns.Add("parent_surname");
    dt.Columns.Add("club_name");
    foreach (var item in list)
    {
        var row = dt.NewRow();

        row["child_id"] = item.child_id;
        row["child_firstname"] = Convert.ToString(item.child_firstname);
        row["parent_surname"] = Convert.ToString(item.parent_surname);
        row["club_name"] = Convert.ToString(item.parent_surname);

        dt.Rows.Add(row);
    }

    return dt;
}

private void GridViewDataBind()
    {
        GridView1.DataSource = ConvertToDatatable(DetailsAccessLayer.GetAllChildBasicDetails());
        GridView1.DataBind();        
    }