INSERTING into SQL Server Table using Joins

Hi,

Here is what I am trying to do:

I want to create a Photo Gallery for Students.

Each student can have many Photo Collections.

Each Photo Collection contains a number of images.

So each Photo Collection will need to be linked to a Student, and each Image will need to be linked to a Photo Collection.

I am able to display Photo Collections successfully, based on each student.

I am also able to INSERT into Students, Images and Photo_Collection, but I aml unable to successfully INSERT into the Photo_Collection_Images table.

Can anyone help me how to insert into this table? Thanks a lot.

Here is my C#:

public void InsertStudentImages(string filename, string description, string collectionName, string studentName)
{
string cmdText = @"DECLARE @Student_Id INT;
DECLARE @Photo_Collection_Id INT;
DECLARE @Student_Image_Id INT;
INSERT INTO dbo.Students(Name)
VALUES (@StudentName)
--Get the last identity
SET @Student_Id = SCOPE_IDENTITY()
INSERT INTO dbo.Student_Images (Student_Id, Filename, Description)
VALUES (@Student_Id, @Filename, @Description);
SET @Student_Image_Id = SCOPE_IDENTITY()
INSERT INTO dbo.Photo_Collection (Student_Id, Name)
VALUES (@Student_Id, @CollectionName);
SET @Photo_Collection_Id = SCOPE_IDENTITY()
INSERT INTO dbo.Photo_Collection_Images (Photo_Collection_Id, Student_Image_Id)
VALUES (@Photo_Collection_Id, @Student_Image_Id);";
SqlCommand cmd = new SqlCommand(cmdText, con);
cmd.Parameters.AddWithValue("@Filename", filename);
cmd.Parameters.AddWithValue("@Description", description);
cmd.Parameters.AddWithValue("@CollectionName", collectionName);
cmd.Parameters.AddWithValue("@StudentName", studentName);
if (con.State == ConnectionState.Closed)
{
con.Open();
}
cmd.ExecuteNonQuery();
con.Close();
}

Here are my SQL Server tables:

CREATE TABLE [dbo].[Students] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Name] NVARCHAR (50) NULL,
CONSTRAINT [Students.Id.PrimaryKey] PRIMARY KEY CLUSTERED ([Id] ASC)
);
CREATE TABLE [dbo].[Student_Images] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Student_Id] INT NOT NULL,
[Filename] NVARCHAR (250) NULL,
[Description] NVARCHAR (250) NULL,
CONSTRAINT [Student_Images.Id.PrimaryKey] PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [Student_Images.to.Student] FOREIGN KEY ([Student_Id]) REFERENCES [dbo].[Students] ([Id])
);
CREATE TABLE [dbo].[Photo_Collection] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Student_Id] INT NOT NULL,
[Name] NVARCHAR (250) NULL,
CONSTRAINT [Photo_Collection.Id.PrimaryKey] PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [Photo_Collection.to.Student] FOREIGN KEY ([Student_Id]) REFERENCES [dbo].[Students] ([Id])
);
CREATE TABLE [dbo].[Photo_Collection_Images] (
[Photo_Collection_Id] INT NOT NULL,
[Student_Image_Id] INT NOT NULL,
CONSTRAINT [Photo_Collection_Images.to.Student_Images] FOREIGN KEY ([Student_Image_Id]) REFERENCES [dbo].[Student_Images] ([Id]),
CONSTRAINT [Photo_Collection_Images.to.Photo_Collection] FOREIGN KEY ([Photo_Collection_Id]) REFERENCES [dbo].[Photo_Collection] ([Id])
);

3 Replies

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

Nitin Sontakke9y ago
I think either the table design is flowed or I don't understand it.

You only need three tables

Student - all students Id is the primary key
StudentCollections - all collections of all students - Id is the primary key. Also has StudentId column FK to Id in Student table
CollectionImages - all images of all collections - Id is the primary key. Also has CollectionId column FK to Id in StudentCollections.

That's it. Why attach image to the student? There is no relationship.
Bryian Tan9y ago
The codes look OK. I tested it and didn't see any issue inserting into [Photo_Collection_Images] table. Are you getting any error message?
Bikesh Srivastava9y ago
Hi,
I think you are doing good ,but you need to change some little bit of code,
Have you tried with your approach.