Leaving data alone

dhutton@creativeone.com

I'm sure this is out there somewhere I'm just not using the right search criteria. If we input something like January 21-23 into a text field (using latest DT & Editor) it converts that to a date. Is there a universal way to tell DT / Editor to treat the data as verbatim and not convert to a date (or an int ... etc.)?

colin

If you set fields.type to be text, that should do the trick.

See example here for setting those types.

Colin

dhutton@creativeone.com

Does that work for you? It doesn't for me in my set up. Text is the default and I tried explicitly defining the type as textarea too. Same behavior. It takes January 21-23, converts it to one day's date, deletes the original content and replaces it with the conversion. So I can't even tell what the originator put in to change it back.

I haven't ruled out doing something wrong my end but this looks like a bug - the code is changing data types on me. Is there maybe another way I can keep the conversion from happening?

Preconditions

1. An editable text field (or textarea) within a datatable
2. Latest / greatest of editor and datatable
3. Using inline edit (editor.inline(this, { onBlur: 'submit' });)

Expected results
String is saved as-is

Actual results
String is converted and deleted, converted date is new value

Steps to reproduce
1. Type January 21-23 into a text field and submit (dialog or inline)
2. Observe

Work around
None

kthorngren

String is converted and deleted, converted date is new value

Is the conversion stored as a date in the DB?

Looking at the Developer Tools what is the XHR response when editing the field?

It takes January 21-23, converts it to one day's date, deletes the original content and replaces it with the conversion.

What does this actually look like?

Although this isn't saving to the server this example doesn't make any conversions:
http://live.datatables.net/wozozugu/1/edit

Kevin

dhutton@creativeone.com

I am posting through the server / controller so maybe the conversion is happening in there. I don't see any custom code that would be causing this, the set up is pretty simple compared to our other implementations (and all of our implementations are doing it). Anywhere I have a text field and type that in, it's converted into a date and written to the db that way.

You may need to help me with the XHR you're trying to get

kthorngren

The diagnosis section of this technote will walk you through the steps:
https://datatables.net/manual/tech-notes/1

You can see the request with the January 21-23 field and the response from the server. That should help determine where to look next. Let us know what you find.

Kevin

allan

Also could you upload a trace for me using the debugger. Click the Upload button and then let me know what the debug code is.

What is the server-side code you are using?

Allan

dhutton@creativeone.com

ok thanks for the link on XHR

So I clear my console, go to network / xhr and then type in January 21-23 and hit enter to submit. I can see in the response that the date is already there, so I'm assuming that means it's happening in the js first before it gets to the controller.

Here's the controller code:

[HttpPost]
[HttpGet]
public IActionResult getAllFaqsByEvent(long eventId)
{
var dbType = Globals.dbType;
var dbConnection = Globals.dbConnection;
using (var db = new Database(dbType, dbConnection))
{
DtResponse response = new Editor(db, "c1events_faqs", "id")
.Model<C1FaqsModel>()
.Where("eventid", eventId)
.TryCatch(false)
.Process(Request)
.Data();
return Json(response);
}
}

dhutton@creativeone.com

Allan I clicked upload, it left me a shortcode but when I go there I got a 404. I tried it again and hit upload and still 404. When I tried a variation the 3rd time it said only one request per minute. So maybe I didn't do something right.

kthorngren

I can see in the response that the date is already there, so I'm assuming that means it's happening in the js first before it gets to the controller.

The Request is what you want to look at to see what is sent by the client. In Chrome that would be in the Headers (scroll to the bottom). The Response is the data back from your server.

Kevin

dhutton@creativeone.com

oh ok. So when I clear my console, input January 21-23 and hit enter, this is what my headers say:

action=edit&data%5Brow_75%5D%5Banswer%5D=January+21-23&data%5Brow_75%5D%5Bdatemodified%5D=Thu+Jan+09+2020+10%3A18%3A26+GMT-0600+(Central+Standard+Time)

so I think that's telling me the problem isn't on the client side?

Although the data starts out already converted. So I'll be more sequential as to what I'm seeing:
1) Data says this in the field: 2023-01-21 00:00:00
2) I edit it and replace it with January 21-23
3) Then I see what I sent above in the header / form data

