Using DataTable with AJAX on ASP VB.Net

lordmight

Hi All,

I would like to know, how to use a function from ASMX (Web Service) that returns a JSON string, here's my function from asmx

<WebMethod()> _
    Public Function GetJsonPrevTrans(CardCode As String) As String
        Return GetJson(MyLib.QueryDb.GetBpPrevTrans(CardCode:=CardCode))
    End Function

GetJson Function

Public Function GetJson(ByVal dt As DataTable) As String

        Dim serializer As System.Web.Script.Serialization.JavaScriptSerializer = New System.Web.Script.Serialization.JavaScriptSerializer()
        Dim rows As New List(Of Dictionary(Of String, Object))
        Dim row As Dictionary(Of String, Object)

        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
            rows.Add(row)
        Next
        Return serializer.Serialize(rows)
    End Function

And the GetBpPrevTrans

Public Shared Function GetBpPrevTrans(Optional CardCode As String = "")
        Dim PrevTransDT As DataTable = cmdDataTable("SELECT * FROM ViewInvoiceList WHERE CardCode LIKE '%" & CardCode & "%'")
        Return PrevTransDT
    End Function

Which returns this JSON

[{"TransId":"f6762604-17a9-4c42-80a8-fd4d68f3762f","DocEntry":38,"CardCode":"JP1001","DocNum":"123","Dscription":"This ii sa mple","DocDate":"\/Date(1432656000000)\/","DocDueDate":"\/Date(1432137600000)\/","DocTotal":1200.000000,"DocStatus":"2","GroupName":"Guild of Systems Owners and Corp","CompName":"Cygnal Technologies Trading and Services","ItemName":"SEO","UsrName":"Kristoffer Daniel  Santillan"},{"TransId":"f6762604-17a9-4c42-80a8-fd4d68f3762f","DocEntry":39,"CardCode":"JP1001","DocNum":"456","Dscription":"Description","DocDate":"\/Date(1432656000000)\/","DocDueDate":"\/Date(1432137600000)\/","DocTotal":14444.000000,"DocStatus":"2","GroupName":"Guild of Systems Owners and Corp","CompName":"Cygnal Technologies Trading and Services","ItemName":"SEO","UsrName":"Kristoffer Daniel  Santillan"},{"TransId":"f6762604-17a9-4c42-80a8-fd4d68f3762f","DocEntry":40,"CardCode":"JP1001","DocNum":"2326","Dscription":"Testing","DocDate":"\/Date(1432656000000)\/","DocDueDate":"\/Date(1432137600000)\/","DocTotal":14444.000000,"DocStatus":"2","GroupName":"Guild of Systems Owners and Corp","CompName":"Cygnal Technologies Trading and Services","ItemName":"SEO","UsrName":"Kristoffer Daniel  Santillan"},{"TransId":"0e898964-98fb-4be6-9be5-a225522d4d37","DocEntry":43,"CardCode":"JP1001","DocNum":"45345","Dscription":"455467567","DocDate":"\/Date(1432656000000)\/","DocDueDate":"\/Date(1432656000000)\/","DocTotal":450.000000,"DocStatus":"2","GroupName":"Guild of Systems Owners and Corp","CompName":"Guild of System Owners and Corp","ItemName":"SEO","UsrName":"Kristoffer Daniel  Santillan"},{"TransId":"35544d31-4bc9-42d9-95d4-45f9cc2ae214","DocEntry":91,"CardCode":"JP1001","DocNum":"1","Dscription":"1","DocDate":"\/Date(1432828800000)\/","DocDueDate":"\/Date(1432828800000)\/","DocTotal":1.000000,"DocStatus":"1","GroupName":"SKYWIND GROUP","CompName":"MU Classic Continent","ItemName":"SEO","UsrName":"Juan Pablo  Cigaral Sapitan"}]

How will I use the data result above to Data Tables? I am getting errors stating to visit /tn/1, /tn/2, /tn/3 so on and so forth.
Here's the jQuery;

$.ajax({
        data: JSON.stringify(CardCode),
        dataType: "json",
        url: "/WebServices/Invoice/BusinessPartner.asmx/GetJsonPrevTrans",
        type: "POST",
        contentType: "application/json; charset=utf-8",
        success: function (ef1) {
            var parsed = $.parseJSON(JSON.stringify(ef1.d));
            console.log(ef1);
            console.log(parsed);
            var bpPrevDt = $('#bpPrevTransData').DataTable({
                "ajax": {
                    "url": parsed,
                    "dataSrc": ""
                }
                //"bProcessing" : true,
                //"sAjaxSource": parsed,
                ////"sAjaxDataProp": "",
                //"aoColumns": [{
                //    "sTitle": "Desc",
                //    "mData" : "DocNum"
                //},
                //{
                //    "sTitle": "Desc",
                //    "mData": "DocNum"
                //},
                //{
                //    "sTitle": "Desc",
                //    "mData": "DocNum"
                //}]
            })
        }
    });

