Altering data that has been returned via Ajax

elboffor

Hi,

Just wondering if someone could assist me here.
I'm currently building my table from a JSON that is constructed in VBScript (a lot of customer HTML code in spans for each cell) and there are certain characters that don't play nice.

I'll give you an example.
the server doesn't like the ' character for obvious reasons (SQL Injection) but having them in the returned data is required, for example if someone's name is O'Malley then that's how it should be displayed.

The way I've gotten around this previously is by searching for ' and replacing with "an-apostrophe" and then when populating the html table I would replace "an-apostrophe" with ' , draw the DataTable based on the html and all was well.

As I'm now rendering the table based off Ajax data I now have cells that say "Oan-apostropheMalley" for example.

the way im writing my table is as follows:

                        $("#table9").DataTable({
                            "sScrollX": "90%",
                            "sScrollXInner": "100%",
                            "ajax": "Ajax.asp?RT=weeklyData",
                            "order": [[ 0, "desc" ]]
                        });

ideally something like

ajax.replace(/an-apostrophe/g, "'"); 

would be ideal, but I can't find the relevant datatabkes equivalent. I assume I need to break up the ajax call to something like

                        $("#table9").DataTable({
                            "sScrollX": "90%",
                            "sScrollXInner": "100%",
                            "ajax": {
                                url: "Ajax.asp?RT=weeklyData",
                                replace: (/an-apostrophe/g, "'")}
                            "order": [[ 0, "desc" ]]
                        });

but can't seem to find the right syntax to do it.

Any help would be greatly appreciated

Thanks

elboffor

elboffor

I'm now finding that " is altering the JSON and making it invalid so I'm having to replace that with the phrase "quotation-mark" when writing the JSON.
Looks like I need the ability to change that back also {{facepalms}}
I highly doubt I am the first person to come across this so no doubt the solution exists, but googling and searching this site brings up a lot of references to altering the dataSrc, in itself this would be fine but when DataTables tried to render the form it'll cause the same issues (invalid JSON) as it will add a " in the middle of a string.

It must be something like aRender but I just cant find the correct syntax :(
(tried several things and still no dice)
Don't think my client will like seeing phrases such as an-apostrophe and quotation-mark all over the place though lol

glenderson

I'll provide you two suggestions that I use, there are many other ways to address it.

1) Fix your database - Having an apostrophe is very normal, to insert into the database you merely need to escape it with another apostrophe ' becomes ''. I do this on all SQL text fields because I use adhoc instead of parameterization of the SQL string. When rendering a string into HTML, perform a similar escape method. I have two function showtextfield() and fixtextfield() that handle it all for myself.

2) Since you are an asp. page, return the data already formatted in the format you would like. That is, return json data format in .asp use response.write( ... ) where the ... would be the json formatted data. That way you can perform a replace using .asp code instead of javaScript.

elboffor

thanks for the reply glenderson.
While escaping the apostrophe with an additional one would work well it's basically the same principle (you change to '' and change back and I change to an-apostrophe and change back).

Function fromDB(value)
if not IsNull(value) then
    fromDB=replace(value, "an-apostrophe","'")
else
    fromDB=""
end if
End Function
Function toDB(value)
    toDB=replace(value,"'", "an-apostrophe")
End Function

In relation to returning the data however, I have multiple DataTables on one page which pulls data from a ridiculously large database. If I were to create the table in html on load, the page load time would be rather silly.

This is why I use JSON, as the data can be retrieved as and when it's needed and it returns in a format I can use.

But if I was to use a quotation mark ( " ) then it makes the JSON invalid, as such I need to be able to change the data that is written to the DOM once DataTables has handled the data.
This would also be useful for other scripts I plan to run on the page.