How to get "data" into the serialized json string

How to get "data" into the serialized json string

CoolinJackCoolinJack Posts: 4Questions: 2Answers: 0
edited March 2021 in Free community support

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!

Answers

  • kthorngrenkthorngren Posts: 20,149Questions: 26Answers: 4,736

    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

  • CoolinJackCoolinJack Posts: 4Questions: 2Answers: 0
    edited March 2021

    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
    
This discussion has been closed.