Failed to load resource: the server responded with a status of 500 (Internal Server Error) When using JqGrid

Question

I am Getting Problem as Server returns 500 Error. Same URL is running on When i use inside ajax call

URL is = "TestService.asmx/GetQueryInfo"

jQuery("#list2").jqGrid({
                        mtype: 'POST',
                        postData: "{ TableName: '" + TableName + "', ColumnList: '" + ColumnNames + "' }",
                        serializeGridData: function (postData) {
                            // extend the parameter which will be send to the server
                            postData = JSON.stringify(postData);
                            // serialize the parameters as JSON string
                            alert(postData);
                            return JSON.stringify(postData);
                        },
                        url: "PredictiveDialer.asmx/GetQueryInfo",
                        ajaxGridOptions: { contentType: 'application/json; charset=utf-8' },
                        datatype: 'json',
                        colNames: ['ID', 'Code', 'Name', 'PassWord', 'ClientLevel', 'DeptNo', 'DeptName'],
                        colModel: [
                            { name: 'id', index: 'id', width: 55 },
                            { name: 'code', index: 'code', width: 90 },
                            { name: 'name', index: 'name', width: 100 },
                            { name: 'password', index: 'password', width: 80, align: "right" },
                            { name: 'ClientLevel', index: 'ClientLevel', width: 80, align: "right" },
                            { name: 'DeptNo', index: 'DeptNo', width: 80, align: "right" },
                            { name: 'DeptName', index: 'DeptName', width: 150, sortable: false }
                        ],
                        rowNum: 10,
                        rowList: [10, 20, 30],
                        pager: '#pager2',
                        sortname: 'id',
                        viewrecords: true,
                        sortorder: "id",
                        caption: "JSON Example",
                        jsonReader: {
                            root: function (obj) { return obj.d; },
                            page: function () { return 1; },
                            total: function () { return 1; },
                            records: function (obj) { alert(obj.d.length); return obj.d.length; }
                        }                        
                    });
                    jQuery("#list2").jqGrid('navGrid', '#pager2', { edit: false, add: false, del: false });

and WebService Code is as below

[WebMethod]    
public String GetQueryInfo(String TableName, String ColumnList)
{
    String daresult = String.Empty;
    String SQL = String.Empty;
    SqlConnection con = new SqlConnection("MyConnectionString");       
    SQL = "Select " + ColumnList + " From "+ TableName;
    SqlDataAdapter sda = new SqlDataAdapter(SQL, con);
    DataSet ds = new DataSet();
    sda.Fill(ds);
    daresult = DataSetToJSON(ds);
    return daresult;

}

public string DataSetToJSON(DataSet ds)
{
    Dictionary<string, object> dict = new Dictionary<string, object>();
    foreach (DataTable dt in ds.Tables)
    {
        object[] arr = new object[dt.Rows.Count + 1];
        for (int i = 0; i <= dt.Rows.Count - 1; i++)
        {
            arr[i] = dt.Rows[i].ItemArray;
        }
        dict.Add(dt.TableName, arr);
    }
    JavaScriptSerializer json = new JavaScriptSerializer();
    return json.Serialize(dict);
}

Is Something wrong With Above Code?

UPDATED CODE

Now i used new function to paise columnname with ColumnValue inArray

Server Side :

[WebMethod]
public Object GetQueryInfo(String TableNames, String ColumnList)
{
    Object daresult = new Object();
    String SQL = String.Empty;
    SqlConnection con = new SqlConnection("MyConnection");
    SQL = "Select top 2 " + ColumnList + " From " + TableNames;
    SqlDataAdapter sda = new SqlDataAdapter(SQL, con);
    DataSet ds = new DataSet();
    sda.Fill(ds);
    daresult = DataTableToJSON(ds.Tables[0]);
    //daresult = DataSetToJSON(ds);
    return daresult;

}

public object DataTableToJSON(DataTable table)
{     
    List<Dictionary<string, object>> list = new List<Dictionary<string, object>>();
    foreach (DataRow row in table.Rows)
    {
        Dictionary<string, object> dict = new Dictionary<string, object>();
        foreach (DataColumn col in table.Columns)
        {
            dict[col.ColumnName.ToLower()] = row[col];
        }
        list.Add(dict);
    }       
    return list;
}

Client Side :

