Copied to clipboard

Flag this post as spam?

This post will be reported to the moderators as potential spam to be looked at


  • Nathan Grumbine 7 posts 77 karma points
    Jan 11, 2017 @ 21:13
    Nathan Grumbine
    0

    ApiController + Custom Data table insert issues.

    I'm working with the latest version of 7.5.7

    I have a Poco Decorated class ...

    using System;
    using System.Collections.Generic;
    using System.Linq;
    using System.Text;
    using System.Threading.Tasks;
    
    namespace uPost.Data
    {
        using Umbraco.Core.Persistence.DatabaseAnnotations;
        using Umbraco.Core.Persistence;
    
        [TableName(uPost.Constants.CustomDBObjects.WeddingVendors)]
        [PrimaryKey("Id")]
        public class WeddingVendor
        {
            public WeddingVendor()
            {
                Id = Guid.NewGuid();
            }
    
            [PrimaryKeyColumn(AutoIncrement =false, Clustered =true)]
            public Guid Id { get; set; }
    
            public string Name { get; set; }
    
            public string Url { get; set; }
    
        }
    }
    

    I'm trying to just insert a new record in my api...

    namespace uPost.Controllers
    {
        using System.Collections.Generic;
        using System.Web.Http;
        using Umbraco.Web.WebApi;
        using System.Threading.Tasks;
    
        using Data;
    
        public class WeddingVendorApiController : UmbracoApiController
        {
    
            [HttpGet]
            public IEnumerable<WeddingVendor> GetAll()
            {
                var db = ApplicationContext.DatabaseContext.Database;
    
                return db.Fetch<WeddingVendor>("Select * from " + uPost.Constants.CustomDBObjects.WeddingVendors);
            }
    
            [HttpGet]
            public void Insert(string Name, string Url)
            {
                var newVendor = new WeddingVendor() { Name = Name, Url = Url };
    
                var db = ApplicationContext.DatabaseContext.Database;
                db.Insert(newVendor);
            }
    
    
        }
    }
    

    The table is created just fine using ...

        var logger = LoggerResolver.Current.Logger;
        var dbContext = ApplicationContext.Current.DatabaseContext;
        var dbhelper = new DatabaseSchemaHelper(dbContext.Database, logger, dbContext.SqlSyntax);
    
        dbhelper.CreateTable<Data.WeddingVendor>(false);
    

    When i try to insert i get the following error...

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

    When i debug my object has is completely filled out... Id has a guid value. is there something special i'm missing? this is my first time doing this in Umbraco... my background in EF i feel i'm doing everything right. please help.

  • Damiaan 442 posts 1301 karma points MVP 6x c-trib
    Jan 12, 2017 @ 10:03
    Damiaan
    0

    Hi Nathan,

    You have disabled autoIncrement on the ID column.

    [PrimaryKeyColumn(AutoIncrement =false, Clustered =true)] // <-- HERE
    public Guid Id { get; set; }
    

    So when inserting, the ID column will be Null. And that is exactly the error you get. I would consider activating the autoIncrement :-)

  • Nathan Grumbine 7 posts 77 karma points
    Jan 12, 2017 @ 13:52
    Nathan Grumbine
    0

    I'm wanting to use a GUID for my id. I tried your suggestion, and I get this error.

    Additional information: Identity column 'Id' must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, and constrained to be nonnullable.

    That's why I had Autoincrement set to false.

  • Damiaan 442 posts 1301 karma points MVP 6x c-trib
    Jan 12, 2017 @ 15:08
    Damiaan
    0

    Right, did not noticed the Guid.

    If I remember correctly, it's one of the two solutions below:

    [PrimaryKeyColumn(AutoIncrement =false, Clustered =true)]
    public Guid? Id { get; set; }  // <- make this one nullable
    

    or supply the GUID when inserting

    var newVendor = new WeddingVendor() { 
       Id= Guid.MakeNew(),  // <- add this
       Name = Name, 
       Url = Url 
     };
    
  • Nathan Grumbine 7 posts 77 karma points
    Jan 12, 2017 @ 21:03
    Nathan Grumbine
    0

    isn't this a direct violation of MSSQL DB. you can't have a nullable primary key.

  • Damiaan 442 posts 1301 karma points MVP 6x c-trib
    Jan 13, 2017 @ 14:07
    Damiaan
    0

    With this solution, you need to add "newid()" as default value in the database column, the field will not be generated in the update statement.

    The column in the database has still "not null".

    We've done this several times. If it's not working, let me know i'll look up exact code...

  • David Peck 687 posts 1863 karma points c-trib
    Jan 12, 2017 @ 16:36
    David Peck
    0

    I think your solution of setting the ID in the constructor is fine. That's the way I do it.

    The only thing I can see which is different to the way I do it is the inclusion of the Clustered property on the PrimaryKeyColumnAttribute. What happens if you get rid of that?

  • Nathan Grumbine 7 posts 77 karma points
    Jan 12, 2017 @ 21:01
    Nathan Grumbine
    0

    That didn't work

  • David Peck 687 posts 1863 karma points c-trib
    Jan 12, 2017 @ 16:37
    David Peck
    0

    Alternatively use NPoco, if PetaPoco has a bug which is quite plausible.

  • David Brendel 792 posts 2970 karma points MVP 3x c-trib
    Jan 12, 2017 @ 16:51
    David Brendel
    0

    Hi Nathan,

    check if your table has correct Schema.

    Had the same error yesterday and it is quite misguiding as the actuall error was a different column for me.

    After recreating the table with the correct settings everything worked.

    Regards David

  • Nathan Grumbine 7 posts 77 karma points
    Jan 12, 2017 @ 21:02
    Nathan Grumbine
    0

    i checked it, and its not working. deleted the table and recreated still get error.

Please Sign in or register to post replies

Write your reply to:

Draft