Joined tables not displaying as required

Hi,

Using the below tables, I am trying to retrieve the following:

All Group_Name's from the Groups table
All Name's from the Photo_Collection table which are linked to a Group (Group_Id)
All filename's from Group_Images which are linked to a Photo_Collection (Group_Image_Id).

I want to basically display Photo Albums for each Group using the above.

I have tried using the following code:

protected void bindStuff()
{
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_IdGroup_Id = Group_Id, Group_Name FROM Groups SELECT @Photo_Collection_Id = Id, Name FROM Photo_Collection WHERE Group_Id = @Group_Id SELECT @Group_Image_IdGroup_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();
}

But I get this error: "A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations."

Can someone please help, and tell me what I need to change in this code?

Here is my SQL Server table code:

CREATE TABLE [dbo].[Groups] (
[Group_Id] INT IDENTITY (1, 1) NOT NULL,
[Group_Name] NVARCHAR (50) NULL,
[Group_Desc] NVARCHAR (MAX) NULL,
CONSTRAINT [Groups.Group_Id.PrimaryKey] PRIMARY KEY CLUSTERED ([Group_Id] ASC)
);
CREATE TABLE [dbo].[Group_Images] (
[ID] INT IDENTITY (1, 1) NOT NULL,
[Group_Id] INT NOT NULL,
[filename] VARCHAR (250) NULL,
[imageDesc] NVARCHAR (250) NULL,
CONSTRAINT [Group_Images.ID.Primary Key] PRIMARY KEY CLUSTERED ([ID] ASC),
CONSTRAINT [Group_Images.to.Groups] FOREIGN KEY ([Group_Id]) REFERENCES [dbo].[Groups] ([Group_Id])
);
CREATE TABLE [dbo].[Photo_Collection] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Group_Id] INT NOT NULL,
[Name] NVARCHAR (250) NULL,
CONSTRAINT [Photo_Collection.Id.PrimaryKey] PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [Photo_Collection.to.Groups] FOREIGN KEY ([Group_Id]) REFERENCES [dbo].[Groups] ([Group_Id])
);
CREATE TABLE [dbo].[Photo_Collection_Images] (
[Photo_Collection_Id] INT NOT NULL,
[Group_Image_Id] INT NOT NULL,
CONSTRAINT [Photo_Collection_Images.to.Photo_Collection] FOREIGN KEY ([Photo_Collection_Id]) REFERENCES [dbo].[Photo_Collection] ([Id]),
CONSTRAINT [Photo_Collection_Images.to.Group_Images] FOREIGN KEY ([Group_Image_Id]) REFERENCES [dbo].[Group_Images] ([ID])
);

Before I tried the above code in my question, this is what was being displayed::

Above, the images are grouped within the correct groups, Red Cross and Gun Club.

However, the wrong Photo_Collection Names are being displayed above them.

The above was generated using this 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>
<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>
<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>
div>
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 pcpc_name = pc.Name, s_Id = si.Group_Id, sisi_filename = si.filename, sisi_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();
}

1 Reply

Know the answer? Post it — somebody with the same question will find it here.

Damien Sullivan9y ago
Before I tried the above code in my question, this is what was being displayed::

Above, the images are grouped within the correct groups, Red Cross and Gun Club.
However, the wrong Photo_Collection Names are being displayed above them.

The above was generated using this 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>

                                       <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>
                                                               <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>

                               div>

                           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 pcpc_name = pc.Name, s_Id = si.Group_Id, sisi_filename = si.filename, sisi_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();
  }