colin

That shortcode is what we need for the debugger. Please could you try that again and post the shortcode here. You'll get the 404 as it's protected so only staff are able to access it.

Colin

dhutton@creativeone.com

Gotcha, I saved it just in case that's how it works:
https://debug.datatables.net/uzawiw

allan

so I think that's telling me the problem isn't on the client side?

I'd agree with that. It is submitting January 21-23 as a value, so any conversion is happening at the server-side.

Can you show me also C1FaqsModel? Have you got the answer property as just a string? Also, what is the data format in the database - is it plain text?

Allan

dhutton@creativeone.com

Here's the C1FaqsModel

    public class C1FaqsModel
    {
        public string faqid { get; set; }
        public string answer { get; set; }
        public string dateadded { get; set; }
        public string datemodified { get; set; }
        public long eventid { get; set; }
        [Key]
        public int id { get; set; }
        public string question { get; set; }
    }

allan

This is just weird!

Immediately before .Process(Request) can you add .Debug(true). Then reload your page and use the debugger to give me a trace please - click the Upload button and then let me know what the debug code is.

Thanks,
Allan

dhutton@creativeone.com

Hey Allan, sorry for the delay. Here's my debug:
https://debug.datatables.net/osawac

allan

Thanks! I see:

"answer": "2023-01-21 00:00:00",

In the response, so no question that something is converting it into ISO8601, but I'm not yet sure what!

A little more information I need to request I'm afraid. If you have the network inspector open, then submit the edit request that results in this error, can you show me the:

• JSON response from the server for the request
• The headers and parameters being sent (similar to what is above, but want to just have matching data so it all lines up).

Thanks,
Allan

dhutton@creativeone.com

Here's the response and the headers info below that

{
    "draw": null,
    "data": [
        {
            "DT_RowId": "row_75",
            "faqid": "d5a54db9-a3bd-4635-849b-5df34fde9d6f",
            "answer": "2023-01-21 00:00:00",
            "dateadded": "Mon Jan 06 2020 10:52:45 GMT-0600 (Central Standard Time)",
            "datemodified": "Wed Jan 29 2020 15:49:31 GMT-0600 (Central Standard Time)",
            "eventid": 9876,
            "id": 75,
            "question": "Date Test"
        }
    ],
    "recordsTotal": null,
    "recordsFiltered": null,
    "error": null,
    "fieldErrors": [],
    "id": null,
    "meta": {},
    "options": {},
    "files": {},
    "upload": {
        "id": null
    },
    "debug": [
        {
            "query": "UPDATE  c1events_faqs SET  answer = @answer, datemodified = @datemodified WHERE id = @where_0 ",
            "bindings": [
                {
                    "name": "@where_0",
                    "value": "75",
                    "type": null
                },
                {
                    "name": "@answer",
                    "value": "January 21-23",
                    "type": null
                },
                {
                    "name": "@datemodified",
                    "value": "Wed Jan 29 2020 15:49:31 GMT-0600 (Central Standard Time)",
                    "type": null
                }
            ]
        },
        {
            "query": "SELECT  id as \"id\", faqid as \"faqid\", answer as \"answer\", dateadded as \"dateadded\", datemodified as \"datemodified\", eventid as \"eventid\", question as \"question\" FROM  c1events_faqs WHERE eventid = @where_0 AND id = @where_1 ",
            "bindings": [
                {
                    "name": "@where_0",
                    "value": 9876,
                    "type": null
                },
                {
                    "name": "@where_1",
                    "value": "75",
                    "type": null
                }
            ]
        }
    ],
    "cancelled": []
}

General
Request URL: https://localhost:44369/api/C1Events/getAllFaqsByEvent/9876
Request Method: POST
Status Code: 200
Remote Address: [::1]:44369
Referrer Policy: no-referrer-when-downgrade

Response Headers
access-control-allow-origin: *
content-type: application/json; charset=utf-8
date: Wed, 29 Jan 2020 21:49:31 GMT
server: Microsoft-IIS/10.0
status: 200
x-powered-by: ASP.NET

