Reputation: 1623
I keep getting "invalid json response" from datatable Jq plugin, I m posting my webmethod and HTML structure along with AJAX call, I suspect that my web method is not serializing to JSON format properly, but when I tested the format I found it valid (http://jsonlint.com/). I can't figure out the problem here
<WebMethod()> _
<ScriptMethod(ResponseFormat:=ResponseFormat.Json, UseHttpGet:=False, XmlSerializeString:=False)> _
Public Function SrcTblRegx()
Dim constr As String = ConfigurationManager.ConnectionStrings("ARTSQLConStrng").ConnectionString
Using con As New SqlConnection(constr)
Using cmd As New SqlCommand("TblRegSearchx", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Connection = con
Dim ds As New DataSet()
Using sda As New SqlDataAdapter(cmd)
sda.Fill(ds)
End Using
Dim jsondata As String = JsonConvert.SerializeObject(ds)
Return jsondata
End Using
End Using
End Function
The Webmethod Output is
<anyType xmlns:q1="http://www.w3.org/2001/XMLSchema" xmlns:d1p1="http://www.w3.org/2001/XMLSchema-instance" xmlns="http://tempuri.org/" d1p1:type="q1:string">
{"Table":[{"Filenum":15112777,"FullName":"marwam saleh moh saleem","DOB":"2015-11-26T00:00:00"}]}
</anyType>
<table id="RegSrc2" class="table table-bordered table-striped">
<thead>
<tr>
<th><b>File Number</b></th>
<th><b>Patient Name</b></th>
<th><b>DOB</b></th>
</tr>
</thead>
<tbody></tbody>
<tfoot>
<tr>
<th><b>File Number</b></th>
<th><b>Patient Name</b></th>
<th><b>DOB</b></th>
</tr>
</tfoot>
</table>
$(document).ready(function () {
var $table = $('#RegSrc2');
$table.dataTable({
bProcessing: true,
"serverSide": true,
"sAjaxSource": $table.data('../CONFIG/WebSerTblsSearch.asmx/SrcTblRegx'),
})
});
I m adding my web browser error too
jquery.dataTables.js:3929 Uncaught TypeError: Cannot set property 'data' of null
at _fnBuildAjax (jquery.dataTables.js:3929)
at _fnAjaxUpdate (jquery.dataTables.js:3946)
at _fnDraw (jquery.dataTables.js:3409)
at _fnReDraw (jquery.dataTables.js:3528)
at _fnInitialise (jquery.dataTables.js:4710)
at loadedInit (jquery.dataTables.js:1320)
at HTMLTableElement.<anonymous> (jquery.dataTables.js:1332)
at Function.each (jquery-2.1.4.js:374)
at jQuery.fn.init.each (jquery-2.1.4.js:139)
at jQuery.fn.init.DataTable [as dataTable] (jquery.dataTables.js:869)
Upvotes: 0
Views: 584
Reputation: 1623
I made changes from the code above, i m using datatable instead of dataset.
this code for serializing datatable
Public Function DataTableToJSONWithJavaScriptSerializer(table As DataTable) As String
Dim jsSerializer As New JavaScriptSerializer()
Dim parentRow As New List(Of Dictionary(Of String, Object))()
Dim childRow As Dictionary(Of String, Object)
For Each row As DataRow In table.Rows
childRow = New Dictionary(Of String, Object)()
For Each col As DataColumn In table.Columns
childRow.Add(col.ColumnName, row(col))
Next
parentRow.Add(childRow)
Next
Return jsSerializer.Serialize(parentRow)
End Function
Server side Processing, webmethod (stored procedure)
<WebMethod()> _
<ScriptMethod(ResponseFormat:=ResponseFormat.Json)> _
Public Function SrcTblReg(ByVal SrchTxt As String)
Dim constr As String = ConfigurationManager.ConnectionStrings("ARTSQLConStrng").ConnectionString
Using con As New SqlConnection(constr)
Using cmd As New SqlCommand("TblRegSearch", con)
cmd.CommandType = CommandType.StoredProcedure
If (String.IsNullOrEmpty(SrchTxt)) Then
cmd.Parameters.Add("@Searchtxt", SqlDbType.NVarChar).Value = DBNull.Value
Else
cmd.Parameters.Add("@Searchtxt", SqlDbType.NVarChar).Value = SrchTxt.Trim()
End If
cmd.Connection = con
Using sda As New SqlDataAdapter(cmd)
Dim dt As New DataTable()
sda.Fill(dt)
Dim sJSON = DataTableToJSONWithJavaScriptSerializer(dt)
Return sJSON
End Using
'Dim jsondata As String = JsonConvert.SerializeObject(ds)
'Return jsondata
End Using
End Using
End Function
Finally Client side as provided by Bindrid.
$(document).ready(function () {
$('#example').DataTable({
"processing": false,
// since you are getting all the data at once set serverSide to
// false, otherwise using the built in search and paging will trigger
// more ajax calls to get the same data.
"serverSide": false,
"ajax": {
// returning the data from the server as json data
contentType: "application/json; charset=utf-8",
// assuming your url is right....
url: "../CONFIG/WebSerTblsSearch.asmx/SrcTblRegx",
type: "Post",
// You are not sending parameters to the server so next line commented out
data: function (dtParms) {
return JSON.stringify({ SrchTxt: "your search string" });
},
dataFilter: function (res) {
// do what you need to the data before it loads to the table
// first deserialization
var parsed = JSON.parse(res);
// now you can access the data in d and deserialize that
var morp = JSON.parse(parsed.d);
// reserialize to what datatables expect.
return JSON.stringify( { data: morp });
},
error: function (x, y) {
console.log(x);
}
},
columns:[
{data:"Filenum"},
{data: "FullName"},
{data:"DOB"}]
});
});
Upvotes: 0
Reputation: 3735
Here is the way I do it. I have had more success using a post instead of a get with web services. Also, because your web service is serializing (I do it the same way) the json object ends up getting serialized twice. Also, data tables is expecting the data to be in the form of {data: [your data] } and web services returns {d:[your data serialized]} you have to make adjustments client side. Here is how I do it.
$(document).ready(function () {
$('#example').DataTable({
"processing": false,
// since you are getting all the data at once set serverSide to
// false, otherwise using the built in search and paging will trigger
// more ajax calls to get the same data.
"serverSide": false,
"ajax": {
// returning the data from the server as json data
contentType: "application/json; charset=utf-8",
// assuming your url is right....
url: "../CONFIG/WebSerTblsSearch.asmx/SrcTblRegx",
type: "Post",
// You are not sending parameters to the server so next line commented out
data: function (dtParms) {
return JSON.stringify({ SrchTxt: "your search string" });
},
dataFilter: function (res) {
// do what you need to the data before it loads to the table
// first deserialization
var parsed = JSON.parse(res);
// now you can access the data in d and deserialize that
var morp = JSON.parse(parsed.d);
// reserialize to what datatables expect.
return JSON.stringify( { data: morp });
},
error: function (x, y) {
console.log(x);
}
},
columns:[
{data:"Filenum"},
{data: "FullName"},
{data:"DOB"}]
});
});
Upvotes: 1