lordmight

Update, i revised the code to (in reference to http://legacy.datatables.net/ref#aaData):

var bpPrevDt = $('#bpPrevTransData').DataTable({
                "aaData": parsed,
                "aoColumns": [
                    { "sTitle": "DocNum", "mData": "DocNum" },
                    { "sTitle": "DocNum", "mData": "CardCode" },
                    { "sTitle": "DocNum", "mData": "CardName" },
                ]
            });

The parsed data is

[{"TransId":"f6762604-17a9-4c42-80a8-fd4d68f3762f","DocEntry":38,"CardCode":"JP1001","DocNum":"123","Dscription":"This ii sa mple","DocDate":"\/Date(1432656000000)\/","DocDueDate":"\/Date(1432137600000)\/","DocTotal":1200.000000,"DocStatus":"2","GroupName":"Guild of Systems Owners and Corp","CompName":"Cygnal Technologies Trading and Services","ItemName":"SEO","UsrName":"Kristoffer Daniel  Santillan"},{"TransId":"f6762604-17a9-4c42-80a8-fd4d68f3762f","DocEntry":39,"CardCode":"JP1001","DocNum":"456","Dscription":"Description","DocDate":"\/Date(1432656000000)\/","DocDueDate":"\/Date(1432137600000)\/","DocTotal":14444.000000,"DocStatus":"2","GroupName":"Guild of Systems Owners and Corp","CompName":"Cygnal Technologies Trading and Services","ItemName":"SEO","UsrName":"Kristoffer Daniel  Santillan"},{"TransId":"f6762604-17a9-4c42-80a8-fd4d68f3762f","DocEntry":40,"CardCode":"JP1001","DocNum":"2326","Dscription":"Testing","DocDate":"\/Date(1432656000000)\/","DocDueDate":"\/Date(1432137600000)\/","DocTotal":14444.000000,"DocStatus":"2","GroupName":"Guild of Systems Owners and Corp","CompName":"Cygnal Technologies Trading and Services","ItemName":"SEO","UsrName":"Kristoffer Daniel  Santillan"},{"TransId":"0e898964-98fb-4be6-9be5-a225522d4d37","DocEntry":43,"CardCode":"JP1001","DocNum":"45345","Dscription":"455467567","DocDate":"\/Date(1432656000000)\/","DocDueDate":"\/Date(1432656000000)\/","DocTotal":450.000000,"DocStatus":"2","GroupName":"Guild of Systems Owners and Corp","CompName":"Guild of System Owners and Corp","ItemName":"SEO","UsrName":"Kristoffer Daniel  Santillan"},{"TransId":"35544d31-4bc9-42d9-95d4-45f9cc2ae214","DocEntry":91,"CardCode":"JP1001","DocNum":"1","Dscription":"1","DocDate":"\/Date(1432828800000)\/","DocDueDate":"\/Date(1432828800000)\/","DocTotal":1.000000,"DocStatus":"1","GroupName":"SKYWIND GROUP","CompName":"MU Classic Continent","ItemName":"SEO","UsrName":"Juan Pablo  Cigaral Sapitan"}]

I am still getting error in console:

Uncaught TypeError: Cannot use 'in' operator to search for 'length' in 

Any help will be appreciated :)

lordmight

Dear All,

This is solved, it appears that I need to reparse the JSON result of the ASP web service back to JS Object.
Here is the updated Code:

$.ajax({
        data: JSON.stringify(CardCode),
        dataType: "json",
        url: "/WebServices/Invoice/BusinessPartner.asmx/GetJsonPrevTrans",
        type: "POST",
        contentType: "application/json; charset=utf-8",
        success: 
        function (ef1) {
            console.log(JSON.parse(ef1.d))
            console.log(ef1)
            $("#bpPrevJSONTrans").val(ef1.d).trigger('change');
                var parsed = JSON.parse(ef1.d);
                var bpPrevDt = $('#bpPrevTransData').DataTable({
                    retrieve: true,
                    "aaData": parsed,
                    "aoColumns": [
                      { "mData": "TransId" },
                      { "mData": "CardCode" },
                      { "mData": "GroupName" }
                    ]
                });  
        }
    });