Retrieve Data using Joined SQL Server Tables

Question

Hi,

 

Here is my requirement:

 

• I want to display details (name, etc.) of images which are stored in photo albums.
• The file-path of each image is stored in an SQL Server table. 

• I have a Drop-down which displays Group Names.
• When I select a Group name (index is changed), this then populates a second drop-down which contains Photo Album names which are connected to the selected group when they are first added.
• Then, I want to display those image details in a table, to allow end-users to delete images if they wish.

 

Here are my SQL tables:

 

 

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

 Also, here is my current ASPX:

 

<h4>Group Photo Albums:</h4>

<asp:Label ID="lbl97" runat="server">Group:</asp:Label>

<table>

<tr>

<td align="right">

<asp:Label runat="server">Group:</asp:Label>

</td>

<td>

<asp:DropDownList ID="ddlGroupName" runat="server" AutoPostBack-="True"

DataTextField="Group_Name" DataValueField="Group_Id" AppendDataBoundItems="true"

OnSelectedIndexChanged="ddlGroupName_SelectedIndexChanged">

<asp:ListItem Value="0">Select Group</asp:ListItem>

</asp:DropDownList>

</td>

</tr>

<tr>

<td>

<asp:Label runat="server" Text="Photo Album:"></asp:Label>

</td>

<td>

<asp:DropDownList ID="ddlPhotoAlbum" runat="server" AppendDataBoundItems="true" DataTextField="Name"

DataValueField="Id" AutoPostBack="true"

OnSelectedIndexChanged="ddlPhotoAlbum_SelectedIndexChanged">

<asp:ListItem Value="0">Select Album</asp:ListItem>

</asp:DropDownList>

</td>

</tr>

</table>

 

Can any of you guys tell me how to display images which are associated with the Photo Album selected in ddlPhotoAlbum?

 

Thanks in advance for any help!