0

So what i am doing is that i am taking the response from a api endpoint, saving it to my DB table and then displaying the table contents.

I have gone through possible reasons as to why one can get a null value, as explained here http://www.sql-server-helper.com/error-messages/msg-515.aspx, and none of those reasons apply in this case.

For this particular table, i am getting a exception error,

An error occurred while updating the entries. See the inner exception for details.

Here is the exception message i get:

An error has occurred. Cannot insert the value NULL into column 'FrameSizeID', table 'diafirearmserver.dbo.Reference_Frame_Sizes'; column does not allow nulls. INSERT fails. The statement has been terminated. System.Data.SqlClient.SqlException at System.Data.SqlClient.SqlCommand.<>c.b__167_0(Task1 result) at System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke() at System.Threading.Tasks.Task.Execute() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Data.Entity.Core.Mapping.Update.Internal.DynamicUpdateCommand.d__0.MoveNext() --- End of stack trace from previous location where exception was thrown --- at System.Runtime.CompilerServices.TaskAwaiter.ThrowForNonSuccess(Task task) at System.Runtime.CompilerServices.TaskAwaiter.HandleNonSuccessAndDebuggerNotification(Task task) at System.Data.Entity.Core.Mapping.Update.Internal.UpdateTranslator.d__0.MoveNext()

My Questions is: Why am i getting a null value being passed, when the table (Screenshot below) has no null values in it? What am i overlooking?

Here is a screenshot of the table that feeds the 'External api endpoint' Here is my data model:

namespace FirearmsAPI.Models
{
    using System;
    using System.Collections.Generic;

    public partial class Reference_Frame_Sizes
    {
        public int FrameSizeID { get; set; }
        public string FrameSize { get; set; }
    }
}

Here is my Controller:

public class Reference_Frame_SizesController : ApiController
{
    private DataEntities db = new DataEntities();
    static string _address = "http://localhost:57454/api/Reference_Frame_Sizes?format=json";
    private List<Reference_Frame_Sizes> result;

    // GET: api/Reference_Frame_Sizes
    /// <summary>
    /// Retrieves Firearm Frame size data from external api endpoint, saves data to DB and returns Data
    /// </summary>
    public async Task<IEnumerable<Reference_Frame_Sizes>> GetReference_Frame_Sizes()
    {
        db.Database.ExecuteSqlCommand("TRUNCATE TABLE Reference_Frame_Sizes"); //Truncates internal table

        List<Reference_Frame_Sizes> resultset = await GetResponse();
        foreach (Reference_Frame_Sizes manu in resultset)
        {
            db.Reference_Frame_Sizes.Add(manu);
        }
        await db.SaveChangesAsync();
        return db.Reference_Frame_Sizes;
    }

    private async Task<List<Reference_Frame_Sizes>> GetResponse()
    {
        var client = new HttpClient();
        HttpResponseMessage response = await client.GetAsync(_address);
        response.EnsureSuccessStatusCode();
        result = await response.Content.ReadAsAsync<List<Reference_Frame_Sizes>>();
        return result;
    }

protected override void Dispose(bool disposing)
    {
        if (disposing)
        {
            db.Dispose();
        }
        base.Dispose(disposing);
    }

    private bool Reference_Frame_SizesExists(int id)
    {
        return db.Reference_Frame_Sizes.Count(e => e.FrameSizeID == id) > 0;
    }
}
8
  • Check the results that are being returned in the GetResponse method. Commented Jul 7, 2016 at 19:33
  • It might be because you never mapped FrameSizeID in your EF mappings. In that case EF will ignore those values on insert and it will default to the default value of NULL in the DB unless you have a default constraint setup AND are using Sql Server. This is a guess because you did not supply the EF mapping code for Reference_Frame_Sizes but a likely one considering its an int property so if it were mapped but no value was assigned then the value of 0 would always be used and you would get a different error entirely. Commented Jul 7, 2016 at 19:36
  • @Igor I checked the FrameSizeID mapping, and its mapped correctly. Commented Jul 7, 2016 at 19:44
  • Are you using/mapping stored procs for the inserts with EF? Either way if you want further help you will have to show your model mapping code as well as the rest of the partial class Reference_Frame_Sizes. Else you will have to figure it out on your own as there is not enough to go on. Commented Jul 7, 2016 at 20:06
  • @Igor My apologies, how do i view the EF to model mapping code?. I only know how to view the Table mapping, while viewing the edmx file. Commented Jul 7, 2016 at 20:26

2 Answers 2

2

You're not getting that error, because you're reading null values from the database, you're getting it because you're trying to insert null values into the database.

Use the debugger to evaluate the result of the GetResponse() method. odds are that one of the FrameSizeID properties is null.

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

6 Comments

I thought that as well but his model has an int type for FrameSizeID and FrameSizeID is listed in the Exception message as the column with the NULL value. I assumed that the column name and property name were the same. As its an int type you could never pass in NULL unless you did so with an stored proc or something with ADO.NET. I was thinking this would make sense if the mapping was not correct as then the int value would never be passed. ... Cannot insert the value NULL into column 'FrameSizeID'
GetResponse returns 8 values, as it should. Here is a screenshot: i693.photobucket.com/albums/vv293/saturobi360/…
@saturobi360 - that is not interesting, what is of interest is the values in each object. If none of the mapped properties in your returned collection have a null value where there should not be one then the only other option as to why it is failing is incorrect EF mapping of your model to your database.
Here is another screenshot showing that GetResponse() returns values: i693.photobucket.com/albums/vv293/saturobi360/… None of the returned objects in the collection has a null value in it.
@saturobi360 - then its your EF to model mapping. Supply that code if you want further help, I also commented this on your post.
|
1

The problem is with the EF mapping. The property FrameSizeID has the property StoreGeneratedPattern set to identity which means that EF will never try to provide a value at insert time. You need to remove that so that EF knows you will provide a value when you create a new record in the database. As EF does not provide a value and the value cannot be null this is generating the exception you are experiencing.

Solution Recap

Set the value of StoreGeneratedPattern to None for property FrameSizeID.

Supporting Documentation

Note

This is based on the latest comment that provided the following screen shot:

EF Properties for FrameSizeID

3 Comments

Thanks so much @Igor, you where very patient in guiding me through working out the issue. Many thanks from me.
although this is obviously the solution, it should be mentioned the mapping is not false by default. it just doesn't match the database schema. for that reason, setting the column to IDENTITY would solve this issue as well.
@DevilSuichiro - the OP is getting the data from an external web service including the value for property/column FrameSizeID so in this situation an Identity column would not work because the OP wants to explicitly set the value of the column and not have a sequential database generated value.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.