Hi,
Here is my requirement:
I need to create a Photo Gallery page for Students.
Each student can have many Photo Collections, and each image is associated with a Photo Collection.
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])
);
And here are my INSERT statements:
public void addCollection()
{
string cmdText = "INSERT INTO Photo_Collection (Student_Id, Name) VALUES (@I, @N)";
SqlCommand cmd = new SqlCommand(cmdText, con);
cmd.Parameters.AddWithValue("@I", ddlStudentToAddCollection.SelectedValue);
cmd.Parameters.AddWithValue("@N", txtPhotoCollectionName.Text.ToString());
if (con.State == ConnectionState.Closed)
{
con.Open();
}
cmd.ExecuteNonQuery();
con.Close();
}
public void addImage()
{
string cmdText = "INSERT INTO Student_Images (Student_Id, Filename, Description) VALUES (@S, @F, @D)";
SqlCommand cmd = new SqlCommand(cmdText, con);
cmd.Parameters.AddWithValue("@S", ddlStudentToAddImage.SelectedValue);
cmd.Parameters.AddWithValue("@F", fuImage.FileName.ToString());
cmd.Parameters.AddWithValue("@D", txtImageDescription.Text.ToString());
if (con.State == ConnectionState.Closed)
{
con.Open();
}
cmd.ExecuteNonQuery();
con.Close();
}
The above INSERT statements are inserting as expected, but the below statement is not inserting anything to the database:
private void updateCollectionImage()
{
try
{
//string cmdText = "insert into Photo_Collection_Images (Photo_Collection_Id , Student_Image_Id) select pc.Id, si.Id from dbo.photo_collection as pc inner join dbo.student_images as si on pc.student_id = si.student_id inner join dbo.students as s on s.id=si.student_id where s.Name = @S and pc.Name = @PC and si.FileName = @F";
string cmdText = "insert into Photo_Collection_Images (Photo_Collection_Id , Student_Image_Id) select pc.Id, si.Id from dbo.photo_collection as pc inner join dbo.student_images as si on pc.student_id = si.student_id inner join dbo.students as s on s.id=si.student_id where s.Name = @S and pc.Name = @PC and si.FileName = @F";
SqlCommand cmd = new SqlCommand(cmdText, con);
cmd.Parameters.AddWithValue("@S", ddlStudentToAddImage.SelectedValue);
cmd.Parameters.AddWithValue("@PC", txtPhotoCollectionName.Text.ToString());
cmd.Parameters.AddWithValue("@F", fuImage.FileName.ToString());
if (con.State == ConnectionState.Closed)
{
con.Open();
}
cmd.ExecuteNonQuery();
con.Close();
}
catch (Exception ex)
{
lblAddError.Text = ex.Message.ToString();
}
}
The purpose of this final table is to link the images with their respective Photo Collection.
As I said, the other image and photo collection INSERT's are working, but it's this final statement which I can't get going.
Can anyone please give me any help?