Cannot retrieve inserted id - no primary key was found

Cannot retrieve inserted id - no primary key was found

ctran2ctran2 Posts: 11Questions: 0Answers: 0

I'm getting this error "Cannot retrieve inserted id - no primary key was found." when trying to create a new record using editor.
I've searched the forums and found similar issues, but can't seem to apply these responses to a resolution:

http://mail.datatables.net/forums/discussion/45626/cannot-retrieve-inserted-id-no-primary-key-was-found
https://datatables.net/forums/discussion/53576/cannot-retrieve-inserted-id-no-primary-key-was-found
http://mail.datatables.net/forums/discussion/59623/cannot-retrieve-inserted-id

Our use case involves:
* .NET 6.0
* Editor 2.0.6
* SQL Server 2019

My web page has 2 tabs:
* UserInformation table to store personal info of users
* A SQL view joining 2 tables - UserInformation and UserAccess. The view edits Email and Access columns from UserAccess table using select dropdowns. For now I can edit current records, but have issue with creating a new one.

View model:

public class UserInfoAndAccessModel
    {
        public int UserAccessId { set; get; }   // PK of UserAccess table
        public string? FirstName { set; get; }
        public string? LastName { set; get; }
        public string? Title { set; get; }
        public string? Email { set; get; }
        public string? Access { set; get; }

    }

UserInformation model:

public class UserInformationModel
    {
        public int UserId { set; get; }
        public string? FirstName { set; get; }
        public string? LastName { set; get; }
        public string? Title { set; get; }
        public string? Email { set; get; }

    }

UserAccess model:

 public class UserAccessModel
    {
        public int UserAccessId { set; get; }
        public string? Email { set; get; }
        public string? Access { set; get; }

    }

Executing insert query directly in SQL Server did create new data. It just did not work from the web app. SQL tracker showed that the query was completed when I hit the create button, just no record was added, and the "no primary key" error popped up. Below is the insert query that was recorded:

exec sp_executesql N'INSERT INTO  [VW_UserInfoAndAccess]  ( [Access], [Email] ) VALUES (  @Access,  @Email )',N'@Access nvarchar(7),@Email nvarchar(24)',@Access=N'Austria',@Email=N'abc@gmail.com'

