dreaded "s data match" and casting numbers as varchar

dreaded "s data match" and casting numbers as varchar

raydlevel5raydlevel5 Posts: 112Questions: 49Answers: 1

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 = (
                    cast(JobFairID as varchar) as JobFairID
                   ,cast(Schedule as varchar) as Schedule
                   ,cast(Slot as varchar) as Slot
                   ,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


  • allanallan Posts: 51,411Questions: 1Answers: 7,752 Site admin

    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.


  • raydlevel5raydlevel5 Posts: 112Questions: 49Answers: 1

    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.

Sign In or Register to comment.