Damien Sullivan

Damien Sullivan

  • 1.9k
  • 26
  • 3.7k

Retrieve Data using Joined SQL Server Tables

Feb 10 2017 10:27 AM
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! 
 

Brought to you by:

Answers (1)