nitro Posts - Answer Overflow

nitro

•Created by nitro on 7/12/2024 in #help

Foreign Key Exception on Id when attempting to insert

I am getting the following exception when trying to insert records into my video_genres table:

System.Exception: Microsoft.Data.SqlClient.SqlException The INSERT statement conflicted with the FOREIGN KEY constraint "FK_video_genre_videos". The conflict occurred in database "BlockBusters", table "dbo.videos", column 'id'.
      The statement has been terminated.

System.Exception: Microsoft.Data.SqlClient.SqlException The INSERT statement conflicted with the FOREIGN KEY constraint "FK_video_genre_videos". The conflict occurred in database "BlockBusters", table "dbo.videos", column 'id'.
      The statement has been terminated.

// Attempt to create a link in the join table at [dbo].[video_genres].
foreach (var genre in genres)
{
    if (videoData.Genres != null)
    {
        foreach (var g in videoData.Genres)
        {
            if (g.Genre == genre.Name)
            {
                // Insert the list we created for the videoGenres to the [dbo].[video_genres].
                using (SqlCommand command = new SqlCommand(insertQueryVideoGenres, connection, transaction))
                {
                    command.Parameters.AddWithValue("@VideoId", video.Id);
                    command.Parameters.AddWithValue("@GenreId", genre.Id);
                    command.ExecuteScalar();
                };
            }
        }
    }
}

// Attempt to create a link in the join table at [dbo].[video_genres].
foreach (var genre in genres)
{
    if (videoData.Genres != null)
    {
        foreach (var g in videoData.Genres)
        {
            if (g.Genre == genre.Name)
            {
                // Insert the list we created for the videoGenres to the [dbo].[video_genres].
                using (SqlCommand command = new SqlCommand(insertQueryVideoGenres, connection, transaction))
                {
                    command.Parameters.AddWithValue("@VideoId", video.Id);
                    command.Parameters.AddWithValue("@GenreId", genre.Id);
                    command.ExecuteScalar();
                };
            }
        }
    }
}

And here is my .sql table:

CREATE TABLE [dbo].[video_genres] (
    [id]       INT IDENTITY (1, 1) NOT NULL,
    [video_id] INT NOT NULL,
    [genre_id] INT NOT NULL,
    CONSTRAINT [PK_video_genre] PRIMARY KEY CLUSTERED ([id] ASC),
    CONSTRAINT [FK_video_genre_genres] FOREIGN KEY ([genre_id]) REFERENCES [dbo].[genres] ([id]),
    CONSTRAINT [FK_video_genre_videos] FOREIGN KEY ([video_id]) REFERENCES [dbo].[videos] ([id])
);

CREATE TABLE [dbo].[video_genres] (
    [id]       INT IDENTITY (1, 1) NOT NULL,
    [video_id] INT NOT NULL,
    [genre_id] INT NOT NULL,
    CONSTRAINT [PK_video_genre] PRIMARY KEY CLUSTERED ([id] ASC),
    CONSTRAINT [FK_video_genre_genres] FOREIGN KEY ([genre_id]) REFERENCES [dbo].[genres] ([id]),
    CONSTRAINT [FK_video_genre_videos] FOREIGN KEY ([video_id]) REFERENCES [dbo].[videos] ([id])
);

9 replies

CC#

•Created by nitro on 7/11/2024 in #help

Trying to map values to a single DTO

I have three separate tables, one for the videos, one for all possible genres and one that holds the videoID and genreID so I can tell which video has which genre(s). Currently it makes sense that I'm getting all possible genres populated instead of only the ones that are actually for the videoID. How can I solve this properly? GenreRepository.cs:

public IEnumerable<Genre> getAllGenres()
{
    List<Genre> genres = new List<Genre>();

    using(SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();

        using(SqlTransaction transaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
        {
            using (SqlCommand command = new SqlCommand("SELECT [id],[genre] FROM [dbo].[genres]", connection, transaction))
            {
                using (SqlDataReader reader = command.ExecuteReader())
                {

                    while (reader.Read())
                    {
                        genres.Add(new Genre()
                        {
                            Id = (int)reader["id"],
                            Name = (string)reader["genre"],
                        });
                    }
                }
            }

            transaction.Commit();

        }
    }

    return genres;
}

public IEnumerable<Genre> getAllGenres()
{
    List<Genre> genres = new List<Genre>();

    using(SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();

        using(SqlTransaction transaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
        {
            using (SqlCommand command = new SqlCommand("SELECT [id],[genre] FROM [dbo].[genres]", connection, transaction))
            {
                using (SqlDataReader reader = command.ExecuteReader())
                {

                    while (reader.Read())
                    {
                        genres.Add(new Genre()
                        {
                            Id = (int)reader["id"],
                            Name = (string)reader["genre"],
                        });
                    }
                }
            }

            transaction.Commit();

        }
    }

    return genres;
}

401 replies

Gaming

Programming