Joined tables not displaying as required

Question

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])
);