INSERTING into SQL Server Table using Joins

Question

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