Editor - .net table with schema

Editor - .net table with schema

montoyammontoyam Posts: 568Questions: 136Answers: 5

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?

Answers

  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

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

    Allan

  • montoyammontoyam Posts: 568Questions: 136Answers: 5
    edited November 2021

    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,''))
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    edited November 2021

    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

  • montoyammontoyam Posts: 568Questions: 136Answers: 5
    table_name  column_name data_type   data_length
    RequestHeader   RequestID   int NULL
    
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin

    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

  • montoyammontoyam Posts: 568Questions: 136Answers: 5

    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.

Sign In or Register to comment.