Request Headers
:authority: localhost:44369
:method: POST
:path: /api/C1Events/getAllFaqsByEvent/9876
:scheme: https
accept: application/json, text/javascript, /; q=0.01
accept-encoding: gzip, deflate, br
accept-language: en-US,en;q=0.9
content-length: 151
content-type: application/x-www-form-urlencoded; charset=UTF-8
cookie: ***
origin: https://localhost:44369
referer: https://localhost:44369/C1Events/DetailsFaqs/9876
sec-fetch-mode: cors
sec-fetch-site: same-origin
user-agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/79.0.3945.130 Safari/537.36
x-requested-with: XMLHttpRequest

Form Data
action: edit
data[row_75][answer]: January 21-23
data[row_75][datemodified]: Wed Jan 29 2020 15:49:31 GMT-0600 (Central Standard Time)

allan

Excellent - thank you!

So in the SQL statement we have:

SET answer = @answer

where @answer is bound as:

"value": "January 21-23",

So that's good! The value should be in the database. Can you use SQL Studio or similar to look at your data directly in the database and confirm that it is there? I suspect it will be.

What is not so good is the JSON response:

"answer": "2023-01-21 00:00:00",

That suggests to me, that somewhere between reading the value from the db (assuming it was written correctly - that will be important to know), and writing it out to the client, it is being converted into a DateTime.

Your model and controller are just as above? You don't have any event handlers or any formatters?

Is it SQL Server you are using? What version of it?

Thanks,
Allan

dhutton@creativeone.com

My database is PostGreSQL. The data looks exactly like what's pictured in the UI's table using PgAdmin (the UI to the db similar to SQL Studio).No event handlers or formatters.

The fact that it's doing this to me in all tables I use throughout my app, it's sounding like it has something to do with DT interaction with postgres since that's the common denominator in this. I've had problems with DT & postgres before since postgres does things like wraps mixed case table and column names / data in double quotes vs. single.

My data type in my db is text and I can manually add January 21-23 and save it via pgadmin and confirm the value sticks, so it's something to do with how DT is reading that data from postgres and converting it to a date for some reason (or I guess reading the submitted data and converting it and writing it that way). At least that's what it seems to be. If I'm correct any dt/editor set up to point at a postgres db will reproduce.

allan

The data looks exactly like what's pictured in the UI's table using PgAdmin

Do you mean your image above from 17th Jan? The screenshot there doesn't show the "January 21-23" data in the answer column (or an ISO date - I think that row has just been clipped) so I'm not clear if the db has January 21-23 in it or an ISO date?

edit I'm trying to clarify if the problem is the data being written to the db, or being read from the db. The SQL debug above shows that Editor is writing the correct string to the SQL statement, so one possibility is that the database driver is converting the string to a date. Another is that it is doing it on read. Or something else is going wrong on read.

Thanks,
Allan

dhutton@creativeone.com

On the first question, I mean I see the same result looking at the data via pgAdmin that I do looking at the finished data in the datatable.

I know the db is capable of just holding January 21-23 as a string because I've done it via pgAdmin. So I don't think the problem is happening when reading the data from the db I think it's converting before writing it to db. Presumably if I just write January 21-23 as a value to one of my entity models using my own code that I would see that written as-is in my db. If I do it through dt it's being converted. I just can't tell what's doing it.

If it's the postgres driver than anyone running a postgres db should see the same results, at least anyone using postgres and datatables / editor together I guess.

allan

Right - If you are seeing the ISO date when looking at the table with pgAdmin, then there is no question in my mind, that the issue is on writing to the db. Looking at the SQL debug above we can see that the SQL statement is being bound with January 21-23.

I wonder if specifying it as a string type for the db would help (apologies I can't try this right now):

.Field(
  new Field("answer")
    .DbType(DbType.String)
)

just after the .Model() call for that. Could you add that in then try it again, and if it still doesn't work show me the response from the server (the JSON) again?

Thanks,
Allan

dhutton@creativeone.com

It didn't work but I got an interesting exception:

allan

Apologies for the delay in getting back to you on this. I will do so later on today.

Allan