View controller:

    public class UserInfoAndAccessController : ControllerBase
    {
        [HttpGet]
        [HttpPost]
        public ActionResult UserInfoAndAccess()
        {
            string? cnStr = Program.ConnectionString; 
            using (var db = new Database("sqlserver", cnStr))
            {
                var editor = new Editor(db, "VW_UserInfoAndAccess", "UserAccessId")
                    .Model<UserInfoAndAccessModel>("VW_UserInfoAndAccess")

                    // Access column refers to CountryName column
                    .Field(new Field("VW_UserInfoAndAccess.Access").Options("Country", "CountryName", "CountryName")
                        .Validator(Validation.DbValues())
                    )
                    .Field(new Field("Country.CountryName").Set(false))
                    .LeftJoin("Country", "Country.CountryName = VW_UserInfoAndAccess.Access")

                    // UserAccess's Email refers to UserInformation's Email
                    .Field(new Field("VW_UserInfoAndAccess.Email").Options("UserInformation", "Email", "Email")
                        .Validator(Validation.DbValues())
                    )
                    .Field(new Field("UserInformation.Email").Set(false))
                    .LeftJoin("UserInformation", "UserInformation.Email = VW_UserInfoAndAccess.Email");
               
                 // Return data
                return new JsonResult(editor.Process(Request).Data());

            }
}

View js:

        var editor = new $.fn.dataTable.Editor({
            ajax: {
                "url": '/api/UserInfoAndAccess',
                "type": 'POST'
            },
            table: '#UserInfoAndAccess',
            fields: [
                {
                    "label": "Email",
                    "name": "VW_UserInfoAndAccess.Email",
                    "type": "select"
                },
                {
                    "label": "Access",
                    "name": "VW_UserInfoAndAccess.Access",
                    "type": "select"
                }
            ]
        });

        var table = $('#UserInfoAndAccess').DataTable({
            dom: 'Bfiprt',
            orderCellsTop: true,
            fixedHeader: true,
            "lengthMenu": [[12, 24, 36, 48, 60, -1], [12, 24, 36, 48, 60, "All"]],
            "pageLength": 60,

            ajax: {
                "url": '/api/UserInfoAndAccess',
                "type": 'POST'
            },
            columns: [
                {
                    "data": "VW_UserInfoAndAccess.FirstName"
                },
                {
                    "data": "VW_UserInfoAndAccess.LastName"
                },
                {
                    "data": "VW_UserInfoAndAccess.Title"
                },
                {
                    "data": "UserInformation.Email",
                    "editField": "VW_UserInfoAndAccess.Email"
                },
                {
                    "data": "Country.CountryName",
                    "editField": "VW_UserInfoAndAccess.Access"
                },
            ],
            select: true,
            lengthChange: false,
            buttons: [
                { extend: "create", editor: editor },
                { extend: "edit", editor: editor },
                { extend: "remove", editor: editor }
            ],
        });

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

Replies

  • allanallan Posts: 56,837Questions: 1Answers: 9,035 Site admin

    Can you show me the schema for your DB table please?

    Thanks,
    Allan

  • ctran2ctran2 Posts: 11Questions: 0Answers: 0

    Hi Allan - thank you for the prompt response. Here are my tables:

    UserInformation:

    CREATE TABLE [dbo].[UserInformation](
        [UserId] [int] IDENTITY(1,1) NOT NULL,
        [FirstName] [varchar](40) NULL,
        [LastName] [varchar](50) NULL,
        [Title] [varchar](100) NULL,
        [Email] [varchar](60) NULL,
    PRIMARY KEY CLUSTERED 
    (
        [UserId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
    UNIQUE NONCLUSTERED 
    (
        [Email] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    

    UserAccess:

    CREATE TABLE [dbo].[UserAccess](
        [UserAccessId] [int] IDENTITY(1,1) NOT NULL,
        [Email] [varchar](60) NULL,
        [Access] [varchar](30) NULL,
    PRIMARY KEY CLUSTERED 
    (
        [UserAccessId] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
     CONSTRAINT [UQ_UserAccess_Email] UNIQUE NONCLUSTERED 
    (
        [Email] ASC,
        [Access] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    

    View:

    CREATE VIEW [dbo].[VW_UserInfoAndAccess]
    AS
    SELECT dbo.UserInformation.FirstName, dbo.UserInformation.LastName, dbo.UserInformation.Title, dbo.UserAccess.Access, dbo.UserAccess.Email, dbo.UserAccess.UserAccessId
    FROM   dbo.UserAccess INNER JOIN
                 dbo.UserInformation ON dbo.UserInformation.Email = dbo.UserAccess.Email
    

    Country: Country.CountryName is data for editField VW_UserInfoAndAccess.Access

    CREATE TABLE [dbo].[Country](
        [CountryName] [varchar](30) NOT NULL,
     CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED 
    (
        [CountryName] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
    ) ON [PRIMARY]
    
  • allanallan Posts: 56,837Questions: 1Answers: 9,035 Site admin

    Super - thank you. I'm with you now.

    This is where we are tripping up. We attempt to read the primary key from the information schema, but the view doesn't have matching information there, hence it fails when operating on that view for an insert.

    What I'm wondering about is how to bypass that lookup. You are explicitly giving it the primary key name, so I'm thinking about just passing that through.

    Let me prototype that and I'll get back to you next week.

    Regards,
    Allan

  • ctran2ctran2 Posts: 11Questions: 0Answers: 0

    hi @allan - have you got an opportunity to take a look at this yet?

  • allanallan Posts: 56,837Questions: 1Answers: 9,035 Site admin

    Apologies - no not yet. I had a health hiccup (slipped disc) which has set me back a bit and only just now starting to get back on the case. I'll try to look at this in the next day or two :)

    Allan

  • allanallan Posts: 56,837Questions: 1Answers: 9,035 Site admin

    Hi,

    I've been doing some work on this today and believe I have a fix now. Could you try the appropriate dll for the .NET version you are using from this download. It should address the issue by using the primary key name that you give it in the Editor constructor.

    Regards,
    Allan

  • ctran2ctran2 Posts: 11Questions: 0Answers: 0

    Hi @allan, thanks for the prompt response. I tried the dll inside Netcoreapp2.1 folder that you sent but still got the same error. Is this the proper one to use while my application is .NET 6?

  • allanallan Posts: 56,837Questions: 1Answers: 9,035 Site admin

    The .NET core one should do the job nicely in .NET 6.

    That's disappointing that it didn't work! My testing seemed to show that it was all good. Can you show me the JSON response from an insert command when the .Debug(true) option enabled please?

    Allan

  • ctran2ctran2 Posts: 11Questions: 0Answers: 0

    I set .Debug(true):

    {"draw":null,"data":[],"recordsTotal":null,"recordsFiltered":null,"error":"Cannot retrieve inserted id - no primary key was found.","fieldErrors":[],"id":null,"meta":{},"options":{},"searchBuilder":{"options":{}},"searchPanes":{"options":{}},"files":{},"upload":{"id":null},"debug":[{"query":"SELECT  [CountryName] as 'CountryName' FROM  [Country] WHERE [CountryName] = @where_0 ","bindings":[{"name":"@where_0","value":"USA","type":null}]},{"query":"SELECT  [Email] as 'Email' FROM  [UserInformation] WHERE [Email] = @where_0 ","bindings":[{"name":"@where_0","value":"user@gmail.com","type":null}]},{"query":"INSERT INTO  [VW_UserInfoAndAccess]  ( [Access], [Email] ) VALUES (  @Access,  @Email )","bindings":[{"name":"@Access","value":"USA","type":null},{"name":"@Email","value":"user@gmail.com","type":null}]}],"cancelled":[]}
    

    Let me know if you need any further information.

  • allanallan Posts: 56,837Questions: 1Answers: 9,035 Site admin

    Thanks. There is something wrong there - the insert SQL is not complete. I've added some more debug and uploaded a new dll. Could you download this new one, install and then run an insert command again and send me the JSON debug output again?

    Thanks,
    Allan

  • ctran2ctran2 Posts: 11Questions: 0Answers: 0
    edited August 10

    Hi @allan, I completely removed the Nugget package and tried adding reference to both of the dll files you sent in the last two posts. In both cases, the debug outputs were identical.

  • allanallan Posts: 56,837Questions: 1Answers: 9,035 Site admin

    Darn it sorry. I gave you the old URL... The correct new one is http://datatables.net/dev/Editor-dll-pkey-fix-2.zip . Could you try with that please?

    Allan

  • ctran2ctran2 Posts: 11Questions: 0Answers: 0

    @allan, no worries. This time the debug output was

    {"draw":null,"data":[],"recordsTotal":null,"recordsFiltered":null,"error":"Cannot retrieve inserted id - no primary key was found.","fieldErrors":[],"id":null,"meta":{},"options":{},"searchBuilder":{"options":{}},"searchPanes":{"options":{}},"files":{},"upload":{"id":null},"debug":[{"query":"SELECT  [CountryName] as 'CountryName' FROM  [Country] WHERE [CountryName] = @where_0 ","bindings":[{"name":"@where_0","value":"USA","type":null}]},{"query":"SELECT  [Email] as 'Email' FROM  [UserInformation] WHERE [Email] = @where_0 ","bindings":[{"name":"@where_0","value":"user@gmail.com","type":null}]},{"query":"Defined pkey is: VW_UserInfoAndAccess.UserAccessId","bindings":null},{"query":"Get pkey info: \r\n                        SELECT\r\n                            DATA_TYPE as data_type,\r\n                            CHARACTER_MAXIMUM_LENGTH as data_length,\r\n                            COLUMN_NAME as column_name\r\n                        FROM INFORMATION_SCHEMA.COLUMNS\r\n                        WHERE \r\n                            \r\n                            TABLE_NAME   = @table AND \r\n                            COLUMN_NAME  = @column\r\n                    ","bindings":null},{"query":"No rows found","bindings":null},{"query":"INSERT INTO  [VW_UserInfoAndAccess]  ( [Access], [Email] ) VALUES (  @Access,  @Email )","bindings":[{"name":"@Access","value":"USA","type":null},{"name":"@Email","value":"user@gmail.com","type":null}]}],"cancelled":[]}
    
  • ctran2ctran2 Posts: 11Questions: 0Answers: 0

    Hi @allan, anything useful in this debug that shows what could be triggering the issue?

  • allanallan Posts: 56,837Questions: 1Answers: 9,035 Site admin

    Apologies for the long delay on this one! I might know why that failed. One more try if you would be so kind: http://datatables.net/dev/Editor-dll-pkey-fix-3.zip . I've added a little bit more debug as well to check I've do it right this time (and hopefully enough to correct it if not!).

    Regards,
    Allan

  • ctran2ctran2 Posts: 11Questions: 0Answers: 0

    @allan, the new DLL works great. Update and New records now work. The only issue I still have is on delete, but this is a SQL constraint as a delete would be referencing both underlying tables that are joined in a view. Is there a way to modify the delete SQL syntax from a datatable's perspective so that it is only referencing the same fields that both update and new refer too?

  • ctran2ctran2 Posts: 11Questions: 0Answers: 0

    Here's the debug of delete:
    {"draw":null,"data":[],"recordsTotal":null,"recordsFiltered":null,"error":"View or function 'VW_UserInfoAndAccess' is not updatable because the modification affects multiple base tables.","fieldErrors":[],"id":null,"meta":{},"options":{},"searchBuilder":{"options":{}},"searchPanes":{"options":{}},"files":{},"upload":{"id":null},"debug":[{"query":"DELETE FROM [VW_UserInfoAndAccess] WHERE ([VW_UserInfoAndAccess].[UserAccessId] = @where_1 )","bindings":[{"name":"@where_1","value":"136","type":null}]}],"cancelled":[]}

  • allanallan Posts: 56,837Questions: 1Answers: 9,035 Site admin

    Fantastic - good to hear that did the job!

    Regarding the delete - it just does a delete on rows, not specific columns / fields (I'm actually not sure how that would work?). There isn't a way to modify the SQL it generates, but you could capture the delete action send from the client-side and perform your own delete instead of calling the Editor class.

    Allan

  • ctran2ctran2 Posts: 11Questions: 0Answers: 0

    Thanks, @allan. I'm also not sure if deleting certain columns actually works. I'm thinking of performing a delete statement directly on the base table with a custom SQL query when a user deletes a record of the View. Could you guide me a bit more on how to "capture the delete action sent from the client-side and perform your own delete instead of calling the Editor class" or possibly provide some source code for this? I imagine it would look something like this:

    public ActionResult UserInfoAndAccess()
            {
                   // Minimized code for simplicity
    
                   // Execute custom delete
                   // Get error cannot convert 'object' to 'int'
                    editor.PreRemove += (sender, e) => Delete(e.Id)
    
                    
                    return new JsonResult(editor.Process(Request).Data());
    
                }
            }
    // if action = remove then execute Delete function
    public int Delete(int id)
            {
                string? cnStr = Program.ConnectionString;
                int count = 0;
    
                using (var con = new SqlConnection(cnStr))
                {
                    try
                    {
                        con.Open();
                        // Delete query for delete action
                        var query = "DELETE FROM UserAccess WHERE UserAccessId =" + id;
                        con.Execute(query);
                    }
                    catch (Exception ex)
                    {
                        throw ex;
                    }
                    finally
                    {
                        con.Close();
                    }
    
                    return count;
                }
            }
    

    Let me know if any of this makes sense.

  • allanallan Posts: 56,837Questions: 1Answers: 9,035 Site admin

    Yup, in C# you need to do something like:

    var request = new DtRequest( Request.Form );
    
    if ( request.Action === DtRequest.RequestTypes.EditorRemove ) {
      // Do delete
    }
    else {
      // Do Editor
    }
    

    This is the code for the DtRequest class.

    Allan

Sign In or Register to comment.