I'm trying to load a Handsontable with JSON data from SQL Server using SQL Server's "FOR JSON AUTO" option and the jQuery AJAX POST method. The issue is the data being returned from the WebMethod function I use to retrieve the SQL Server data is truncating the JSON and therefore "breaking" the JSON format. I don't believe it's the jQuery AJAX POST method because if I pull the same JSON data as a static string from the function it works. It only truncates and breaks when I pull the data from SQL Server using the SQL Server "FOR JSON AUTO" option.
I've included are some sample code below.
Here is the jQuery AJAX POST method used to load the Handsontable. It is referring to a WebMethod function that returns data from SQL Server. I checked the length of the data returned from the function and it's being truncated to 2033 characters even though the full length is about 2257 characters.
$.ajax({
type: "POST",
url: "test.aspx/LoadJsonFromSqlServer",
data: '{MyRecordID: "1" }',
contentType: "application/json; charset=utf-8",
dataType: "json",
success: function (MyResponse) {
console.log(MyResponse.d.length);
var vMYDATA = JSON.parse(MyResponse.d);
vHOT.loadData(vMYDATA);
},
error: function (MyResponse) {
console.log("errorThrown = " + errorThrown);
}
});
Here's the WebMethod being called by AJAX above. It pulls data from SQL Server and uses the SQL Server "FOR JSON AUTO" option to format that data as JSON and return to AJAX above. This works fine when it's a small data set. However, larger data sets seem to be truncated.
<System.Web.Services.WebMethod()>
Public Shared Function LoadSqlServerJson(ByVal MyRecordID As String) As String
Dim vDBLOCAL As String = ConfigurationManager.ConnectionStrings("dbTest").ConnectionString
Dim vCNLOCAL As SqlConnection
Dim vSQLOCAL As String
Dim vCMLOCAL As SqlCommand
Dim vDRLOCAL As SqlDataReader
Dim vRESULT As String = ""
vCNLOCAL = New SqlConnection(vDBLOCAL)
vCNLOCAL.Open()
vSQLOCAL = "SELECT [TRANSACTION_ID],[PRODUCT_NAME],[TRANSACTION_QTY],[TRANSACTION_COST],[TRANSACTION_TOTAL],[TRANSACTION_NOTES],[PHYSICALTEMPLATE_ID] FROM [INPR_T_PHYSICALTEMPLATEDETAIL] FOR JSON AUTO" ' WHERE [TRANSACTION_ID]=@P00 FOR JSON AUTO;"
vCMLOCAL = New SqlCommand(vSQLOCAL, vCNLOCAL)
vCMLOCAL.Parameters.AddWithValue("@p00", MyRecordID)
vDRLOCAL = vCMLOCAL.ExecuteReader()
If vDRLOCAL.Read() Then
vRESULT = vDRLOCAL(0)
End If
vCNLOCAL.Close()
vDRLOCAL = Nothing
vCMLOCAL = Nothing
vCNLOCAL = Nothing
LoadSqlServerJson = vRESULT
End Function
The reason I don't think it has to do with the AJAX method is because if I pass the exact same JSON that the SQL Server's "FOR JSON AUTO" option returns (when running the query directly in SQL Server) as a static string, it works perfectly. The data is not truncated in AJAX and the Handsontable loads. Below is the full string that is returned. If I use this method, it all works.
<System.Web.Services.WebMethod()>
Public Shared Function LoadJsonFromString(ByVal MyRecordID As String) As String
Dim vRESULT As String = ""
vRESULT = ""
vRESULT += "["
vRESULT += " {"
vRESULT += " ""TRANSACTION_ID"": 1,"
vRESULT += " ""PRODUCT_NAME"": ""Cookie Dough"","
vRESULT += " ""TRANSACTION_QTY"": 5,"
vRESULT += " ""TRANSACTION_COST"": 20,"
vRESULT += " ""TRANSACTION_TOTAL"": 100,"
vRESULT += " ""TRANSACTION_NOTES"": ""Test"","
vRESULT += " ""PHYSICALTEMPLATE_ID"": ""WTF"""
vRESULT += " },"
vRESULT += " {"
vRESULT += " ""TRANSACTION_ID"": 2,"
vRESULT += " ""PRODUCT_NAME"": ""Chocolate Chips"","
vRESULT += " ""TRANSACTION_QTY"": 4,"
vRESULT += " ""TRANSACTION_COST"": 3,"
vRESULT += " ""TRANSACTION_TOTAL"": 12,"
vRESULT += " ""TRANSACTION_NOTES"": ""Test"","
vRESULT += " ""PHYSICALTEMPLATE_ID"": ""WTF"""
vRESULT += " },"
vRESULT += " {"
vRESULT += " ""TRANSACTION_ID"": 3,"
vRESULT += " ""PRODUCT_NAME"": ""Butterscoth Chips"","
vRESULT += " ""TRANSACTION_QTY"": 3,"
vRESULT += " ""TRANSACTION_COST"": 5,"
vRESULT += " ""TRANSACTION_TOTAL"": 15,"
vRESULT += " ""TRANSACTION_NOTES"": ""Test"","
vRESULT += " ""PHYSICALTEMPLATE_ID"": ""WTF"""
vRESULT += " },"
vRESULT += " {"
vRESULT += " ""TRANSACTION_ID"": 4,"
vRESULT += " ""PRODUCT_NAME"": ""Cookie Dough"","
vRESULT += " ""TRANSACTION_QTY"": 5,"
vRESULT += " ""TRANSACTION_COST"": 20,"
vRESULT += " ""TRANSACTION_TOTAL"": 100,"
vRESULT += " ""TRANSACTION_NOTES"": ""Test"","
vRESULT += " ""PHYSICALTEMPLATE_ID"": ""WTF"""
vRESULT += " },"
vRESULT += " {"
vRESULT += " ""TRANSACTION_ID"": 5,"
vRESULT += " ""PRODUCT_NAME"": ""Cookie Dough"","
vRESULT += " ""TRANSACTION_QTY"": 5,"
vRESULT += " ""TRANSACTION_COST"": 20,"
vRESULT += " ""TRANSACTION_TOTAL"": 100,"
vRESULT += " ""TRANSACTION_NOTES"": ""Test"","
vRESULT += " ""PHYSICALTEMPLATE_ID"": ""WTF"""
vRESULT += " },"
vRESULT += " {"
vRESULT += " ""TRANSACTION_ID"": 6,"
vRESULT += " ""PRODUCT_NAME"": ""Cookie Dough"","
vRESULT += " ""TRANSACTION_QTY"": 5,"
vRESULT += " ""TRANSACTION_COST"": 20,"
vRESULT += " ""TRANSACTION_TOTAL"": 100,"
vRESULT += " ""TRANSACTION_NOTES"": ""Test"","
vRESULT += " ""PHYSICALTEMPLATE_ID"": ""WTF"""
vRESULT += " },"
vRESULT += " {"
vRESULT += " ""TRANSACTION_ID"": 7,"
vRESULT += " ""PRODUCT_NAME"": ""Cookie Dough"","
vRESULT += " ""TRANSACTION_QTY"": 5,"
vRESULT += " ""TRANSACTION_COST"": 20,"
vRESULT += " ""TRANSACTION_TOTAL"": 100,"
vRESULT += " ""TRANSACTION_NOTES"": ""Test"","
vRESULT += " ""PHYSICALTEMPLATE_ID"": ""WTF"""
vRESULT += " },"
vRESULT += " {"
vRESULT += " ""TRANSACTION_ID"": 8,"
vRESULT += " ""PRODUCT_NAME"": ""Chocolate Chips"","
vRESULT += " ""TRANSACTION_QTY"": 4,"
vRESULT += " ""TRANSACTION_COST"": 3,"
vRESULT += " ""TRANSACTION_TOTAL"": 12,"
vRESULT += " ""TRANSACTION_NOTES"": ""Test"","
vRESULT += " ""PHYSICALTEMPLATE_ID"": ""WTF"""
vRESULT += " },"
vRESULT += " {"
vRESULT += " ""TRANSACTION_ID"": 9,"
vRESULT += " ""PRODUCT_NAME"": ""Butterscoth Chips"","
vRESULT += " ""TRANSACTION_QTY"": 3,"
vRESULT += " ""TRANSACTION_COST"": 5,"
vRESULT += " ""TRANSACTION_TOTAL"": 15,"
vRESULT += " ""TRANSACTION_NOTES"": ""Test"","
vRESULT += " ""PHYSICALTEMPLATE_ID"": ""WTF"""
vRESULT += " },"
vRESULT += " {"
vRESULT += " ""TRANSACTION_ID"": 10,"
vRESULT += " ""PRODUCT_NAME"": ""Cookie Dough"","
vRESULT += " ""TRANSACTION_QTY"": 5,"
vRESULT += " ""TRANSACTION_COST"": 20,"
vRESULT += " ""TRANSACTION_TOTAL"": 100,"
vRESULT += " ""TRANSACTION_NOTES"": ""Test"","
vRESULT += " ""PHYSICALTEMPLATE_ID"": ""WTF"""
vRESULT += " },"
vRESULT += " {"
vRESULT += " ""TRANSACTION_ID"": 11,"
vRESULT += " ""PRODUCT_NAME"": ""Cookie Dough"","
vRESULT += " ""TRANSACTION_QTY"": 5,"
vRESULT += " ""TRANSACTION_COST"": 20,"
vRESULT += " ""TRANSACTION_TOTAL"": 100,"
vRESULT += " ""TRANSACTION_NOTES"": ""Test"","
vRESULT += " ""PHYSICALTEMPLATE_ID"": ""WTF"""
vRESULT += " },"
vRESULT += " {"
vRESULT += " ""TRANSACTION_ID"": 12,"
vRESULT += " ""PRODUCT_NAME"": ""Cookie Dough"","
vRESULT += " ""TRANSACTION_QTY"": 5,"
vRESULT += " ""TRANSACTION_COST"": 20,"
vRESULT += " ""TRANSACTION_TOTAL"": 100,"
vRESULT += " ""TRANSACTION_NOTES"": ""Test"","
vRESULT += " ""PHYSICALTEMPLATE_ID"": ""WTF"""
vRESULT += " }"
vRESULT += "]"
LoadJsonFromString = vRESULT
End Function
One last thing, I read a number of related posts here with most suggesting altering the "web.config" file to increase the "maxJsonLength" value (in various ways) and none of them worked.
Since it works with smaller datasets, then I would try changing the allowed size in web.config.
Try this:
<appSettings>
<add key="aspnet:MaxJsonDeserializerMembers" value="2147483647" />
</appSettings>
Now above is 2 gig, but the above appSettings key should fix this, and allow a larger size.
You should find the above key in web.config after system.web, after system.webserver, and after runtime.
<system.web.extensions>
<scripting>
<webServices>
<jsonSerialization maxJsonLength="2047483647"/>
</webServices>
</scripting>
</system.web.extensions>
<security>
<requestFiltering>
<requestLimits maxAllowedContentLength="2048000000" maxQueryString="8192" />
</requestFiltering>
</security>
Given that SQL server will automatic limit json size returned, then seem some suggestions outlined here: