Cannot insert the value NULL into column 'CompanyId' column does not allow nulls. INSERT fails

Question

I am getting this error when I try to save to my database using EF.

Cannot insert the value NULL into column 'CompanyId' column does not allow nulls.

From the message it is quite obvious what the problem is, but it don't see any reasons in my code to have this problem.

Here is my model :

[Key]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)]
public int CompanyId { get; set; }
public string CompanyName { get; set; }
public string CompanyCountry { get; set; }
public string CompanyCity { get; set; }
public string CompanyPostalCode { get; set; }
public string CompanyPhoneNumber { get; set; }
public string EmailCA { get; set; }

Controller :

[HttpPost]
[ValidateAntiForgeryToken]
public ActionResult Create([Bind(Include = "CompanyId,CompanyName,CompanyAddress,CompanyCountry,CompanyCity,CompanyPostalCode,CompanyPhoneNumber,EmailCA")] Company company)
{
    if (ModelState.IsValid)
    {
        db.Companies.Add(company);
        db.SaveChanges();
        return RedirectToAction("Index");
    }
    return View(company);
}

What I am expecting from these attributes is to have an auto generated key from them, but obviously this doesn't work.

Can someone help and explain, please?

@Ganesh_Devlekar The error message already shows that it is `not null` in the database, and that's exactly what it should be. What could be a problem though is if the database definition makes it non-nullable, but doesn't make it an identity column. So seeing the database definition of the table / column would still be useful. — , Dec 21 '16 at 14:22
The only explanation is that the database column is not set to Identity. — Igor, Dec 21 '16 at 14:31
You shouldn't bind the CompanyId because it's auto generated in database (Identity). Probably you're not passing it from your html and the model binder is setting it to null. — Douglas Gandini, Dec 21 '16 at 14:37

score 2 · Answer 1 · answered Dec 21 '16 at 14:32

2

The only explanation based on your code is that the database column definition for CompanyId is not set to Identity. When you execute your insert from the DbContext a value for CompanyId will not be provided because it assumes the database will generate it based on the attribute [DatabaseGenerated(DatabaseGeneratedOption.Identity)] you supplied on the CompanyId property. The (bad) schema has a non nullable (and no identity) column for CompanyId, hens the exception.

answered Dec 21 '16 at 14:32

Igor

60,821
10
100
175

if by "not set do indetity you mean "is identity - False. Then yes. This is what i see when i double click on the column – Robert Ross Dec 21 '16 at 14:42
@RobertRoss - right, your current (wrong) database schema has that column demarked (not marked) as identity. If you change the database schema and check (mark) CompanyId as Identity you will fix the problem. – Igor Dec 21 '16 at 14:43
Any suggestions how to do that? – Robert Ross Dec 21 '16 at 14:46
@RobertRoss - if you are using SQL then see http://stackoverflow.com/q/1049210/1260204 or you can use the designer and save the changes that way. – Igor Dec 21 '16 at 14:47

Konstantin Dinev · Answer 2 · 2016-12-21T15:09:28.547

1

If you're doing database first or model first, then check your .edmx and see whether in the properties of CompanyId the StoreGeneratedPattern is set to Identity, Computed, or None. If it's not correctly set there, then change it to Identity.

If you're doing code first, then the DB column is not set to identity.

edited Dec 21 '16 at 15:09

answered Dec 21 '16 at 14:31

Konstantin Dinev

34,219
14
75
100

Thanks for the answer. Probably this is the problem indeed, but I don't even know where that file is. Can you help with that? – Robert Ross Dec 21 '16 at 14:38
@RobertRoss - If you are using EF code first (and you are based on your model attribute) there will not be an `.edmx` file. – Igor Dec 21 '16 at 14:39
@RobertRoss Since you're going code first, you should indeed refer to Igor's answer. – Konstantin Dinev Dec 21 '16 at 15:08

score 0 · Answer 3 · answered Dec 23 '16 at 13:02

You can use a stored procedure to produce an identity (next id), i used one like this:

ALTER procedure [dbo].[ctgetNextID]  @tableName nchar(40), @outid int output
AS      
    declare @id_next int      
    declare @id_table int
    select  @id_next = 0
    declare @id_inc int
    declare @sql nvarchar(200)

BEGIN TRANSACTION T1
    select @id_table = bord_tableid from custom_tables where bord_caption = @tableName

    exec('DECLARE my_cursor CURSOR FOR SELECT Id_NextId FROM ' +      
            'SQL_Identity WITH (UPDLOCK) WHERE Id_TableId ='+@id_table)      

    --open cursor and fetch max back      
    open my_cursor      
    fetch next from my_cursor into @id_next

    if (@@fetch_status=0) begin             
        select @id_inc = @id_next+1
        select @sql = 'update SQL_Identity set Id_NextId='+STR(@id_inc)+' where Id_TableId='+STR(@id_table)
            exec(@sql)      
        end 

        close my_cursor      
        deallocate my_cursor      
COMMIT TRANSACTION T1

       select @outid = @id_next
       return @outid



SET QUOTED_IDENTIFIER ON

Cannot insert the value NULL into column 'CompanyId' column does not allow nulls. INSERT fails

3 Answers3