Showing Election Results in Repeater inside DataList.

Ballot Table

When voters vote for a particular candidate by clicking on a radiobutton related to the candidate and submit their votes, the votes are inserted into the table as shown above.

The voters ID and names are also inserted based on the voter.

Example John is a voter and he voted for Mike King as President, and also voted for Kate Johnson as Secretary.

It should count the number of times a candidate has appeared and display the candidate's total number of votes he or she has gotten and display.

From the Voting Table, Mike King has 3 votes for President, while Lawrence Victor has 1 vote. The other candidates – Peter Manny has no vote in the Voting Table.

For Secretary: Kate Johnson has 3 votes, while Helen Tim has 2 votes. Wendy Cahill has no vote in the Voting Table.

This means that Mike King has won for President and Kate Johnson has won as Secretary.

I want to show the result sequence as:

PRESIDENT:

Peter Manny ---------- 0 (0%)

Lawrence Victor ------- 1 (25%)

Mike King -------------- 3  (75%)

SECRETARY:

Helen Tim ---------- 2 (40%)

Kate Johnson ------ 3 (60%)

Wendy Cahill ------ 0 (0%)

I tried to use DataList and nested Repeater, since the record is displayed from two Tables

 HTML

<asp:ScriptManager ID="script" runat="server"></asp:ScriptManager>
<div class="wrapper">
    <div>
        <div id="content">
            <div class="line"></div>
            <asp:Label ID="Labelurl" runat="server" Text="http://localhost:60223/vote.aspx?id=3rcX7i6v"></asp:Label>
            <asp:Label ID="startlbl" runat="server" Text=""></asp:Label>
            <asp:Label ID="endlbl" runat="server" Text=""></asp:Label>
            <asp:Label ID="Adminlbl" runat="server" Text="Richard" />
            <asp:Label ID="electlbl" runat="server" Text="Faculty of Law Elections" />
 
 
            <div class="jumbotron-fluid">
                <asp:DataList runat="server" ID="dlquestion" CssClass="row" CellPadding="4" OnItemDataBound="dlquestion_ItemDataBound" Width="100%">
                    <ItemTemplate>
                        <div class="card p-3 mb5 bg-white rounded">
                            <asp:Label ID="lblQuestion" Text='<%# Eval("BallotQst") %>' runat="server" Font-Bold="true" Font-Names="Varela Round" Font-Size="16pt" />
                            <asp:Repeater ID="rptOptions" runat="server">
                                <HeaderTemplate></HeaderTemplate>
                                <ItemTemplate>
                                    <div class="" runat="server" id="innerTable">
                                        <div class="" id="middleTable">
                                            <asp:HiddenField ID="hfBallotQuestion" runat="server" Value='<%#Eval("BallotQuestions")%>' />
                                            <asp:Image ID="postimg" runat="server" Height="20%" Width="20%" />
                                            <asp:Label ID="candlbl" runat="server" Text='<%# Eval("votes") %>' />
                                            &nbsp;<asp:Label ID="voteslbl" runat="server" Text="" />
                                        </div>
                                        <hr />
                                    </div>
                                </ItemTemplate>
                            </asp:Repeater>
                        </div>
                    </ItemTemplate>
                </asp:DataList>
            </div>
        </div>
    </div>
</div>

protected void Page_Load(object sender, EventArgs e)
{
    if (!IsPostBack)
    {
        string query = "SELECT DISTINCT BallotQst FROM Ballots WHERE Name = '" + Adminlbl.Text + "' AND ElectionName = '" + electlbl.Text + "'";
        DataTable dt = GetData(query);
        dlquestion.RepeatColumns = dt.Rows.Count;
        dlquestion.DataSource = dt;
        dlquestion.DataBind();
        dlquestion.RepeatColumns = 1;
        dlquestion.RepeatDirection = RepeatDirection.Vertical;
    }
}
 
private DataTable GetData(string query)
{
    SqlCommand cmd = new SqlCommand(query);
    using (SqlConnection con = new SqlConnection("Data Source = (LocalDB)\\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\\BallotDB.mdf;Integrated Security = True;"))
    {
        using (SqlDataAdapter sda = new SqlDataAdapter())
        {
            cmd.Connection = con;
            sda.SelectCommand = cmd;
            using (DataTable dt = new DataTable())
            {
                sda.Fill(dt);
                return dt;
            }
        }
    }
}
 
protected void dlquestion_ItemDataBound(object sender, DataListItemEventArgs e)
{
    if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
    {
        string BallotQuestion = (e.Item.FindControl("lblQuestion") as Label).Text;
        Repeater rptOptions = e.Item.FindControl("rptOptions") as Repeater;
        string query = "SELECT '" + BallotQuestion + "' AS ballotQuestions,votes FROM votes WHERE BallotQuestions = '" + BallotQuestion + "' AND AdminName = '" + Adminlbl.Text + "' AND ElectionName = '" + electlbl.Text + "'";
        rptOptions.DataSource = GetData(query);
        rptOptions.DataBind();
    }
}