2

I'm trying to get data from:

 jQuery(document).ready(function() {

 var mydata;
        $.getJSON('@Url.Action("GetJsonData", "Home")', function(data) {

            datatype: 'json',
                mydata = data;
            // alert(mydata);

        });

To my jqGrid:

        $("#grid").jqGrid({
            datastr: data,
            datatype: 'json',
            width: '100%',
            colNames: ["Seq ID", "Fund ID", "Name", "Fund", "Bonus", "Allocation", "Blank", "Begin", "End"],
            colModel: [
                {
                    name: 'seqid',
                    index: 'seqid',
                    editable: true,
                }, {
                    name: 'fund id',
                    index: 'fund id',
                    editable: true,
                }, {
                    name: 'name',
                    index: 'name',
                    editable: true,
                }, {
                    name: 'fund',
                    index: 'fund',
                    editable: true,
                }, {
                    name: 'bonus',
                    index: 'bonus',
                    editable: true,
                }, {
                    name: 'allocation',
                    index: 'allocation',
                    editable: true,
                }, {
                    name: 'blank',
                    index: 'blank',
                    editable: true,
                }, {
                    name: 'begin',
                    index: 'begin',
                    editable: true,
                }, {
                    name: 'end',
                    index: 'end',
                    editable: true,
                }
            ],


            pager: '#pager',
            'cellEdit': true,
            'cellsubmit': 'clientArray',
            editurl: 'clientArray'
        });

Data looks like:

{
    "FUND_SEQ_ID": 1.0,
    "FUND_ID": 23,
    "FUND_NM": "INSTITUTIONAL",
    "FUND_TICKER_NM": "TINXX",
    "FUND_SALARY_IND": "A",
    "FUND_BONUS_IND": "N",
    "FUND_ALCTN_IND": "\u0000",    <- This should be null
    "BEG_DT": "20140101",
    "END_DT": "24000101"
  },

I tried: datatype: jsonstring, datastr: data, data: data.. all give me nothing or p.colModel is null or not an object.

The data in the getJSON method is there. Just not sure how to pass it.

Update: Here's how I got it to work using a DataTable in MVC 4 Razor.

In HomeController.cs I have a method:

    public string GetAssociateFromDb()
    {
        DataTable dt = new DataTable();
        string jsonData;
        string connString = ConfigurationManager.ConnectionStrings["DEFCOMP"].ConnectionString;
        using (SqlConnection connection = new SqlConnection())
        {
            connection.ConnectionString = connString;
            using (var cmd = new SqlCommand("SELECT * FROM FUND", connection))
            {
                connection.Open();
                SqlDataAdapter myAdapter = new SqlDataAdapter(cmd);
                myAdapter.Fill(dt);
                return JsonConvert.SerializeObject(dt); // Converted to JSON string
            }
        }

    }

In my view (Index.cshtml), I call that method in the url of jQGrid.

    $(document).ready(function() {


            jQuery("#grid").jqGrid({
                url: '@Url.Action("GetAssociateFromDb", "Home")',
                datatype: "json",
                width: '100%',
                colNames: ["Seq ID", "Fund ID", "Name", "Fund", "Salary", "Bonus", "Allocation", "Begin", "End"],
                colModel: [
                    { name: "FUND_SEQ_ID" },
                    { name: "FUND_ID" },
                    { name: "FUND_NM" },
                    { name: "FUND_TICKER_NM" },
                    { name: "FUND_SALARY_IND" },
                    { name: "FUND_BONUS_IND" },
                    { name: "FUND_ALCTN_IND" },
                    { name: "BEG_DT" },
                    { name: "END_DT" }
                ],
                cmTemplate: { editable: true },
            //    data: JSON.parse(data),  // Load Data
                rowNum: 10,        // Total records to show at a time by default
                loadonce: true,       
                rowList: [10, 20],  // For Paging
                pager: '#pager',
                jsonReader: {
                    repeatitems: false,
                    page: function () { return 1; },  // This was necessary.
                    root: function (obj) { return obj; },
                    records: function (obj) { return obj.length; }
                },
                viewrecords: true,
                gridview: true,
                autowidth: true,
                height: 'auto',
                hoverrows: true,
                caption: "List of Funds"
        });
    });
Mark C.
  • 6,332
  • 4
  • 35
  • 71
  • The data which you included in the question are **not an array on items**. Do you really try to use jqGrid in the way or you just included not full response from the server? Why you don't use `url: '@Url.Action("GetJsonData", "Home")'` and `datatype: "json"`? it's the most standard way of filling of the grid data. – Oleg May 20 '14 at 17:23
  • Thanks for the comment @Oleg. I did try that, but the grid is still empty. – Mark C. May 20 '14 at 17:25
  • The data is actually an array. It looks like the example provided, but 10 additional records wrapped in `[ ]`. – Mark C. May 20 '14 at 17:28

4 Answers4

2

The main problem which I see is the naming of columns not the same as in the input JSON data. Try to replace colModel to the following:

colModel: [
    { name: "FUND_SEQ_ID" },
    { name: "FUND_ID" },
    { name: "FUND_NM" },
    { name: "FUND_TICKER_NM" },
    { name: "FUND_SALARY_IND" },
    { name: "FUND_BONUS_IND" },
    { name: "FUND_ALCTN_IND" },
    { name: "BEG_DT" },
    { name: "END_DT" }
],
cmTemplate: {editable: true},

The option cmTemplate allows you to set common properties in colModel. I recommend you also use gridview: true and autoencode: true in all your grids.

Additionally you can use

url: '@Url.Action("GetJsonData", "Home")',
datatype: "json",
loadonce: true

instead of usage of $.getJSON.

Oleg
  • 220,925
  • 34
  • 403
  • 798
  • Thank you for your answer. I did exactly as you said..but still no data? No errors or anything, just no data. – Mark C. May 20 '14 at 21:35
  • @MurDeR: You are welcome! I suppose that you made some error in your code. Look at [the demo](http://www.ok-soft-gmbh.com/jqGrid/MurDeR.htm) and [another one](http://www.ok-soft-gmbh.com/jqGrid/MurDeR1.htm). The first demo uses `datatype: "jsonstring"` and the second uses `datatype: "json"` and loads the data from the server. Both the demos works. You should compare the code of the demos with your code and you should find your error. – Oleg May 20 '14 at 23:39
  • thanks again for your demo. I have tried your way and it works. I think my problem is `$("#grid").jqGrid({ url: '@Url.Action("GetJsonData", "Home")', datatype: "json", loadonce: true,` ... It sucks I can't use `alert(@Url.Action.....);` – Mark C. May 21 '14 at 12:29
  • can we continue this discussion somewhere else? – Mark C. May 21 '14 at 18:14
  • @MurDeR: we are in different time zones. What is your current problem? What you mean with `alert`? You can use `beforeProcessing` and `loadComplete` callbacks (or `jqGridAfterGridComplete`) to do some actions after response from the server have received. The callback `beforeProcessing` allows you even modify the server response before it will be processed by jqGrid. The callback `beforeProcessing` will be called after the response have been proceed and the grid have been filled. – Oleg May 22 '14 at 07:55
  • Thank you for the further explanation! I am having a hard time understanding why my data from my controller isn't getting to the `jqGrid` call? I have literally searched for hours, and while I don't anticipate you to be a personal debugging service, I feel as though you would be able to see my mistake in ~5 seconds, compared to me who has no idea where the error/bug is. – Mark C. May 22 '14 at 12:15
  • @MurDeR: Do you debugged the code? You can define both `beforeProcessing` and `loadComplete` callbacks and set breakpoints inside of callbacks. You can verify that the first parameter of `beforeProcessing` callback (`data`) contains the data which you expected from the server. Do you validated (set breakpoint) that `GetJsonData` action are called? – Oleg May 22 '14 at 14:29
  • It DOES hit `GetJsonData` and it DOES hit `url: '@Url.Action("GetJsonData", "Home")',`.. but that's it. It does not load anything in `jqGrid` after that...If you want a visualization of my `JavaScript`, [here](http://jsfiddle.net/GentleMaN/KQ7cK/) it is. – Mark C. May 22 '14 at 14:47
  • @MurDeR: Will be called `beforeProcessing`? – Oleg May 22 '14 at 17:39
  • No, should I put that above `url:...`? – Mark C. May 22 '14 at 17:41
  • @MurDeR: You should include `loadError` callback in the case (see [the answer](http://stackoverflow.com/a/6969114/315935) for more details). Probably you return wrong formatted data from `'@Url.Action("GetJsonData", "Home")'`. I recommend you to trace HTTP traffic with respect of Developer Tools of IE (press F12 to start, choose Network and start the trace) or even better with [Fiddler](http://www.telerik.com/fiddler). – Oleg May 22 '14 at 18:10
  • In my controller, the return for `public JsonResult GetJsonData()` looks like...`[{"FUND_SEQ_ID":1,"FUND_ID":23,"FUND_NM":"INSTITUTIONAL MONEY MARKET FUND","FUND_TICKER_NM":"TINXX","FUND_SALARY_IND":"A","FUND_BONUS_IND":"N","FUND_ALCTN_IND":"\u0000","BEG_DT":"20140101","END_DT":"24000101"},{"FUND_SEQ_ID":2,"FUND_ID":30,"FUND_NM":"LARGE CAP GROWTH FUND CLASS"`... etc. Do I need to format differently? I will also work on what you directed me to do. I have done 0 java script debugging before, so sorry for my inexperience. – Mark C. May 22 '14 at 18:20
  • @MurDeR: Do you include `loadError` callback? Is it the data which you get from HTTP trace? The problem could be that the data will be placed inside of HTML page or the server could generate wrong HTTP headers (for example wrong `Content-Type` etc)... – Oleg May 22 '14 at 18:50
  • Yes, I included the `loadError` from the link you suggested. It never gets hit – Mark C. May 22 '14 at 18:53
  • @MurDeR: It's very strange! Either `beforeProcessing` or `loadError` will be called after jqGrid receive response from the server. Do you made HTTP trace? Do you have the demo online? – Oleg May 22 '14 at 18:56
  • No, but I can supply source code and results if that would help you? I am close but I just don't know where I am going wrong. – Mark C. May 22 '14 at 18:59
  • @MurDeR: If you supply source code of your demo I could take a look in it tomorrow. – Oleg May 22 '14 at 19:03
  • Here is the source code with some notes [JSFiddle](http://jsfiddle.net/GentleMaN/KQ7cK/1/) – Mark C. May 22 '14 at 19:48
  • 1
    @MurDeR: You have to replace `return serializer.Serialize(rows);` to `return rows;` in `GetAssociateFromDB`. The problem is that `return Json(jsonData, JsonRequestBehavior.AllowGet);` **makes JSON serialization**. If you serialize object than the deserialized data will be also object on the client side. If you sertilize **string** (like you do currently) then the string will be deserialized as the string too on the client side and processing will failed. So the current error is **in the server code**. You will see `\"` instead of `"` in the server response. – Oleg May 23 '14 at 07:27
  • I'm sorry I'm just getting back to you - I was out of town. Is there a way I could have known that this was the issue? Do you know of an example I could follow? I get an error because it `"Cannot convert var rows = new List>(); to 'string' "` – Mark C. May 27 '14 at 12:25
  • @MurDeR: I'm not sure which problem you have currently. You can just use *anonymous* data type: `new {FUND_SEQ_ID: 1.0, FUND_ID: 23,FUND_NM: "INSTITUTIONAL MONEY MARKET FUND",...}`. You can use `List` (or `List`). – Oleg May 27 '14 at 15:22
  • thanks for replying! So, I am confused. Do you know of a tutorial for what I am trying to do? I am not sure how to get the data from the datatable to JSON format. I feel like this should be easy but it's been nothing but frustration. Thanks for your help, by the way. – Mark C. May 27 '14 at 15:30
  • @MurDeR: Look at the code of [the old answer](http://stackoverflow.com/a/5501644/315935) for example. One can simplify the code if the size of data is not large (less as 10000 rows for example). One would can use `loadonce: true` option on the client side, one would need return *all* data and the paging, sorting and filtering of the data will be done on the client side by jqGrid. – Oleg May 27 '14 at 16:57
  • Thank you for the resource. That example is a little much for my experience. I will try to keep looking for an example with DataTable that I can understand. I will stop annoying you haha thank you for your time and patience, – Mark C. May 27 '14 at 17:05
0

Based on what @Oleg said, you should remove your function on jQuery(document).ready and set this options on jqGrid:

$("#grid").jqGrid({
    url: '@Url.Action("GetJsonData", "Home")',
    datatype: "json",
    width: '100%',
    // and so on
...
});
lucasdc
  • 1,032
  • 2
  • 20
  • 42
  • View my updated code [here](http://jsfiddle.net/GentleMaN/3DRGw/). It's making IE crash. Do you see anything wrong? I did as you said, I removed the `url:` and changed datatype to `local` – Mark C. May 20 '14 at 20:45
  • What do you mean by "It's making IE crash"? What's the error? – lucasdc May 20 '14 at 20:47
  • "Stop running this script? A script on this page is causing your web browser to run slowly, if it continues to run, your computer may become unresponsive." – Mark C. May 20 '14 at 20:50
  • It loads the table, but with no data. I've updated my [fiddle](http://jsfiddle.net/GentleMaN/3DRGw/1/) – Mark C. May 20 '14 at 21:04
0

Is your data format can be changed? The best way will be to get it from the server as needed, for example:

  { 
  "totalpages": "xxx", 
  "currpage": "yyy",
  "totalrecords": "zzz",
  "invdata" : [
    {"id" :"1", "cell" :["1.0", "23", "INSTITUTIONAL MONEY MARKET FUND", ...]},
    {"id" :"2", "cell" :["cell21", "cell22", "cell23"]},
      ...
  ]
}

if you can't - you still can change your data to some 'new_data' so it will be in the above format (taken from jqgrid wiki1

if you still choose to stay with your code - consider the following:

datastr - The string of data when datatype parameter is set to xmlstring or jsonstring

(taken from jqgrid wiki2) and your datatype is 'json' hope thats will help you..

ItayB
  • 10,377
  • 9
  • 50
  • 77
  • Well I would like for it to be dynamic (from database / live feed), that's why I'm not using static data. I did try the `datastr` method but it did not work for me, unfortunately. The grid loaded with no data. – Mark C. May 20 '14 at 21:08
  • who talked about static data? of course you want it to be dynamically changed.. so why not to use the 'url' and get from your server the above format? (like the simple demo examples). what do I miss? – ItayB May 20 '14 at 21:13
  • I'm not sure how to do what you are saying. Sorry. – Mark C. May 20 '14 at 21:15
0
loadonce: true,
jsonReader: {
repeatitems: false,
root: function (obj) { return obj; },
page: function (obj) { return grid.jqGrid('getGridParam', 'page'); },
total: function (obj) { return Math.ceil(obj.length / 
grid.jqGrid('getGridParam', 'rowNum')); },
records: function (obj) { return obj.length; }
},

add this aswell in your $("#grid").jqGrid call.

Aqib
  • 13
  • 1
  • 5