CODEWITHSUNDEEP
c#asp.netsql-serverlinq
J.G.Sable
J.G.Sable

Reputation: 1388

Invalid column name for SQL Server update-database

I'm trying to populate a table of genres but continually get an error:

Invalid column name

I have a simple class model for movie genres.

public class Genre
{
    public int Id { get; set; }
    public string Name { get; set; }
}

A movie class is tied to the genre like this:

public class Movie
{
    public int Id { get; set; }

    [Required]
    public string Name { get; set; }

    [Required]
    public Genre Genre { get; set; }

    [Required]
    public DateTime ReleaseDate { get; set; }

    [Required]
    public DateTime DateAdded { get; set; }

    [Required]
    public int  NumInStock { get; set; }
}

In my Nuget console, I run add-migration and it generated an empty Genre table for me with two columns, Id & name.

Then, I try and populate the genre table with this SQL query:

public override void Up()
{
    Sql("INSERT INTO Genres (Id, Name) VALUES (1, Fantasy)");
    Sql("INSERT INTO Genres (Id, Name) VALUES (2, Drama)");
    Sql("INSERT INTO Genres (Id, Name) VALUES (3, Action-Adventure)");
    Sql("INSERT INTO Genres (Id, Name) VALUES (4, Foreign)");
    Sql("INSERT INTO Genres (Id, Name) VALUES (5, Horror)");
    Sql("INSERT INTO Genres (Id, Name) VALUES (6, Romance)");
    Sql("INSERT INTO Genres (Id, Name) VALUES (7, Crime)");
    Sql("INSERT INTO Genres (Id, Name) VALUES (8, Thriller)");
    Sql("INSERT INTO Genres (Id, Name) VALUES (9, Animated)");
    Sql("INSERT INTO Genres (Id, Name) VALUES (10, Western)");
}

Not sure what I'm doing wrong. if I put 'fantasy' in quotes (because it is maybe expecting a string?) then I receive this error:

Cannot insert explicit value for identity column in table 'Genres' when IDENTITY_INSERT is set to OFF.

Upvotes: 0

Views: 648

Answers (2)

Alexcei Shmakov
Alexcei Shmakov

Reputation: 2353

As other option you can turn on SET IDENTITY_INSERT for this table. It is allows explicit value to be inserted into the identity column of a table.
And how was marked you should set string value in single quotes

Sql("SET IDENTITY_INSERT Genres ON");
Sql("INSERT INTO Genres (Id, Name) VALUES (1, 'Fantasy')");
.....
Sql("SET IDENTITY_INSERT Genres OFF");

Upvotes: 1

sgeddes
sgeddes

Reputation: 62831

You definitely need the single quotes to insert string values. From your error message, the Id column is an auto increment (identity) column. So you can't explicitly define those values without setting identity_insert.

Easiest fix is to remove the Id from your insert and allow the database to maintain those values. For example: 

insert into genres (name) values ('Fantasy')

Upvotes: 2

Related Questions