Populating a Json/Ajax Datatable with an array

Ok so now that I can populate a datatable with a sql query, how can i do it with the contents of a 2-dimensional arry?
Bob SchneiderCo-OwnerAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
can you give an example of what your array looks like?
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
if this is related to your other questions, I thought all of your data was coming from the database, is that not the case?
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
you may want to have a look at the link below for examples on how to load the datatable with just an array:

https://datatables.net/examples/ajax/simple.html

once you have the array built, you will need to write out out line by line (since it's coming from an ajax call in the following format:

{
  "data": [
    [
      "Tiger Nixon",
      "System Architect",
      "Edinburgh",
      "5421",
      "2011/04/25",
      "$320,800"
    ],
    [
      "Garrett Winters",
      "Accountant",
      "Tokyo",
      "8422",
      "2011/07/25",
      "$170,750"
    ],
    [
      "Ashton Cox",
      "Junior Technical Author",
      "San Francisco",
      "1562",
      "2009/01/12",
      "$86,000"
    ],
    [
      "Cedric Kelly",
      "Senior Javascript Developer",
      "Edinburgh",
      "6224",
      "2012/03/29",
      "$433,060"
    ]
]
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Bob SchneiderCo-OwnerAuthor Commented:
It is related to my other post, yes.  The db gurus were trying to refine my query so I did not have to use an array.  So far no luck and I want to get it out there so I will need to use an array approach until we get there.  I will look at your posts and come back to you  if I have other questions.

Thanks so much!!
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
If the data IS coming from the database (opposed to being hard-coded), you do not need to use an array, as you're using the QueryToJSON function that writes out the data in the proper format for the data table. whatever your query is should not affect how you're getting the data out to the table, whether it's pure json or array based.

I guess I'm a bit unclear on why you want to move over to arrays when you already have a working solution.
0
Bob SchneiderCo-OwnerAuthor Commented:
Ok I feel good about the way I have it built and it is not quite working, although I think it is close.  Lets start with this:

 My array shows what I want it to.  Here is how I am trying to display it in an asp page:

{
  "data": [
        <%For i = 0 To UBound(Results, 2) - 1%>
            [
            <%For j = 0 To iArrDim1%>
                "<%=Results(j, i)%>",
            <%Next%>
            ],
        <%Next%>
]
}

Open in new window


Any issues?  It doesn't have to be a text file does it?
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
No it doesn't

If you do a view source what does the output look like?
0
Bob SchneiderCo-OwnerAuthor Commented:
Not sure what I am looking for but here is the page itself: http://www.gopherstateevents.com/series/series_results3.asp?series_id=19
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
We got sidetracked to a different method on your last question.  This is what I showed you before http://www.experts-exchange.com/Programming/Languages/Scripting/AJAX/Q_28479115.html#a40207820

The key is we need to generate the json by hand and we need to escape some characters which is why it is a bit drawn out. But it works.
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
again, if this data is coming from the database, why turn it into an array when you already had a solution in place. You still haven't answered that for me :)

the reason why it's not working is because your format is invalid. I stuck your json into an online json validator and it gave me errors/ Each "section" of your data has an extra comma at the end:

{
    "data": [
        [
            "62816",
            "Birr, Loren",
            "42",
            "M",
            "100",
            "100",
            "0",            
        ],
        [
            "62812",
            "Seiler, Alex",
            "12",
            "M",
            "96.67",
            "96.67",
            "0",            
        ],

Open in new window


you need to change your asp code to:

{
  "data": [
        <%For i = 0 To UBound(Results, 2) - 1%>
            [
            <%For j = 0 To iArrDim1%>
                "<%=Results(j, i)%>"
            <%
            if j < iArrDim1 then 
                    Response.Write ","       '-- don't output a comma on the last element
                  end if
                Next%>
            ],
        <%Next%>
]
}

Open in new window


again, I would leave the solution in place that used the QueryToJSON function, that way you don't have to worry about format or escaping specific characters. why reinvent the wheel here? :)
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
I have already outlined how to use this method to start with https://datatables.net/examples/ajax/simple.html.   What I outlined included escaping and the trailing comma.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bob SchneiderCo-OwnerAuthor Commented:
For the life of me I can't find the page talking about escapes.  Here is what I have on my asp page.  I really believe I am close and I am certain I am missing something that I have already been told so I apologize:

<%@ Language=VBScript%>
<%
Option Explicit
%>

<!--#include file = "../includes/JSON_2.0.4.asp" -->

<%
Dim conn, rs, sql, rs2, sql2
Dim i, j, k
Dim lSeriesID
Dim iArrDim1
Dim sGender
Dim iYear, iAgeTo, iAgeFrom, iNumFin
Dim Results, Races(), SortArr(14), RsltsArr()

Server.ScriptTimeout = 1200

lSeriesID = Request.QueryString("series_id")
iYear = Request.QueryString("year")
sGender = Request.QueryString("gender")
iAgeTo = Request.QueryString("age_to")

lSeriesID = "19"
iYear = "2014"
sGender = "M"
iAgeTo = "0"

If CStr(iAgeTo) = vbNullString Then 
    iAgeTo = 0
Else
    If CInt(iAgeTo) = 14 Then
        iAgeFrom = "0"
    Else
        iAgeFrom = CInt(iAgeTo) - 4
    End If
End If

Response.Buffer = True		'Turn buffering on
Response.Expires = -1		'Page expires immediately
				
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "Provider=SQLNCLI10;Server=localhost\SQLExpress;Database=xxx;Uid=xxx;Pwd=xxx;"

j = 0
ReDim Races(1, j)
Set rs = Server.CreateObject("ADODB.Recordset")
sql = "SELECT sr.RaceID, se.EventName, sr.RaceName FROM SeriesRaces sr INNER JOIN SeriesEvents se ON sr.SeriesEventsID = se.SeriesEventsID "
sql = sql & "WHERE se.SeriesID = " & lSeriesID & " AND se.EventDate < '" & Date & "' ORDER BY se.EventDate"
rs.Open sql, conn, 1, 2
Do While Not rs.EOF
    Races(0, j) = rs(0).Value
    Races(1, j) = Replace(rs(2).Value, "''", "'") & "<br>" & Replace(rs(1).Value, "''", "'")
    j = j + 1
    ReDim Preserve Races(1, j)
    rs.MoveNext
Loop
rs.Close
Set rs = Nothing

iArrDim1 = UBound(Races, 2) + 4

i = 0
ReDim Results(iArrDim1, 0)
Set rs = Server.CreateObject("ADODB.Recordset")
If iAgeTo = 0 Then
    sql = "SELECT ParticipantID, PartName, Age, Gender "
    sql = sql & " FROM SeriesParts WHERE SeriesID = " & lSeriesID & " AND Gender = '" & sGender & "' ORDER BY PartName"
Else
    sql = "SELECT ParticipantID, PartName, Age, Gender "
    sql = sql & "FROM SeriesParts WHERE SeriesID = " & lSeriesID & " AND Gender = '" & sGender & "' AND Age >= "  & iAgeFrom & " AND Age <= " & iAgeTo & " ORDER BY PartName"
End If
rs.Open sql, conn, 1, 2
Do While Not rs.EOF
    Results(0, i) = rs(0).Value
    Results(1, i) = Replace(rs(1).Value, "''", "'")
    Results(2, i) = rs(2).Value
    Results(3, i) = rs(3).Value

    For j = 4 To iArrDim1
        Results(j, i) = "0"
    Next

    i = i + 1
    ReDim Preserve Results(iArrDim1, i)
    rs.MoveNext
Loop
rs.Close
Set rs = Nothing

For i = 0 To UBound(Results, 2) - 1
    For j = 5 To iArrDim1 - 1
        Results(j, i) = GetMyPts(Races(0, j - 5), Results(0, i))
    Next
Next

For i = 0 To UBound(Results, 2) - 1
    For j = 5 To iArrDim1 - 1
        Results(4, i) = CSng(Results(4, i)) + CSng(Results(j, i))
    Next
Next

'sort the array
For i = 0 To UBound(Results, 2) - 2
    For j = i + 1 To UBound(Results, 2) - 1
       If CSng(Results(4, i)) < CSng(Results(4, j)) Then
            For k = 0 To iArrDim1
                SortArr(k) = Results(k, i)
                Results(k, i) = Results(k, j)
                Results(k, j) = SortArr(k)
            Next
        End If
    Next
Next

Private Function GetMyPts(lThisRaceID, lThisPartID)
    Dim iMyPl
    Dim bInRace
    Dim iNumFin

    iNumFin = 0
    GetMyPts = 0
    iMyPl = 0
    bINRace = False

    Set rs = Server.CreateObject("ADODB.Recordset")
    If CInt(iAgeTo) = 0 Then   'get open standings
        sql = "SELECT ir.ParticipantID FROM IndResults ir INNER JOIN Participant p ON ir.ParticipantID = p.ParticipantID WHERE ir.RaceID = " 
        sql = sql & lThisRaceID & " AND p.Gender = '" & sGender & "' AND ir.FnlTime IS NOT NULL AND ir.FnlTime <> '00:00:00.000' ORDER BY ir.EventPl" 
    Else
        sql = "SELECT ir.ParticipantID FROM IndResults ir INNER JOIN Participant p ON ir.ParticipantID = p.ParticipantID "
        sql = sql & "INNER JOIN PartRace pr ON ir.RaceID = pr.RaceID WHERE pr.RaceID = " & lThisRaceID & " AND ir.RaceID = " 
        sql = sql & lThisRaceID & " AND ir.FnlTime IS NOT NULL AND ir.FnlTime <> '00:00:00.000' AND p.Gender = '" & sGender & "' AND pr.Age >= " 
        sql = sql & iAgeFrom & " AND pr.Age <= " & iAgeTo & " ORDER BY ir.EventPl"
    End If

    rs.Open sql, conn, 1, 2
    If rs.RecordCount > 0 Then iNumFin = rs.RecordCount
    Do While Not rs.EOF
        iMyPl = CInt(iMyPl) + 1
        If CLng(lThisPartID) = CLng(rs(0).Value) Then 
            bInRace = True
            Exit Do
        End If
        rs.MoveNext
    Loop
    rs.Close
    Set rs = Nothing

    If CInt(iNumFin) > 0 Then 
        If bInRace = True Then GetMyPts = Round(((CInt(iNumFin) - CInt(iMyPl) + 1)/CInt(iNumFin))*100, 2)
    End If
End Function

conn.Close
Set conn = Nothing

Response.Write tojson(Results)
%>
<!--
{
  "data": [
        <%For i = 0 To UBound(Results, 2) - 1%>
            [
            <%For j = 0 To iArrDim1%>
                "<%=Results(j, i)%>",
            <%Next%>
            ],
        <%Next%>
]
}
-->

Open in new window

0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
you still have a trailing comma at the end of each section of data. change
<!--
{
  "data": [
        <%For i = 0 To UBound(Results, 2) - 1%>
            [
            <%For j = 0 To iArrDim1%>
                "<%=Results(j, i)%>",
            <%Next%>
            ],
        <%Next%>
]
}
-->

Open in new window


to

{
  "data": [
        <%For i = 0 To UBound(Results, 2) - 1%>
            [
            <%For j = 0 To iArrDim1%>
                "<%=Results(j, i)%>"
            <%
                  if j < iArrDim1 then 
                    Response.Write ","       '-- don't output a comma on the last element
                  end if
                Next%>
            ],
        <%Next%>
]
}

Open in new window

0
Bob SchneiderCo-OwnerAuthor Commented:
ok did that...still didn't work so I went to JSONLint and here is what it told me:

Parse error on line 1:
<!DOCTYPEHTMLPUBLIC"
^
Expecting '{', '['
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
can you paste here what you put into the validator?
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
This is what I was referring to http://www.experts-exchange.com/Programming/Languages/Scripting/AJAX/Q_28479115.html#a40207820

One think you will find I have done that makes this easier is naming variables inside the for next.
for r = LBound (myArray,2) to UBound(myArray,2)
	artistname 		= myArray(0, r)
	RecordingTitle 	= myArray(1, r)
	TrackTitle 		= myArray(2, r)
	TrackFileName 	= myArray(3, r)

Open in new window


You will also see the end of the for next I have a test for ubound (end of the file) and if not at the end, then add the comma.
 if r < UBound(myArray,2) then
    	response.write ","
    end if


next

Open in new window


That was the code I was referring to that previously.

You will also see I have escaped quotes and slashes.  I don't know this was the best route, but it worked.  You can see I first escaped a single slash with a double slash.  I then used an [x] where there should be a quote in the output.  However, the quote needs to be escaped as well.  So the very last thing is to replace the [x] with a slash and char(34) which is a quote.   If you don't have quotes and slashes in your output, you probably didn't get an error in your json which would have  broke your data tables.
	TrackFileLink = "<a href="&"[x]"&TrackFileUrl&"[x]"&">"&TrackTitle&"</a>"

    theURL = replace("["&chr(34)&artistname&chr(34)&","&chr(34)&RecordingTitle&chr(34)&","&chr(34)&TrackTitle&chr(34)&","&chr(34)&TrackFileLink&chr(34)&"]","\","\\")
    theURL = replace(theURL,"[x]","\"&chr(34))
    response.write theURL

Open in new window

0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
Your json output should be pure json and nothing else.
0
Bob SchneiderCo-OwnerAuthor Commented:
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
you have an extra comma in the last section of your data:

        [
            "63978",
            "Kraemer, Avery",
            "9",
            "M",
            "0",
            "0",
            "0"
        ],    <-- remove extra comma
        
    ]
}

Open in new window


this is why I'm advocating using the aspJSON library, so you don't have to worry about things like this. why re-invent the wheel when it's already been done and proven to work?

if you're adamant going down this route, change your code to:

{
  "data": [
        <%For i = 0 To UBound(Results, 2) - 1%>
            [
            <%For j = 0 To iArrDim1%>
                "<%=Results(j, i)%>"
            <%
                    if j < iArrDim1 then 
                       Response.Write ","       '-- don't output a comma on the last element
                    end if
                Next
               Response.Write "]"

               if j < UBound(Results, 2) - 1 then 
                   Response.Write ","           '-- don't output a comma on the last section
               end if
           Next%>
]
}

Open in new window

0
Bob SchneiderCo-OwnerAuthor Commented:
Ok I did that.  Can you once again show me how to NOT do it that way?

Also, on the JSONLint page for this I still get an error on this line:

<!DOCTYPEHTMLPUBLIC"-//W3C//DTD HTML 4.01//EN""http://www.w3.org/TR/html4/strict.dtd"><html><head><title>GSE(GopherStateEvents)SeriesResults</title><metaname="GENERATOR"Content="Microsoft Visual Studio 6.0"><metahttp-equiv="Content-Type"content="text/html; charset=iso-8859-1"><metaname="postinfo"content="/scripts/postinfo.asp"><metaname="resource"content="document"><metaname="distribution"content="global"><metaname="description"content="GSE race series for road races, nordic ski, showshoe events, mountain bike, duathlon, and cross-country meet management (timing)."><linkrel="icon"href="favicon.ico"type="image/x-icon"><linkrel="shortcut icon"href="favicon.ico"type="image/x-icon"><linkrel="stylesheet"type="text/css"href="../misc/styles.css"><linkrel="stylesheet"type="text/css"href="/drop_menu/menu.css"><linkhref="//cdn.datatables.net/1.10.2/css/jquery.dataTables.css"rel="stylesheet"type="text/css"/><scriptsrc="//code.jquery.com/jquery-1.11.1.min.js"></script><scriptsrc="//cdn.datatables.net/1.10.2/js/jquery.dataTables.min.js"></script><scripttype="text/javascript"src="../misc/scripts.js"></script><script>$(document).ready(function(){

The error is:
Parse error on line 1:
<!DOCTYPEHTMLPUBLIC"
^
Expecting '{', '['

Am I missing some bracketing?
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
Ok I did that.  Can you once again show me how to NOT do it that way?

Not following you here, can you elaborate?

As for the validator just put the array output, not all of the html code
0
Bob SchneiderCo-OwnerAuthor Commented:
You guys are awesome.  http://www.gopherstateevents.com/series/series_results3.asp?series_id=19

Not totaling/sorting correctly but Houston, we have data!

Thanks so much.  I have learned an absolute ton!!!
0
Bob SchneiderCo-OwnerAuthor Commented:
These guys are awesome!
0
Scott Fell, EE MVEDeveloper & EE ModeratorCommented:
BobbaBuoy, I tried your link and it is getting a 500 error.  This is something we should have had set for you in the previous question.  From looking at your chosen answers where, I an not convinced there is a true answer for this issue.

Did you change your ajax page since accepting the answer?  What was the final code you used?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.