?
Solved

Populating a Json/Ajax Datatable with an array

Posted on 2014-08-05
24
Medium Priority
?
1,183 Views
Last Modified: 2014-08-06
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?
0
Comment
Question by:Bob Schneider
  • 10
  • 9
  • 5
24 Comments
 
LVL 34

Assisted Solution

by:Big Monty
Big Monty earned 1500 total points
ID: 40242235
can you give an example of what your array looks like?
0
 
LVL 34

Assisted Solution

by:Big Monty
Big Monty earned 1500 total points
ID: 40242238
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
 
LVL 34

Assisted Solution

by:Big Monty
Big Monty earned 1500 total points
ID: 40242252
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:Bob Schneider
ID: 40242259
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
 
LVL 34

Assisted Solution

by:Big Monty
Big Monty earned 1500 total points
ID: 40242270
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
 

Author Comment

by:Bob Schneider
ID: 40242509
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
 
LVL 34

Assisted Solution

by:Big Monty
Big Monty earned 1500 total points
ID: 40242528
No it doesn't

If you do a view source what does the output look like?
0
 

Author Comment

by:Bob Schneider
ID: 40242582
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
 
LVL 54

Assisted Solution

by:Scott Fell, EE MVE
Scott Fell,  EE MVE earned 500 total points
ID: 40242693
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
 
LVL 34

Assisted Solution

by:Big Monty
Big Monty earned 1500 total points
ID: 40242811
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
 
LVL 54

Accepted Solution

by:
Scott Fell,  EE MVE earned 500 total points
ID: 40242875
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
 

Author Comment

by:Bob Schneider
ID: 40243644
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
 
LVL 34

Assisted Solution

by:Big Monty
Big Monty earned 1500 total points
ID: 40243654
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
 

Author Comment

by:Bob Schneider
ID: 40243698
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
 
LVL 34

Expert Comment

by:Big Monty
ID: 40243701
can you paste here what you put into the validator?
0
 
LVL 54

Assisted Solution

by:Scott Fell, EE MVE
Scott Fell,  EE MVE earned 500 total points
ID: 40243709
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
 
LVL 54

Expert Comment

by:Scott Fell, EE MVE
ID: 40243711
Your json output should be pure json and nothing else.
0
 

Author Comment

by:Bob Schneider
ID: 40243802
0
 
LVL 34

Assisted Solution

by:Big Monty
Big Monty earned 1500 total points
ID: 40243850
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
 

Author Comment

by:Bob Schneider
ID: 40244000
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
 
LVL 34

Assisted Solution

by:Big Monty
Big Monty earned 1500 total points
ID: 40244030
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
 

Author Comment

by:Bob Schneider
ID: 40244057
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
 

Author Closing Comment

by:Bob Schneider
ID: 40244063
These guys are awesome!
0
 
LVL 54

Expert Comment

by:Scott Fell, EE MVE
ID: 40245331
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There are a couple ways to attach a JavaScript function to dynamically created elements. You can make a new script for each element as it’s created or you can use delegation. Delegation allows a single script that is added at page creation to mat…
Introduction Knockoutjs (Knockout) is a JavaScript framework (Model View ViewModel or MVVM framework).   The main ideology behind Knockout is to control from JavaScript how a page looks whilst creating an engaging user experience in the least …
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…
Suggested Courses

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question