当前位置: 动力学知识库 > 问答 > 编程问答 >

jquery - DataTables.net post to return json not updating table

问题描述:

I have an asp.net page that successfully builds the json for return to Datatables but the page is not updated?

Here is the call from my Server.aspx page:

$('#displayData').dataTable( {

"bProcessing": true,

"bStateSave": true,

"bServerSide": true,

"sAjaxSource": "Data.aspx",

"aoColumns": [

{ "sName": "id", "sTitle": "ID", "sWidth": "5%", "bSortable": "true" },

{ "sName": "engine", "sTitle": "Engine", "sWidth": "10%", "bSortable": "true" },

{ "sName": "browser", "sTitle": "browser", "sWidth": "10%", "bSortable": "true" },

{ "sName": "platform", "sTitle": "platform", "sWidth": "10%", "bSortable": "true" },

{ "sName": "version", "sTitle": "version", "sWidth": "10%", "bSortable": "true" },

{ "sName": "grade", "sTitle": "grade", "sWidth": "10%", "bSortable": "true" }

],

"sPaginationType": "full_numbers",

"aaSorting": [[1,'asc']],

"oLanguage": {

"sLengthMenu": "Page length: _MENU_",

"sSearch": "Filter:",

"sZeroRecords": "No matching records found"

},

"fnServerData": function ( sSource, aoData, fnCallback ) {

aoData.push(

{ "name": "table", "value": "ajax" },

{ "name": "sql", "value": "SELECT id, engine, browser, platform, version, grade" }

);

$.ajax( {"dataType": 'json',

"type": "POST",

"url": sSource,

"data": aoData,

"success": function (msg) {

alert(msg.d);

fnCallback(msg.d);

}

});

}

});

Here is the table in the page:

<table cellpadding="0" cellspacing="0" border="0" class="display" id="displayData">

<thead>

<tr>

<th align="left">ID</th>

<th align="left">Engine</th>

<th align="left">Browser</th>

<th align="left">Platform</th>

<th align="left">Version</th>

<th align="left">Grade</th>

</tr>

</thead>

<tbody>

<tr>

<td colspan="3" class="dataTables_empty">Loading data from server</td>

</tr>

</tbody>

</table>

