How to get "data" into the serialized json string

CoolinJack

Here is the example of my aspx page

 <script type="text/javascript">
            $(document).ready(function () {
                $('#players').DataTable({
                    "processing": true,
                    "serverSide": true,
                    "ajax": {
                        "url": "ServerSideProcessing.aspx",
                        "type": "POST",
                        "dataType": "JSON",
                        "dataSrc": "data",
                        "columns": [
                            { "data": "ID" },
                            { "data": "TeamID" },
                            { "data": "TeamName" },
                            { "data": "pno" },
                            { "data": "PlayerName" },
                            { "data": "Games" },
                            { "data": "Goals" },
                            { "data": "Assists" },
                            { "data": "Points" },
                            { "data": "Pens" },
                            { "data": "Misc" }
                                   ]
                    }
                });
            });
         </script>

Valid Json Example:

[{
    "ID": "3",
    "teamid": "1",
    "TeamName": "Team 1",
    "pno": "3",
    "PlayerName": "Player 1",
    "Games": "2",
    "Goals": "3",
    "Assists": "4",
    "Points": "5",
    "Pens": "8",
    "Misc": "7"
}]

How do I get the "data" Header into the json string ?

{
    "data": [  <<<<<<<<<<
        {
            "name": "Tiger Nixon",
            "position": "System Architect",
            "salary": "$320,800",
            "start_date": "2011/04/25",
            "office": "Edinburgh",
            "extn": "5421"
        },
        ...
    ]
}

Edited by Kevin: Syntax highlighting. Details on how to highlight code using markdown can be found in this guide!

kthorngren

How do I get the "data" Header into the json string ?

You don't have to do that but to do so is specific to the language you are using for your server. Instead you can use ajax.dataSrc with an empty string. See below code.

You have defined your columns inside the ajax config option which isn't going to work. Based on the JSON snippet you provided you aren't using server side processing as it looks like the response doesn't have the parameters discussed in the SSP docs. Try this code:

<script type="text/javascript">
           $(document).ready(function () {
               $('#players').DataTable({
                   "ajax": {
                       "url": "ServerSideProcessing.aspx",
                       "type": "POST",
                       "dataType": "JSON",
                       "dataSrc": "",
                   },
                   "columns": [
                       { "data": "ID" },
                       { "data": "TeamID" },
                       { "data": "TeamName" },
                       { "data": "pno" },
                       { "data": "PlayerName" },
                       { "data": "Games" },
                       { "data": "Goals" },
                       { "data": "Assists" },
                       { "data": "Points" },
                       { "data": "Pens" },
                       { "data": "Misc" }
                    ]

               });
           });
        </script>

The columns.data strings are case sensitive. You have { "data": "TeamID" }, but your JSON response has "teamid": "1",. The case needs to match.

Kevin

CoolinJack

Hi Kevin

Many thanks for those pointers, it will help me to get a working solution !.

I though it might be of help if I included all My Code !, as It would help someone in the same situation

I have read the requirements of the values that would be required in the json reponse, for ssp, so I though I would share with you how I have created my json.

Step 1: A Datatable is Filled using a MSQL Stored Procedure.
Step 2: A New DataTable is Created with all the correct columns and Data
Step 3: The Datatable is Serialized then returned to the calling web page

Public Function GetAllPlayersJson(PlayerType As String) As String
            Dim NewDBConnections As New MainDBConnections.DBConnection
            Dim lsjsonString As String = ""
            Dim dt = New DataTable()
            Dim connectionString As String = NewDBConnections.ConnectionString

            Try
                Using conn As New MySqlConnection(connectionString)
                    Using SQLCommand As New MySqlCommand("spGetAllPlayers", conn)
                        SQLCommand.CommandType = CommandType.StoredProcedure
                        SQLCommand.Parameters.AddWithValue("@inPlayerType", PlayerType)
                        Using sda As New MySqlDataAdapter(SQLCommand)
                            sda.Fill(dt)
                        End Using
                    End Using
                    conn.Close()
                End Using
            Catch ex As Exception
                Throw New System.Exception("Error Message << " + ex.Message)
            End Try

            lsjsonString = JsonFormat(dt)

            Return lsjsonString
 End Function
 
 Public Function JsonFormat(dt As DataTable) As String
            Dim JsonString As String = Nothing
            Dim liCounter As Integer
            Dim liTotalCounter As Integer
            Dim DataRow As DataRow

            liTotalCounter = dt.Rows.Count - 1

            ' Add the Columns to the Data Table
            Dim PlayerTable As New DataTable
            PlayerTable.TableName = "Players"

            PlayerTable.Columns.Add("ID", GetType(String))
            PlayerTable.Columns.Add("teamid", GetType(String))
            PlayerTable.Columns.Add("TeamName", GetType(String))
            PlayerTable.Columns.Add("pno", GetType(String))
            PlayerTable.Columns.Add("PlayerName", GetType(String))
            PlayerTable.Columns.Add("Games", GetType(String))
            PlayerTable.Columns.Add("Goals", GetType(String))
            PlayerTable.Columns.Add("Assists", GetType(String))
            PlayerTable.Columns.Add("Points", GetType(String))
            PlayerTable.Columns.Add("Pens", GetType(String))
            PlayerTable.Columns.Add("Misc", GetType(String))


            For liCounter = 0 To liTotalCounter

                If IsDBNull(dt.Rows(liCounter).Item(0)) = False Then

                    DataRow = PlayerTable.NewRow

                    DataRow("ID") = CType(dt.Rows(liCounter).Item(0), String)
                    DataRow("teamid") = CType(dt.Rows(liCounter).Item(1), String)
                    DataRow("TeamName") = CType(dt.Rows(liCounter).Item(2), String)
                    DataRow("pno") = CType(dt.Rows(liCounter).Item(3), String)
                    DataRow("PlayerName") = CType(dt.Rows(liCounter).Item(4), String)
                    DataRow("Games") = CType(dt.Rows(liCounter).Item(5), String)
                    DataRow("Goals") = CType(dt.Rows(liCounter).Item(6), String)
                    DataRow("Assists") = CType(dt.Rows(liCounter).Item(7), String)
                    DataRow("Points") = CType(dt.Rows(liCounter).Item(8), String)
                    DataRow("Pens") = CType(dt.Rows(liCounter).Item(9), String)
                    DataRow("Misc") = CType(dt.Rows(liCounter).Item(10), String)

                    PlayerTable.Rows.Add(DataRow)

                End If

            Next

            PlayerTable.AcceptChanges()

            JsonString = GetJson(PlayerTable)

            Return JsonString

        End Function
        
Private Function GetJson(ByVal dt As DataTable) As String
    'Returns a valid json file
    Dim Jserializer As New System.Web.Script.Serialization.JavaScriptSerializer()

    Dim rowsList As New List(Of Dictionary(Of String, Object))
    Dim row As Dictionary(Of String, Object)


    Dim options = New JsonSerializerOptions

    With options
        .WriteIndented = True
    End With


    For Each dr As DataRow In dt.Rows
        row = New Dictionary(Of String, Object)
        For Each col As DataColumn In dt.Columns
            row.Add(col.ColumnName, dr(col))
        Next
        rowsList.Add(row)
    Next

    Return Jserializer.Serialize(rowsList)


End Function