jQuery("#list2").jqGrid({
                     mtype: 'POST',
                     url: "MyService.asmx/GetQueryInfo", 
                     serializeGridData: function (postData) {
                         return JSON.stringify({
                             TableNames: TableName,
                             ColumnList: ColumnNames
                         });
                     },

                     ajaxGridOptions: { contentType: "application/json; //charset=utf-8" }, //charset=utf-8                       
                     datatype: 'json',
                     colNames: ['ID', 'Code', 'Name', 'PassWord', 'ClientLevel', 'DeptNo', 'DeptName'],
                     colModel: [
                         { name: 'ID', index: "ID", width: 55, key: true, localreader: { id: "ID" } },
                         { name: 'Code', width: 90, align: 'left' },
                         { name: 'Name', width: 100, align: 'left' },
                         { name: 'PassWord', width: 80 },
                         { name: 'ClientLevel', width: 80 },
                         { name: 'DeptNo', width: 80 },
                         { name: 'DeptName', width: 150 }
                     ],
                     jsonReader: {
                         repeatitems: false,
                         root:'d',
                         page: function (obj) { return 1; },
                         total: function (obj) { return 1; },
                         records: function (obj) { return obj.length; },
                         id : "ID"
                     },

                     autoencode: true,
                     gridview: true,
                     rowNum: 10,
                     loadonce: true,
                     rowList: [10, 20, 30],
                     pager: '#pager2',
                     viewrecords: true,
                     caption: "JSON Example",

                     loadError: function (jqXHR, textStatus, errorThrown) {
                         alert('HTTP status code: ' + jqXHR.status + '\n' +
                               'textStatus: ' + textStatus + '\n' +
                               'errorThrown: ' + errorThrown);
                         alert('HTTP message body (jqXHR.responseText): ' + '\n' + jqXHR.responseText);
                     }

                 });
                 jQuery("#list2").jqGrid('navGrid', '#pager2', { edit: false, add: false, del: false });

score 1 · Accepted Answer · edited May 23 '17 at 10:25

1

Your current code have many problems. The main is: you convert the data returned from GetQueryInfo explicitly to JSON string which is wrong. The method should return Object instead of String. The .Net framework will convert the returned object to the JSON string automatically for you.

The second problem is the wrong usage of postData. You should never try to convert the returned object to JSON manually (like you do with "{ TableName: '" + TableName + "', ColumnList: '" + ColumnNames + "' }"). Instead of that you can just remove postData and modify the code of serializeGridData to something like

serializeGridData: function () {
    return JSON.stringify({
        TableName: TableName,
        ColumnList: ColumnNames
    });
}

It will replace the standard parameters which will be send typically to the server to TableName and ColumnList.

You should never trust the input data in your server code. The current implementation of GetQueryInfo is ideal way for SQL injection (see here for example too). Such code should be never used in production.

The last remark. You should add some options in jqGrid: loadonce: true (because you don't implemented server side paging of data), gridview: true (to improve the performance of the grid), autoencode: true (to interpret the input data as text and not as HTML fragments). I recommend you to add loadError cellback to the grid (see the answer). To display an error message in case of server side errors like 500.

edited May 23 '17 at 10:25

Community

1
1

answered Nov 05 '14 at 09:51

Oleg

220,925
34
403
798

Thanks For your reply Please look this http://stackoverflow.com/questions/26770284/jqgrid-not-binding-data – Jankya Nov 06 '14 at 01:31
1

@RamdasBhosale: You are welcome! I suppose you still don't change the code of `DataSetToJSON` like I suggested. I suppose that you still use `JavaScriptSerializer.JavaScriptSerializer` which is wrong. `GetQueryInfo` methow should return `Object` ainstead of `String`. – Oleg Nov 06 '14 at 05:51
I have change return Datatype From String to object It returns Valid Json but Not Binding to JqGrid – Jankya Nov 06 '14 at 05:54
1

@RamdasBhosale: You should append your question with updated code of web method. I still think that you use *old* code because the value of `d` property is the string. You can use `root` in `jsonReader` as function defined like in [the answer](http://stackoverflow.com/a/14749279/315935). In the case it will works in your case too. So you can use `jsonReader: {repeatitems: false, root: function (obj) { return typeof obj.d === "string" ? $.parseJSON(obj.d) : obj.d; }, records: function (obj) { return obj.length; }, page: function () { return 1; }, total: function () { return 1; }}` – Oleg Nov 06 '14 at 06:03
u r Great this works me and data is displayed in Grid. Thats Man. – Jankya Nov 06 '14 at 06:18

Failed to load resource: the server responded with a status of 500 (Internal Server Error) When using JqGrid

1 Answers1