Fields linked via leftjoin are all required

guidols

Hi,

I have a problem similar as the one described here. As the conversation has been closed, I open a new one.

I'm using the editor to manage linked entities, like:

Both Status and Project are linked tables, like (in C#):

.LeftJoin("Statuses", "Statuses.Id", "=", "Main.Status_Id")
.LeftJoin("Projects", "Projects.Id", "=", "Main.Project_Id")

Everything works fine as soon as in creation (or in editing) both fields are filled. Is not possible to fill only one on these fields.

If only one filed is filled, then I get the following error: Conversion failed when converting from a character string to uniqueidentifier.

This can be seen for example in inline editing when I try to change only status:

editor.on( 'initEdit', function ( e, node, data, items, type ) {
  console.log( 'initEdit', JSON.stringify( data ) );
} );
editor.on( 'preSubmit', function ( e, data, action ) {
  console.log( 'preSubmit', JSON.stringify( data ) );
} );

Result is:

preSubmit {"data":{"row_1":{"Main":{"Status_Id":"ad4593bb-045f-455e-a1fe-01fd1a922468","Project_Id":null}}},"action":"edit"}

As printed in debug, Project_Id is null and this cause a problem.

If I try to modify a line where both status and project are already filled, everything works.

Can you please tell me how to set a field as not required?

Or what is the easiest solution to fix this?

Thanks!

allan

Hi,

Are you using our .NET libraries to do the server-side data handling? If so, could you add .Debug(true) just before the .Process(...) call, and then show me the JSON return from the server when this error occurs? It will include the SQL that is being generated and executed.

One possible workaround might be to use PreEdit and PreCreate on the server-side to detect when a null value is sent for either of those two fields, and if so, then set the field to not write to the database (editor.Field('name').Set(false);) - although that would cause an issue if you went from it having a value to it being null.

Allan

guidols

Yes, I'm using your libraries.

If I have a row without a Project set, and try to modify the Status, this is the generated query:

UPDATE [Main] SET [TaskStatus_Id] = @TaskStatus_Id, [Project_Id] = @Project_Id WHERE [Main].[Id] = @where_0

So basically the library is trying to update both fields.

So, should I implement PreEdit and PreCreate server-side? Need I to implement both?

Another question at this point is: how can I "add" an empty option to a chosen field?

Using chosen (but also using the select fields) is impossible to delete an existing entry, it's only possible to change it.

allan

So, should I implement PreEdit and PreCreate server-side? Need I to implement both?

Yes, both needed as assuming this can happen at both edit and create time.

Something like:

            editor.PreCreate += (sender, e) => {
                if (e.Values["Project_Id"] == null) {
                    editor.Field("Project_Id").Set(false);
                }
             };

However, as I mentioned, I'm a little concerned about that since you wouldn't be able to remove a value once it has been set.

What we perhaps need to understand better here is why the Project_Id can't be set to null? Should that be valid in the schema?

Another question at this point is: how can I "add" an empty option to a chosen field?

With the select input you can use the placeholder, placeholderValue and placeholderDisabled options to enable an empty value and assign a specific value (typically null or an empty string) to it.

With Chosen, looking at their documentation I don't see anything similar unfortunately. It looks like the list of options fed into it would need to include the empty value to be selectable.

Allan

guidols

What we perhaps need to understand better here is why the Project_Id can't be set to null? Should that be valid in the schema?

That's strange, as the fields are declared like that (for example):
public Guid? Project_Id { get; set; }

guidols

I’m implemented PreCreate and this solves the problem.

The fact is that now, as you correctly said, is not possible anymore to delete a value.

Why the Guid? fields (declared in my model) are required by datatables? Should I declare somewhere that these fields are not required?

guidols

Please note that with DateTime the behaviour is different; i.e., when trying to insert a null (or empty value):

if the field is DateTime --> SQL error, can't insert NULL
if the field is DateTime? --> OK

With Guid the behaviour is different, and also is the error: is not something related to inserting a NULL value, but:

Conversion failed when converting from a character string to uniqueidentifier

allan

Ah - your model uses a DateTime? There isn't a direct representation of a DateTime in JSON, so it would need to become a string as noted in the docs here. I think that would apply to a guid as well since in practice it is just a 16 byte value that we tend to view with base16.

Could you show me the SQL schema for your table that is giving the uniqueidentifier error please?

Thanks,
Allan

guidols

Sure, here is the schema:

CREATE TABLE [dbo].[Main](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Target] [int] NULL,
[Date] [datetime] NULL,
[Project_Id] [uniqueidentifier] NULL,
[Status_Id] [uniqueidentifier] NULL

Seems also to me that the library:
- Can convert strings to Datetime, even if a string is empty.
- Can't convert a "NULL" string to a NULL guid, as the error is:

Conversion failed when converting from a character string to uniqueidentifier.

I see two problems here:
- If I have, in the editor, a line with an empty project and if I modify, for instance, the target value; then also the "empty" project_id is sent to the server, ad described in my first comment.
- This would not be a big issue, if the library would be able to convert the "NULL" to an empty guid. The problem is that it can't.

Any possibility to fix this without the PreCreate workaround?

Thanks!