Trouble Displaying Joined SQL Server Tables using Repeater

[Sweg]

Hi, here is my requirement:

• I need to create a web forms page which displays different local Groups (Soccer, Golf, etc.)
• Each group needs to display a number of Photo Albums
• Each Photo Album can contain a number of Images

At the moment, I am able to create Photo Albums, and add images to each album.

However, I am having trouble trying to display the albums correctly.

Here are my SQL Server tables:



Here is my current code:

<asp:Repeater ID="repGroupPhotoGallery" runat="server" OnItemDataBound="repGroupPhotoGallery_ItemDataBound">
                            <ItemTemplate>
                                <div class="col-md-3">
                                    <div class="panel panel-default">
                                        <div class="panel-heading">
                                            <h3 class="panel-title">
                                                <%# Eval("Group_Name") %>
                                            </h3>
                                        </div>
                                        <!--panel-heading-->
                                        <div class="panel-body">
                                            <div class="col-md-4 text-center">
                                                <div class="thumbnail">
                                                    <asp:DataList ID="dlImages" runat="server" RepeatDirection="Horizontal" RepeatColumns="3" CellPadding="5">
                                                        <ItemTemplate>
                                                            <div class="caption">
                                                                <h3><%# Eval("pc_name") %><br /></h3>                                                   <!--imageDesc   -->
                                                                  <a id="imageLink" href='<%# Eval("si_filename","/Group_Images/{0}") %>' title='<%#Eval("si_description") %>' rel="lightbox[Brussels]">
                                                                    <asp:Image ID="Image1" ImageUrl='<%# Bind("si_filename", "~/Group_Images/{0}") %>' runat="server" Width="112" Height="84" />
                                                                </a>
                                                            </div>
                                                        </ItemTemplate>
                                                    </asp:DataList>
                                                </div>
                                            </div>
                                        </div>
                                    </div>
                                    <!--panel-default-->
                                </div>
                                <!--col-md-3-->
                            </ItemTemplate>
                        </asp:Repeater>

    private void BindRepeater()    {
        string constr = ConfigurationManager.ConnectionStrings["FYPConnectionString1"].ConnectionString;
        using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand())
            {
                //ORDER BY DATE
                cmd.CommandText = "select * from Groups WHERE Group_Type ='Group'";
                cmd.Connection = con;
                con.Open();
                repGroupPhotoGallery.DataSource = cmd.ExecuteReader();
                repGroupPhotoGallery.DataBind();
                con.Close();
            }
        }
    }
    protected void repGroupPhotoGallery_ItemDataBound(object sender, RepeaterItemEventArgs e)
    {
        DataList datalist = e.Item.FindControl("dlImages") as DataList;


        //find the correct group id of the item
        string student_Id = DataBinder.Eval(e.Item.DataItem, "Group_Id").ToString();
        SqlDataAdapter sda = new SqlDataAdapter("select pc_name = pc.Name, s_Id = si.Group_Id, si_filename = si.filename, si_description = si.imageDesc from Groups as s inner join dbo.group_images as si on s.Group_Id = si.group_id inner join dbo.photo_collection_images pci on pci.group_image_id = si.Group_Id inner join dbo.photo_collection as pc on pc.id = pci.photo_collection_id where s.Group_Id =" + student_Id, con);
        DataTable dt = new DataTable();
        sda.Fill(dt);


        //bind data to the nested datalist with the Group_Id in the where clause of the query
        datalist.DataSource = dt;
        datalist.DataBind();
    }

 

[Sweg]

The above code generates the following display:



The images above are correctly linked to those Groups.

But, the headings/names which are displayed above each image are not correct as they are not linked to those images in the SQL tables.

Can any of you guys please tell me how I can fix this problem?

Thanks so much in advance!

 

[Sweg]

I've also tried the following to bind a different repeater with the details but it is not working.
Can someone please tell me what is wrong with the following code snippet?

SqlConnection connR;
string connectionStringR = ConfigurationManager.ConnectionStrings[
"FYPConnectionString1"].ConnectionString;
connR = new SqlConnection(connectionStringR);
SqlDataAdapter sda = new SqlDataAdapter(@"DECLARE @Group_Id INT; DECLARE @Photo_Collection_Id INT; DECLARE @Group_Image_Id INT; SELECT @Group_Id = Group_Id, Group_Name FROM Groups SELECT @Photo_Collection_Id = Id, Name FROM Photo_Collection WHERE Group_Id = @Group_Id SELECT @Group_Image_Id = Group_Image_Id FROM Photo_Collection_Images WHERE Photo_Collection_Id = @Photo_Collection_Id SELECT Id, filename, imageDesc FROM Group_Images WHERE ID = @Group_Image_Id;", connR);
DataTable dt = new DataTable();
sda.Fill(dt);
repStuff.DataSource = dt;
repStuff.DataBind();