Cannot retrieve inserted id - no primary key was found.

BenjiHorse

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.

allan

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

BenjiHorse

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

pjgr

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

allan

Hi @pjgr,

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

Thanks,
Allan

pjgr

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]

pjgr

Hey @allan , any idea what could be happening?

thanks

colin

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

pjgr

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);
        }

'''

allan

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