Cannot retrieve inserted id - no primary key was found

ctran2

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.

allan

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

Thanks,
Allan

ctran2

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]

allan

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

ctran2

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

allan

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

allan

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

ctran2

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?

allan

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

ctran2

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.

allan

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

ctran2

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.

allan

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

ctran2

@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":[]}

ctran2

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

allan

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

ctran2

@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?

ctran2

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":[]}

allan

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

ctran2

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.

allan

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