Problem creating records with IdentityColumn thats not part of primary key

riaanvdl

Hi,

I have a database table (actually several tables that follow this pattern) that is designed with an Identity column that does not form part of the primary key. Because of the way the system is designed I cannot include the Identity column as part of the primary key.

I am using the .Net libaries and this is my code for CRUD operations.

            using (var dt_db = new DataTables.Database(Constants.DT_Editor_Connection, this.db.Database.Connection))
            {
                var response = new Editor(dt_db, "part_ingredients", "pti_id")
                    .Debug(true)
                    .Model<Part_Ingredients>()
                    .Field(
                        new Field("pti_lastmodified")
                            .SetValue(PSI_Control.Helpers.General.NZDateToServerDate(DateTime.Now))
                    )
                    .Field(
                        new Field("pti_modifiedby_userid")
                            .SetValue(Helpers.General.CurrentUserID())
                    )
                    .Process(request)
                    .Data();

                return Json(response, new JsonSerializerSettings { ReferenceLoopHandling = Newtonsoft.Json.ReferenceLoopHandling.Ignore });
            }

Editing an existing row from the datatable works perfectly, the problem I am seeing is that when I try to create a new entry, the editor code automatically tries to return the inserted id, but the problem is that it is not using the field name that I am specifying when I create a new instance of the editor (in this case called "pti_id")
It seems to automatically grab the first primary key column and then assumes that is the output parameter that it should return which is incorrect in my case, I need it to return the "pti_id" value instead of "pti_part"

The only reason this is a problem is because pti_part is varchar, so the create fails with the following error message:

Here is the raw json response with the debug flag set:

{"draw":null,"data":[],"recordsTotal":null,"recordsFiltered":null,"error":"Conversion failed when converting the nvarchar value 'T003438' to data type int.","fieldErrors":[],"id":null,"meta":{},"options":{},"searchPanes":{"options":{}},"files":{},"upload":{"id":null},"debug":[{"Query":"DECLARE @T TABLE ( insert_id varchar (10) ); INSERT INTO  [part_ingredients]  ( [pti_lastmodified], [pti_modifiedby_userid], [pti_part], [pti_site], [pti_part_version], [pti_ingredient_name], [pti_part_id], [pti_amount_percent] ) OUTPUT INSERTED.pti_part as insert_id INTO @T VALUES (  @pti_lastmodified,  @pti_modifiedby_userid,  @pti_part,  @pti_site,  @pti_part_version,  @pti_ingredient_name,  @pti_part_id,  @pti_amount_percent ); SELECT insert_id FROM @T","Bindings":[{"Name":"@pti_lastmodified","Value":"2021-02-17T14:28:59.517862+13:00","Type":null},{"Name":"@pti_modifiedby_userid","Value":443,"Type":null},{"Name":"@pti_part","Value":"T003438","Type":null},{"Name":"@pti_site","Value":200,"Type":null},{"Name":"@pti_part_version","Value":"0.01","Type":null},{"Name":"@pti_ingredient_name","Value":"ff","Type":null},{"Name":"@pti_part_id","Value":51992,"Type":null},{"Name":"@pti_amount_percent","Value":"0.33","Type":null}]},{"Query":"SELECT  [pti_id] as 'pti_id', [pti_lastmodified] as 'pti_lastmodified', [pti_modifiedby_userid] as 'pti_modifiedby_userid', [pti_part] as 'pti_part', [pti_site] as 'pti_site', [pti_part_version] as 'pti_part_version', [pti_ingredient_name] as 'pti_ingredient_name', [pti_type_id] as 'pti_type_id', [pti_part_id] as 'pti_part_id', [pti_sort_order] as 'pti_sort_order', [pti_amount_percent] as 'pti_amount_percent' FROM  [part_ingredients] WHERE [pti_id] = @where_0 ","Bindings":[{"Name":"@where_0","Value":"T003438","Type":null}]}],"cancelled":[]}

How can I modify the code so that I can either explicitly tell editor which field to return after creating the record, or just have it not return anything at all after the insert? (I can just do a full table refresh afterwards, its not a big problem pulling everything again)
Or maybe there is some other way to actually handle this.

As I said, unfortunately I cannot modify the database table structure to include the auto inc field in the primary key so I need to find some other work around.

allan

Hi,

This isn't a situation I'd considered before, so as you are seeing I'm afraid it isn't working out of the box and I don't have an immediate fix for you.

This is the code where we attempt to get the primary key from the database.

We might need to introduce a way to override that to make this situation work. I don't even have a workaround at the moment - I'd wondered about changing the order of the columns, but I doubt that would work...

Sorry I don't have better news at this time.

Allan

riaanvdl

Thanks @allan , I will try to come up with some workaround.

If I have to end up cloning the repo and directly modify the code that you showed me, how does that affect my licence for using this? Will everything still work fine if I clone the repo, make the change compile the solution myself?

allan

No not at all. The .NET library for Editor is actually open source under the MIT license. Only the client-side Editor library carries a commercial license.

Regards,
Allan