Cannot retrieve inserted id - no primary key was found.

Cannot retrieve inserted id - no primary key was found.

BenjiHorseBenjiHorse Posts: 4Questions: 2Answers: 0

I'm getting a "Cannot retrieve inserted id - no primary key was found." error when I try to create a new record using editor.
I'm using dotnet core 2.1 with razor page model.

Here's my Javascript


(function($){ $(document).ready(function() { var editor = new $.fn.dataTable.Editor({ ajax: { url: '/EmployeePortal/BursaryBundleHandler', //headers: function (d) { d.RequestVerificationToken = $('input:hidden[name="__RequestVerificationToken"]').val();} beforeSend: function (xhr) { xhr.setRequestHeader("RequestVerificationToken", $('input:hidden[name="__RequestVerificationToken"]').val()); } }, table: '#BursaryBundles', fields: [ { "label": "NumberLearners:", "name": "NumberLearners" }, { "label": "TotalCost:", "name": "TotalCost" }, { "label": "PaymentReference:", "name": "PaymentReference" } ], idSrc: "BursaryBundleID" } ); var table = $('#BursaryBundles').DataTable( { dom: 'Bfrtip', ajax: { url: '/EmployeePortal/BursaryBundleHandler', beforeSend: function (xhr) { xhr.setRequestHeader("RequestVerificationToken", $('input:hidden[name="__RequestVerificationToken"]').val()); } }, columns: [ { "data": "NumberLearners" }, { "data": "TotalCost" }, { "data": "PaymentReference" } ], select: true, lengthChange: false, buttons: [ { extend: 'create', editor: editor }, { extend: 'edit', editor: editor }, { extend: 'remove', editor: editor } ] } ); } ); }(jQuery));

Here's the C# Razor page model:


public class BursaryBundleHandlerModel : PageModel { [HttpPost] [ValidateAntiForgeryToken] public JsonResult OnPost([FromBody]DtRequest dTBinder) { var request = HttpContext.Request; using (var db = new Database("sqlserver", "Server = (localdb)\\mssqllocaldb; Database = Edupay; Trusted_Connection = True; MultipleActiveResultSets = true")) { var response = new Editor(db, "dbo.BursaryBundles", "BursaryBundleID") .Model<BursaryBundleVM>() .Process(request) .Data(); return new JsonResult(response); } } [HttpGet] public JsonResult OnGet([FromBody]DtRequest dTBinder) { var request = HttpContext.Request; using (var db = new Database("sqlserver", "Server = (localdb)\\mssqllocaldb; Database = Edupay; Trusted_Connection = True; MultipleActiveResultSets = true")) { var response = new Editor(db, "dbo.BursaryBundles", "BursaryBundleID") .Model<BursaryBundleVM>() .Process(request) .Data(); return new JsonResult(response); } } }

Here's the model:


public class BursaryBundleVM { [JsonProperty("NumberLearners")] public int NumberLearners { get; set; } [JsonProperty("TotalCost")] public float TotalCost { get; set; } [JsonProperty("PaymentReference")] public string PaymentReference { get; set; } }

And here's the sql server table


CREATE TABLE [dbo].[BursaryBundles] ( [BursaryBundleID] INT IDENTITY (1, 1) NOT NULL, [NumberLearners] INT NOT NULL, [SchoolID] INT NULL, [TotalCost] REAL NOT NULL, [PaymentReference] NVARCHAR (MAX) NULL, [EmployeeID] INT NULL );

Any ideas what I'm doing wrong? Many thanks in advance for any help.

Answers

  • allanallan Posts: 61,431Questions: 1Answers: 10,048 Site admin

    Hi,

    What version of the Editor dll are you using? Also, could you add .Debug(true) immediately before the .Process(...) call and then show me the resulting JSON when inserting a new record please?

    Thanks,
    Allan

  • BenjiHorseBenjiHorse Posts: 4Questions: 2Answers: 0

    Hi Allan,

    Thanks for coming back to me, I'm using version 1.8.1.

    I've added the debug option as you've asked, where would I find the JSON that you ask for?

    I show the output from the immediate window below, not sure if this helps?

    Microsoft.AspNetCore.Mvc.RazorPages.Internal.PageActionInvoker:Information: Executed action /Details in 205.5949ms
    Microsoft.AspNetCore.Hosting.Internal.WebHost:Information: Request finished in 252.1333ms 200 text/html; charset=utf-8
    Microsoft.AspNetCore.Hosting.Internal.WebHost:Information: Request starting HTTP/1.1 GET http://localhost:44341/EmployeePortal/BursaryBundleHandler?_=1544553352580
    Microsoft.AspNetCore.Mvc.RazorPages.Internal.PageActionInvoker:Information: Route matched with {page = "/BursaryBundleHandler", area = "EmployeePortal", action = "", controller = ""}. Executing action /BursaryBundleHandler
    Microsoft.AspNetCore.Authorization.DefaultAuthorizationService:Information: Authorization was successful.
    Microsoft.AspNetCore.Mvc.RazorPages.Internal.PageActionInvoker:Information: Executing handler method OnGet with arguments () - ModelState is Invalid
    Microsoft.AspNetCore.Mvc.Formatters.Json.Internal.JsonResultExecutor:Information: Executing JsonResult, writing value of type 'DataTables.DtResponse'.
    Microsoft.AspNetCore.Hosting.Internal.WebHost:Information: Request starting HTTP/1.1 POST http://localhost:44341/EmployeePortal/BursaryBundleHandler application/x-www-form-urlencoded; charset=UTF-8 112
    Microsoft.AspNetCore.Mvc.RazorPages.Internal.PageActionInvoker:Information: Route matched with {page = "/BursaryBundleHandler", area = "EmployeePortal", action = "", controller = ""}. Executing action /BursaryBundleHandler
    Microsoft.AspNetCore.Authorization.DefaultAuthorizationService:Information: Authorization was successful.
    Microsoft.AspNetCore.Mvc.RazorPages.Internal.PageActionInvoker:Information: Executing handler method OnPost with arguments () - ModelState is Invalid
    Exception thrown: 'System.FormatException' in System.Private.CoreLib.dll
    Exception thrown: 'System.FormatException' in System.Private.CoreLib.dll
    Microsoft.AspNetCore.Mvc.RazorPages.Internal.PageActionInvoker:Information: Executed action /BursaryBundleHandler in 18604.8099ms
    Microsoft.AspNetCore.Hosting.Internal.WebHost:Information: Request finished in 20438.3881ms 200 application/json; charset=utf-8
    Exception thrown: 'System.Exception' in DataTables-Editor-Server.dll
    Microsoft.AspNetCore.Mvc.Formatters.Json.Internal.JsonResultExecutor:Information: Executing JsonResult, writing value of type 'DataTables.DtResponse'.
    Microsoft.AspNetCore.Mvc.RazorPages.Internal.PageActionInvoker:Information: Executed action /BursaryBundleHandler in 14693.2607ms
    Microsoft.AspNetCore.Hosting.Internal.WebHost:Information: Request finished in 14700.8489ms 200 application/json; charset=utf-8
    Microsoft.AspNetCore.Hosting.Internal.WebHost:Information: Request starting HTTP/1.1 POST http://localhost:44341/EmployeePortal/BursaryBundleHandler application/x-www-form-urlencoded; charset=UTF-8 112
    Microsoft.AspNetCore.Mvc.RazorPages.Internal.PageActionInvoker:Information: Route matched with {page = "/BursaryBundleHandler", area = "EmployeePortal", action = "", controller = ""}. Executing action /BursaryBundleHandler
    Microsoft.AspNetCore.Authorization.DefaultAuthorizationService:Information: Authorization was successful.
    Microsoft.AspNetCore.Mvc.RazorPages.Internal.PageActionInvoker:Information: Executing handler method OnPost with arguments () - ModelState is Invalid
    Step into: Stepping over property 'Microsoft.AspNetCore.Mvc.RazorPages.PageModel.get_HttpContext'. To step into properties or operators, go to Tools->Options->Debugging and uncheck 'Step over properties and operators (Managed only)'.
    Step into: Stepping over property 'Microsoft.AspNetCore.Http.DefaultHttpContext.get_Request'. To step into properties or operators, go to Tools->Options->Debugging and uncheck 'Step over properties and operators (Managed only)'.
    Exception thrown: 'System.Net.Sockets.SocketException' in System.Net.Sockets.dll
    Exception thrown: 'System.FormatException' in System.Private.CoreLib.dll
    Exception thrown: 'System.FormatException' in System.Private.CoreLib.dll
    Exception thrown: 'System.Exception' in DataTables-Editor-Server.dll
    Microsoft.AspNetCore.Mvc.Formatters.Json.Internal.JsonResultExecutor:Information: Executing JsonResult, writing value of type 'DataTables.DtResponse'.
    Microsoft.AspNetCore.Mvc.RazorPages.Internal.PageActionInvoker:Information: Executed action /BursaryBundleHandler in 584639.5781ms
    Microsoft.AspNetCore.Hosting.Internal.WebHost:Information: Request finished in 584676.5402ms 200 application/json; charset=utf-8

  • pjgrpjgr Posts: 4Questions: 0Answers: 0
    edited February 2019

    Hi,

    I'm also facing the same issue in a very similar situation.
    Cannot retrieve inserted id - no primary key was found

    This is while trying to INSERT into database. Edit and Delete operations work fine.

    Using editor v 1.8.1
    response by the server with debug enabled:

    {"draw":null,"data":[],"recordsTotal":null,"recordsFiltered":null,"error":"Cannot retrieve inserted id - no primary key was found.","fieldErrors":[],"id":null,"meta":{},"options":{},"files":{},"upload":{"id":null},"debug":[{"query":"INSERT INTO [Config].[LOV] ( [Hospital], [Field], [Field_Code], [Field_Value], [Sort_order], [Default_value], [Score_Value] ) VALUES ( @Hospital, @Field, @Field_Code, @Field_Value, @Sort_order, @Default_value, @Score_Value )","bindings":[{"name":"@Hospital","value":"Hospital PT","type":null},{"name":"@Field","value":"Nationality","type":null},{"name":"@Field_Code","value":"xx","type":null},{"name":"@Field_Value","value":"xxxxx","type":null},{"name":"@Sort_order","value":"","type":null},{"name":"@Default_value","value":"","type":null},{"name":"@Score_Value","value":"","type":null}]}],"cancelled":[]}

    Thanks in advance

  • allanallan Posts: 61,431Questions: 1Answers: 10,048 Site admin

    Hi @pjgr,

    Could you show me the schema for your LOV table please?

    Thanks,
    Allan

  • pjgrpjgr Posts: 4Questions: 0Answers: 0

    Here it is:

    CREATE TABLE [Config].[LOV](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Hospital] varchar NULL,
    [Field] varchar NULL,
    [Field_Code] nvarchar NULL,
    [Field_Value] nvarchar NULL,
    [Sort_order] [int] NULL,
    [Default_value] [bit] NULL,
    [CreateOn] [date] NULL,
    [CreateBy] varchar NULL,
    [EditOn] [date] NULL,
    [EditBy] varchar NULL,
    [Score_Value] [int] NULL,
    CONSTRAINT [PK_LOV] PRIMARY KEY CLUSTERED
    (
    [ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]

  • pjgrpjgr Posts: 4Questions: 0Answers: 0

    Hey @allan , any idea what could be happening?

    thanks

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    Hi @pjgr ,

    SQL isn't my strong point - but on the tables we create for the demos, we define the primary key like this:

    CREATE TABLE datatables_demo (
        id         int NOT NULL identity,
        first_name varchar(250) NOT NULL default '',
        last_name  varchar(250) NOT NULL default '',
        position   varchar(250) NOT NULL default '',
        email      varchar(250) NOT NULL default '',
        office     varchar(250) NOT NULL default '',
        start_date datetime default NULL,
        age        int,
        salary     int,
        seq        int,
        extn       varchar(8) NOT NULL default '',
        PRIMARY KEY (id)
    );
    

    Your SQL doesn't look like it's setting the primary key in the same way - could that be the issue?

    Cheers,

    Colin

  • pjgrpjgr Posts: 4Questions: 0Answers: 0

    Thanks for the answer @colin

    I think I've sorted the reason for this error.

    If i try to access a table without a schema everything works as expected. However, if the table has a schema associated (in my case 'Config') it returns the "Cannot retrieve inserted id" error.

    I've tried to work around this error specifying the name of the Schema while creating the fields for the table (see below). I've also tried different aproaches like not setting schema for id field or even manually setting the pkey field in the editor constructor.

    From my perspective somehow the Editor isnt able to determine which field is the primary key on a table with a schema associated. I say this because in this case, while inspecting the debug json, the INSERT operation tries to pass the id field and returns:
    error: "Cannot insert explicit value for identity column in table 'DTTest' when IDENTITY_INSERT is set to OFF."

    '''
    using (var db = new Database(dbType, dbConnection)) {

                var response = new Editor(db, "Config.DTTest")
                    .Model<TestDatatableModel>()
                    .Field(new Field("Config.DTTest.id").Set(Field.SetType.Edit))
                    .Field(new Field("Config.DTTest.first_name").Validator(Validation.NotEmpty()))
                    .Field(new Field("Config.DTTest.last_name").Validator(Validation.NotEmpty()))
                    .Field(new Field("Config.DTTest.age").Validator(Validation.NotEmpty()))
                    .TryCatch(true)
                    .Debug(true)
                    .Process(Request)
                    .Data();
    
                return Json(response);
            }
    

    '''

  • allanallan Posts: 61,431Questions: 1Answers: 10,048 Site admin

    Sorry for the delay. Yes - I think you are right, is a schema name related issue which is a limitation in the Editor .NET libraries.

    I'll look into this in the next few days.

    Allan

This discussion has been closed.