dreaded "s data match" and casting numbers as varchar

rldean1

So, after a row is edited, I send data back to Editor from SQL. It's usually a JSON object with all the key/value pairs (the field names) for the one (1) specific row that is being edited. I don't send back the DT_RowId --- Editor just kinda knows

NOW.... sometimes I send back integers, or dates configured in a specific manner. I pretty much have to make sure it matches the same datatypes that I use to populate DataTables in the first place...

I have found that if it is an int datatype, it's missing quotes in the JSON {"JobFairID": 42} vs. {"JobFairID": "42"}, and Editor doesn't like that.

QUESTION: Why do I have to cast my datatypes as varchar in SQL to get quotes around them? I have to do stuff similar to this for both Editor and DataTables (see below -- if you don't cast those columns as a varchar, you get the dreaded "s data match" error).

I'm not complaining -- I'm just wondering if there is something wrong with my approach.

            select @jsonResponse = (
                select
                    cast(JobFairID as varchar) as JobFairID
                   ,cast(Schedule as varchar) as Schedule
                   ,cast(Slot as varchar) as Slot
                   ,[Period]
                   ,cast(AllDay as varchar) as AllDay
                   ,cast(PMOnly as varchar) as PMOnly
                   ,cast(AMOnly as varchar) as AMOnly
                from @tmpInterviewTimes
                for json path, root('data'), INCLUDE_NULL_VALUES
            )

allan

I don't send back the DT_RowId --- Editor just kinda knows

Hum... Are you using idSrc perhaps? If not, and you aren't using DT_RowId then it can't work...

Editor on the client-side shouldn't care about number or string - so this must be a server-side issue. Is it happening when you submit a form or when you load the data? If you can link to a test case, that would be useful.

Allan

rldean1

Yes, I use both idSrc and rowId.

Kinda hard to nail up an example for this one...

I have observed in several cases, for both DT and Editor, that if I don't have quotes around integers, date stamps, and bits, I am likely to get the "s data match is not a function" issue. The only way to get quotes around the values is to cast those datatypes as a varchar.

Just an observation --- next time I build a web app, I will test this observation again to make sure I'm not lying to you.