Web API error: "Cannot insert the value NULL into column 'Id'

Question

I am using Web API and using the get methods in fiddler with some sample data in the table, I can get data from it.

When I try to insert a new record using a post method, I get the error:

"Cannot insert the value NULL into column 'Id', table 'Members'; column does not allow nulls. INSERT fails. The statement has been terminated."

The thing is, I am actually supplying the value for Id in the JSON string. I am copying the json string I get from the get/id method exactly and just changing the Id.

I do have a column on the table called Id which is set to not allow nulls, and is NOT set to auto increment. This is because the requirements of the system only require that it be present and unique. It doesn't have to be sequential. So I want the person inserting to be able to supply their own id.

I don't understand why this error is coming up, as I am supplying all the data required.

Here is the database:

enter image description here

and here is my JSON string:

{
"Id":687,
"FirstName":"auto",
"EMail":"auto",
"Eligible":true,
"InsertLogtime":"2015-07-21T00:51:59.917"
}

This is the server side code: Apologies for not including it. When I step into this in debugger, the members class is actually initialised with an Id.

// POST api/Members
[ResponseType(typeof(Members))]
public IHttpActionResult PostMembers(Members members)
{
    if (!ModelState.IsValid)
        return BadRequest(ModelState);

    db.Members.Add(members);
    db.SaveChanges();

    return CreatedAtRoute("DefaultApi", new { id = members.Id }, members);
}

enter image description here

The Members class, as requested:

public partial class Members
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string EMail { get; set; }
    public Nullable<bool> Eligible { get; set; }
    public Nullable<System.DateTime> InsertLogtime { get; set; }
}

Below is the outermost exception stack trace...

   at System.Data.Entity.Internal.InternalContext.SaveChanges()
   at System.Data.Entity.Internal.LazyInternalContext.SaveChanges()
   at System.Data.Entity.DbContext.SaveChanges()
   at Xyz.Service.Controllers.MembersController.PostMembers(Members members) in c:\Repositories\Xyz\Xyz.Service\Controllers\MembersController.cs:line 41
   at lambda_method(Closure , Object , Object[] )
   at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.<>c__DisplayClass10.<GetExecutor>b__9(Object instance, Object[] methodParameters)
   at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ActionExecutor.Execute(Object instance, Object[] arguments)
   at System.Web.Http.Controllers.ReflectedHttpActionDescriptor.ExecuteAsync(HttpControllerContext controllerContext, IDictionary`2 arguments, CancellationToken cancellationToken)

I'd really appreciate any help finding the problem.

Can you please post the code that actually handles the POST/PUT request on the server side? Just because you're sending JSON data with an ID in it does not mean that the server is doing anything with it — Jesse Carter
– Jesse Carter, Commented Jul 21, 2015 at 0:50
I just inserted it now, apologies and thanks. It was auto generated by EF. — Francis Rodgers
– Francis Rodgers, Commented Jul 21, 2015 at 1:01
The members class is auto generated by EF from database. So id imagine that if it was required then it would be there. But not sure. Thanks for your help. — Francis Rodgers
– Francis Rodgers, Commented Jul 21, 2015 at 1:19

Francis Rodgers · Accepted Answer · 2015-07-21 14:48:36Z

3

I want to thank everyone here for their help... You were really great.

I finally found a solution that works after many hours of digging and given I use S/O for my own reference, I decided to just place it here. If anyone can think of an alternative that should have worked, I'd really like to hear about it.

Here is my solution:

In the EF Autogenerated Members class above:

public partial class Members
{
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string EMail { get; set; }
    public Nullable<bool> Eligible { get; set; }
    public Nullable<System.DateTime> InsertLogtime { get; set; }
}

Simply add this line:

public partial class Members
{
    [DatabaseGenerated(DatabaseGeneratedOption.None)]
    public int Id { get; set; }
    public string FirstName { get; set; }
    public string EMail { get; set; }
    public Nullable<bool> Eligible { get; set; }
    public Nullable<System.DateTime> InsertLogtime { get; set; }
}

I hope others find it helpful, and thanks everyone for your help again.

answered Jul 21, 2015 at 14:48

Francis Rodgers

4,7058 gold badges49 silver badges67 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

DaniDev Over a year ago

Wouldn't this (change) get overwritten any time you recompile the EF model, as this class is auto generated?

Xiaoy312 · Accepted Answer · 2017-03-23 20:36:05Z

For this object:

public class Recipe
{
    public int RecipeId { get; set; }
    [Required]
    public string Title { get; set; }
    public string Source { get; set; }
    public CategoryEnum Category { get; set; }
    public string Instructions { get; set; }
    public virtual ICollection<RecipeNote> Notes { get; set; }
}

I would see the same error you reported above when I tried to post the following from JavaScript:

{"recipeId":"","title":"test","category":1,"source":"test","instructions":"test"}

This caused the same null error that you have above. RecipeId couldn't be null. I fixed this for a short term by using Public int? RecipeId{get;set;}. But that caused other problems. I found a better solution.

I discovered that if I poseted the same data WITHOUT the recipeId field (like below) I didn't get the error. The recipeId field was automatically populated in the database like it should be.

{"title":"test","category":1,"source":"test","instructions":"test"}

So I think the best fix is to exclude the ID field when posting data to your Web API function to add a record.

Hope this helps someone.

Collectives™ on Stack Overflow

Web API error: "Cannot insert the value NULL into column 'Id'

2 Answers 2

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related