And here is the code in the Page Load of Data.aspx (and this does return the json in str but the table on Server.aspx is not populated.

Hope you can shed some light on this.

 'Instanciation des variables

'Creation of variables

Dim str As String = "" 'Chaîne JSON en sortie - Will be the output JSON string

Dim strClassement As String = "" 'Colonne sur laquelle le tableau est trié - Will be the sorted column

Dim strSens As String = "" 'ASC or DESC

Dim strSensInverse As String = "" 'DESC or ASC

'Détection de la colonne de tri

'Detection of sorted column

Select Case Request("iSortCol_0")

Case 0

strClassement = "engine"

Case 1

strClassement = "browser"

Case 2

strClassement = "platform"

Case 3

strClassement = "version"

Case 4

strClassement = "grade"

Case Else

strClassement = "engine"

End Select

'Détection du sens du tri

'detection of direction of the sort

Select Case Request("sSortDir_0")

Case "asc"

strSens = " ASC"

strSensInverse = " DESC"

Case "desc"

strSens = " DESC"

strSensInverse = " ASC"

End Select

'Connexion SQL

'SQL connection

Try

Using maConnexion As New SqlConnection(ConfigurationManager.ConnectionStrings("cnn_TTR").ConnectionString)

maConnexion.Open()

Dim dataset As New DataSet

'Création de la requête SQL

'SQL request creation

Dim strRequeteA As New String("")

Dim strRequeteC As New String("")

Dim strRequeteB_1 As New String("")

Dim strRequeteB_2 As New String("")

Dim strRequeteB_3 As New String("")

'Le requête est bâtie selon une méthode permettant la pagination avec SQL Server

'The request is built with a method which allow pagination with SQL Server

' http://troels.arvin.dk/db/rdbms/

strRequeteA += "SELECT * FROM ("

strRequeteA += "SELECT TOP "

strRequeteA += Request("iDisplayLength").ToString

strRequeteA += " * FROM ("

strRequeteB_1 = "SELECT "

strRequeteB_2 = "TOP " + (Convert.ToInt32(Request("iDisplayStart")) + Convert.ToInt32(Request("iDisplayLength"))).ToString

strRequeteB_3 += " id, engine, browser, platform, version, grade "

strRequeteB_3 += "FROM ajax "

If Request("sSearch") <> "" Then

If Request("sSearch") <> " " Then

strRequeteB_3 += "WHERE "

Dim chaine As String

For i As Integer = 0 To (Split(Request("sSearch").ToString, " ").Length - 1)

chaine = Split(Request("sSearch").ToString, " ")(i)

If chaine <> "" Then

If i <> 0 Then

strRequeteB_3 += "AND ("

Else

strRequeteB_3 += "("

End If

strRequeteB_3 += "engine LIKE '%" + chaine + "%'"

strRequeteB_3 += " OR "

strRequeteB_3 += "browser LIKE '%" + chaine + "%'"

strRequeteB_3 += " OR "

strRequeteB_3 += "platform LIKE '%" + chaine + "%'"

strRequeteB_3 += " OR "

strRequeteB_3 += "version LIKE '%" + chaine + "%'"

strRequeteB_3 += " OR "

strRequeteB_3 += "grade LIKE '%" + chaine + "%'"

strRequeteB_3 += ") "

End If

Next

End If

End If

strRequeteC += " ORDER BY "

strRequeteC += strClassement

strRequeteC += strSens

strRequeteC += ") AS foo ORDER BY "

strRequeteC += strClassement

strRequeteC += strSensInverse

strRequeteC += ") AS bar ORDER BY "

strRequeteC += strClassement

strRequeteC += strSens

'Exécution de la requête

'Request execution

Dim MaCommande As New SqlCommand(strRequeteA + strRequeteB_1 + strRequeteB_2 + strRequeteB_3 + strRequeteC, maConnexion)

Dim MonAdapteur As SqlDataAdapter = New SqlDataAdapter(MaCommande)

MonAdapteur.Fill(dataset)

MaCommande.Dispose()

If dataset.Tables.Count > 0 Then

Dim table As New DataTable

table = dataset.Tables(0)

'Ecriture des données JSON

'JSON data writing

str = "{"

str += """sEcho"": " + Request("sEcho") + ","

'Requête permettant l'écriture du nombre total d'enregistrements

'Request finding the total records number

strRequeteA = "SELECT COUNT(id) FROM ajax"

dataset = New DataSet

MaCommande = New SqlCommand(strRequeteA, maConnexion)

MonAdapteur = New SqlDataAdapter(MaCommande)

MonAdapteur.Fill(dataset)

MaCommande.Dispose()

str += """iTotalRecords"": " + dataset.Tables(0).Rows(0)(0).ToString + ","

'Requête permettant l'écriture du nombre d'enregistrement filtrés

'Request finding the displayed records number

strRequeteA = "SELECT COUNT(id) FROM (" + strRequeteB_1 + strRequeteB_2 + strRequeteB_3 + ") AS P1"

dataset = New DataSet

MaCommande = New SqlCommand("SELECT COUNT(id) FROM (" + strRequeteB_1 + strRequeteB_3 + ") AS P1", maConnexion)

MonAdapteur = New SqlDataAdapter(MaCommande)

MonAdapteur.Fill(dataset)

MaCommande.Dispose()

str += """iTotalDisplayRecords"": " + dataset.Tables(0).Rows(0)(0).ToString + ","

str += """aaData"": ["

'Procédure évitant les doublons dans la dernière page

'Procedure avoiding double records in last page

Dim nbRowANePasAfficher As Integer = 0

If (Convert.ToInt32(Request("iDisplayStart")) + Convert.ToInt32(Request("iDisplayLength"))) > dataset.Tables(0).Rows(0)(0) Then

nbRowANePasAfficher = (Convert.ToInt32(Request("iDisplayStart")) + Convert.ToInt32(Request("iDisplayLength"))) - dataset.Tables(0).Rows(0)(0)

End If

If Convert.ToInt32(Request("iDisplayStart")) = 0 Then

nbRowANePasAfficher = 0

End If

'Ecriture des données JSON

'JSON data writing

Dim autre As Boolean = False

For Each row As DataRow In table.Rows

If nbRowANePasAfficher > 0 Then

nbRowANePasAfficher -= 1

Continue For

End If

If autre = True Then

str += ","

End If

str += "["

str += """" + row("engine").ToString + ""","

str += """" + row("browser").ToString + ""","

str += """" + row("platform").ToString + ""","

str += """" + row("version").ToString + ""","

str += """" + row("grade").ToString + """"

str += "]"

autre = True

Next

End If

str += "]"

str += "}"

Response.Write(str)

maConnexion.Close()

End Using

Catch ex As Exception

End Try

网友答案:

Solved! Works with Asp.Net, Vb.Net, Web Forms. Filter, sorts, paging request posts to data.aspx and return json result updates datatables.net. All works great. Now just need to customize all this for the real dataset.

$('#displayData').dataTable({
    "bProcessing": true,
    "bServerSide": true,
    "bAutoWidth": false,
    "aoColumns": [ 
                { "sName": "id", "sTitle": "ID", "sWidth": "5%", "bSortable": "true" },
                { "sName": "engine", "sTitle": "Engine", "sWidth": "10%", "bSortable": "true" },
                { "sName": "browser", "sTitle": "browser", "sWidth": "10%", "bSortable": "true" },
                { "sName": "platform", "sTitle": "platform", "sWidth": "10%", "bSortable": "true" },
                { "sName": "version", "sTitle": "version", "sWidth": "10%", "bSortable": "true" },
                { "sName": "grade", "sTitle": "grade", "sWidth": "10%", "bSortable": "true" }

            ],
            "sPaginationType": "full_numbers",
            "aaSorting": [[1,'asc']],
            "oLanguage": {
                "sLengthMenu": "Page length: _MENU_",
                "sSearch": "Filter:",
                "sZeroRecords": "No matching records found"
                        },
    "sAjaxSource":'Data.aspx'
});

Data.aspx now looks like this:

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load

    'Instanciation des variables
    'Creation of variables
    Dim str As String = "" 'Chaîne JSON en sortie - Will be the output JSON string
    Dim strClassement As String = "" 'Colonne sur laquelle le tableau est trié - Will be the sorted column
    Dim strSens As String = "" 'ASC or DESC
    Dim strSensInverse As String = "" 'DESC or ASC


    'Détection de la colonne de tri
    'Detection of sorted column
    Select Case Request("iSortCol_0")
        Case 0
            strClassement = "engine"
        Case 1
            strClassement = "browser"
        Case 2
            strClassement = "platform"
        Case 3
            strClassement = "version"
        Case 4
            strClassement = "grade"
        Case Else
            strClassement = "engine"
    End Select

    'Détection du sens du tri
    'detection of direction of the sort
    Select Case Request("sSortDir_0")
        Case "asc"
            strSens = " ASC"
            strSensInverse = " DESC"
        Case "desc"
            strSens = " DESC"
            strSensInverse = " ASC"
    End Select

    'Connexion SQL
    'SQL connection
    Try


        Using maConnexion As New SqlConnection(ConfigurationManager.ConnectionStrings("cnn_TTR").ConnectionString)

            maConnexion.Open()

            Dim dataset As New DataSet

            'Création de la requête SQL
            'SQL request creation
            Dim strRequeteA As New String("")
            Dim strRequeteC As New String("")
            Dim strRequeteB_1 As New String("")
            Dim strRequeteB_2 As New String("")
            Dim strRequeteB_3 As New String("")

            'Le requête est bâtie selon une méthode permettant la pagination avec SQL Server
            'The request is built with a method which allow pagination with SQL Server
            ' http://troels.arvin.dk/db/rdbms/
            strRequeteA += "SELECT * FROM ("
            strRequeteA += "SELECT TOP "
            strRequeteA += Request("iDisplayLength").ToString
            strRequeteA += " * FROM ("
            strRequeteB_1 = "SELECT "
            strRequeteB_2 = "TOP " + (Convert.ToInt32(Request("iDisplayStart")) + Convert.ToInt32(Request("iDisplayLength"))).ToString
            strRequeteB_3 += " id, engine, browser, platform, version, grade "
            strRequeteB_3 += "FROM ajax "

            If Request("sSearch") <> "" Then
                If Request("sSearch") <> " " Then
                    strRequeteB_3 += "WHERE "
                    Dim chaine As String
                    For i As Integer = 0 To (Split(Request("sSearch").ToString, " ").Length - 1)
                        chaine = Split(Request("sSearch").ToString, " ")(i)
                        If chaine <> "" Then
                            If i <> 0 Then
                                strRequeteB_3 += "AND ("
                            Else
                                strRequeteB_3 += "("
                            End If
                            strRequeteB_3 += "engine LIKE '%" + chaine + "%'"
                            strRequeteB_3 += " OR "
                            strRequeteB_3 += "browser LIKE '%" + chaine + "%'"
                            strRequeteB_3 += " OR "
                            strRequeteB_3 += "platform LIKE '%" + chaine + "%'"
                            strRequeteB_3 += " OR "
                            strRequeteB_3 += "version LIKE '%" + chaine + "%'"
                            strRequeteB_3 += " OR "
                            strRequeteB_3 += "grade LIKE '%" + chaine + "%'"
                            strRequeteB_3 += ") "
                        End If
                    Next
                End If
            End If

            strRequeteC += " ORDER BY "
            strRequeteC += strClassement
            strRequeteC += strSens

            strRequeteC += ") AS foo ORDER BY "
            strRequeteC += strClassement
            strRequeteC += strSensInverse

            strRequeteC += ") AS bar ORDER BY "
            strRequeteC += strClassement
            strRequeteC += strSens

            'Exécution de la requête
            'Request execution
            Dim MaCommande As New SqlCommand(strRequeteA + strRequeteB_1 + strRequeteB_2 + strRequeteB_3 + strRequeteC, maConnexion)
            Dim MonAdapteur As SqlDataAdapter = New SqlDataAdapter(MaCommande)
            MonAdapteur.Fill(dataset)
            MaCommande.Dispose()

            If dataset.Tables.Count > 0 Then
                Dim table As New DataTable
                table = dataset.Tables(0)

                'Ecriture des données JSON
                'JSON data writing
                str = "{"
                str += """sEcho"": " + Request("sEcho") + ","

                'Requête permettant l'écriture du nombre total d'enregistrements
                'Request finding the total records number
                strRequeteA = "SELECT COUNT(id) FROM ajax"
                dataset = New DataSet
                MaCommande = New SqlCommand(strRequeteA, maConnexion)
                MonAdapteur = New SqlDataAdapter(MaCommande)
                MonAdapteur.Fill(dataset)
                MaCommande.Dispose()
                str += """iTotalRecords"": " + dataset.Tables(0).Rows(0)(0).ToString + ","

                'Requête permettant l'écriture du nombre d'enregistrement filtrés
                'Request finding the displayed records number
                strRequeteA = "SELECT COUNT(id) FROM (" + strRequeteB_1 + strRequeteB_2 + strRequeteB_3 + ") AS P1"
                dataset = New DataSet
                MaCommande = New SqlCommand("SELECT COUNT(id) FROM (" + strRequeteB_1 + strRequeteB_3 + ") AS P1", maConnexion)
                MonAdapteur = New SqlDataAdapter(MaCommande)
                MonAdapteur.Fill(dataset)
                MaCommande.Dispose()
                str += """iTotalDisplayRecords"": " + dataset.Tables(0).Rows(0)(0).ToString + ","

                str += """aaData"": ["

                'Procédure évitant les doublons dans la dernière page
                'Procedure avoiding double records in last page
                Dim nbRowANePasAfficher As Integer = 0
                If (Convert.ToInt32(Request("iDisplayStart")) + Convert.ToInt32(Request("iDisplayLength"))) > dataset.Tables(0).Rows(0)(0) Then
                    nbRowANePasAfficher = (Convert.ToInt32(Request("iDisplayStart")) + Convert.ToInt32(Request("iDisplayLength"))) - dataset.Tables(0).Rows(0)(0)
                End If
                If Convert.ToInt32(Request("iDisplayStart")) = 0 Then
                    nbRowANePasAfficher = 0
                End If

                'Ecriture des données JSON
                'JSON data writing
                Dim autre As Boolean = False
                For Each row As DataRow In table.Rows
                    If nbRowANePasAfficher > 0 Then
                        nbRowANePasAfficher -= 1
                        Continue For
                    End If
                    If autre = True Then
                        str += ","
                    End If
                    str += "["
                    str += """" + row("id").ToString + ""","
                    str += """" + row("engine").ToString + ""","
                    str += """" + row("browser").ToString + ""","
                    str += """" + row("platform").ToString + ""","
                    str += """" + row("version").ToString + ""","
                    str += """" + row("grade").ToString + """"
                    str += "]"
                    autre = True
                Next
            End If
            str += "]"
            str += "}"

            Response.Clear()
            Response.ClearHeaders()
            Response.ClearContent()
            Response.Write(str)
            Response.Flush()
            Response.End()

            maConnexion.Close()

        End Using
    Catch ex As Exception
    End Try


End Sub
分享给朋友:
您可能感兴趣的文章:
随机阅读: