Editor - .net table with schema

montoyam

I see a post from 2019 where it was discovered that the .net Editor libraries don't work schemas on Insert.

https://datatables.net/forums/discussion/comment/147159/#Comment_147159

I am getting this error. Is this still an issue with Editor?

allan

Could you show me your C# code and the error message please? I thought we'd fixed that... Possibly not...

Allan

montoyam

here is the error:

Cannot retrieve inserted id - no primary key was found.

here is the controller

    public class RequestHeaderController : ApiController
    {
        [HttpGet]
        [HttpPost]
        public IHttpActionResult RequestHeader()
        {
            var request = HttpContext.Current.Request;
            var settings = System.Configuration.ConfigurationManager.ConnectionStrings["msSqlDW"];
            using (var db = new Database("sqlserver", settings.ConnectionString))
            {
                var response = new Editor(db, "UserRequests.RequestHeader as RequestHeader", "RequestID")
                    .Model<RequestHeaderModel>("RequestHeader")
                    .Field(new Field("RequestHeader.RequestID").Set(false))
                    .Field(new Field("RequestHeader.DepartmentID")
                        .SetFormatter(Format.NullEmpty())
                        .Validator(Validation.NotEmpty())
                        .Validator(Validation.Numeric())
                        .Options(new Options()
                                    .Table("UserRequests.Departments")
                                    .Value("DepartmentID")
                                    .Label("DepartmentName")
                        )
                    )
                    .Field(new Field("RequestHeader.ActionID")
                        .Options(() => new List<Dictionary<string, object>>{
                            new Dictionary<string, object>{ {"value", "1"}, {"label", "Add User"} },
                            new Dictionary<string, object>{ {"value", "2"}, {"label", "Remove User Access"} },
                            new Dictionary<string, object>{ {"value", "3"}, {"label", "Change Responsibilities"} }
                        })
                    )
                    .Field(new Field("RequestHeader.Comment").Xss(false))
                    .Field(new Field("RequestHeader.EndDate")
                        .SetFormatter(Format.NullEmpty())
                        .GetFormatter(Format.DateSqlToFormat("MM/dd/yyyy"))
                    )
                    .Field(new Field("RequestHeader.RequestedDate")
                        .SetFormatter(Format.NullEmpty())
                        .GetFormatter(Format.DateSqlToFormat("MM/dd/yyyy"))
                    )
                    .Field(new Field("RequestHeader.EffectiveDate")
                        .SetFormatter(Format.NullEmpty())
                        .Validator(Validation.NotEmpty())
                        .GetFormatter(Format.DateSqlToFormat("MM/dd/yyyy"))
                    )
                    .Field(new Field("RequestHeader.UserID")
                        .SetFormatter(Format.NullEmpty())
                        .Validator(Validation.NotEmpty())
                    )
                    .LeftJoin("UserRequests.Departments as Departments", "Departments.DepartmentID", "=", "RequestHeader.DepartmentID")
                        .Field(new Field("Departments.DepartmentName"))
                    .Debug(true)
                    .Process(request)
                    
                    .Data();
                return Json(response);
            }
        }

    }

here is the debug:

[
  {
    "Query": "INSERT INTO  [UserRequests].[RequestHeader]  ( [DepartmentID], [ActionID], [Comment], [EndDate], [RequestedDate], [EffectiveDate], [UserID], [RequestedBy], [ReplacedUserID] ) VALUES (  @DepartmentID,  @ActionID,  @Comment,  @EndDate,  @RequestedDate,  @EffectiveDate,  @UserID,  @RequestedBy,  @ReplacedUserID )",
    "Bindings": [
      {
        "Name": "@DepartmentID",
        "Value": 3,
        "Type": null
      },
      {
        "Name": "@ActionID",
        "Value": 1,
        "Type": null
      },
      {
        "Name": "@Comment",
        "Value": "",
        "Type": null
      },
      {
        "Name": "@EndDate",
        "Value": null,
        "Type": null
      },
      {
        "Name": "@RequestedDate",
        "Value": "2021/11/17",
        "Type": null
      },
      {
        "Name": "@EffectiveDate",
        "Value": "2021/11/17",
        "Type": null
      },
      {
        "Name": "@UserID",
        "Value": "mememe",
        "Type": null
      },
      {
        "Name": "@RequestedBy",
        "Value": "MONTOYAM",
        "Type": null
      },
      {
        "Name": "@ReplacedUserID",
        "Value": "",
        "Type": null
      }
    ]
  }
]

the sql statement looks fine from what I can tell. In the meantime I have created a stored procedure and passed the data from the editor to the stored procedure. doing it that way the record is able to be created.

    insert into UserRequests.RequestHeader
        (DepartmentID, RequestedBy, RequestedDate, ActionID
         , EffectiveDate, EndDate, UserID, ReplacedUserID, Comment)
    values
        (@DepartmentID, @RequestedBy, @RequestedDate, @ActionID
         , @EffectiveDate, nullif(@EndDate,''), @UserID, nullif(@ReplacedUserID,''), nullif(@Comment,''))

allan

Hi,

Sorry for the delay in replying here! Could you run the following against your database and let me know the result please?

                    SELECT
                        KCU.table_name as table_name,
                        KCU.column_name as column_name,
                        C.DATA_TYPE as data_type,
                        C.CHARACTER_MAXIMUM_LENGTH as data_length
                    FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS AS TC
                    INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS KCU ON
                        TC.CONSTRAINT_TYPE = 'PRIMARY KEY' AND
                        TC.CONSTRAINT_NAME = KCU.CONSTRAINT_NAME AND 
                        KCU.TABLE_SCHEMA = 'UserRequests' AND
                        KCU.TABLE_NAME = 'RequestHeader'
                    JOIN
                        INFORMATION_SCHEMA.COLUMNS as C ON
                            C.table_name = KCU.table_name AND
                            C.column_name = KCU.column_name
                    ORDER BY KCU.TABLE_NAME, KCU.ORDINAL_POSITION

Thanks,
Allan

montoyam

table_name  column_name data_type   data_length
RequestHeader   RequestID   int NULL

allan

Thank you. Also, what version of the Editor .NET libraries are you using?

What is confusing me here is that the debug you show above should contain "OUTPUT INSERTED" which it isn't.

This is the block of code in question. When that returns a value (which it appears to be doing correctly from the SQL output above) then it will wrap the INSERT commend with extra information, which is then logged. That wrapping isn't happening for some reason...

Allan

montoyam

sorry, we were off for Thanksgiving holiday.

It looks like I am using version 2.0.4.0

I believe when I first posted I was using 1.9, but when I realized I wasn't using the newest I upgraded. But i am still getting the same sql statement in the debug.