Clicky

Good Morning,

I'm working on implementing the jqgrid jquery plugin in my vb.net mvc 2 application.  I've got it pulling in data (thanks to experts vbigham and CodeCruiser) from my ms sql database using JSON  based on the tutorial here.  The next issue i need to deal with is getting sorting to work.  Clicking on the column headers of my grid brings the "loading..." dialog up and it looks like it's trying to do something but then it stops and the data is in the original sort order.  

The example I'm working from is in C# but I need to work in vb.net.  I'm having trouble with the following line of code in the example:

Here is the C# version:
1:
2:
3:
4:
var questions = context.Questions
    .OrderBy(sidx + " " + sord)
    .Skip(pageIndex * pageSize)
    .Take(pageSize);


and the converted version (I did this with an online C# to VB.NET convertor):
1:
Dim students = context.Students_ts.OrderBy(sidx & " " & sord).Skip(pageIndex * pageSize).Take(pageSize)


The error in visual studio is as follows:
"Overload resolution failed because no accessible 'OrderBy' can be called with these arguments: Extension method 'Public Function OrderBy(Of TKey)(keySelector As System.Linq.Expressions.Expression(Of System.Func(Of Students_t, TKey))) (and on and on ad nauseam) Specifying the data types explicitly might correct this error."  

I found that if I change the OrderBy to "OrderBy(sidx& " " &sord)" (removing spaces) it reduces the error in Visual Studio to:
"Comma, ')', or valid expression continuation expected."

and when I mouse over the sidx& it shows "Dim sidx As string" which I'm thinking is a good step in the right direction?

Obviously I don't know what the heck I'm doing but I'm trying to help move this project forward so I've taken on this piece.  :)

Any ideas what I can do to resolve this?  When I've been researching I see people making statements such as "I switched from JSON to XML and everything just worked".  Is that a direction you'd recommend?  

Any help you can provide, as always, will be greatly appreciated.  

Thanks!    

asked 10/27/2011 04:14

ttist25's gravatar image

ttist25 ♦♦


21 Answers:
You <operative word> CAN </operative word> do client side coding to do the sorting as perscribed in http://stackoverflow.com/questions/2131280/jqgrid-sorting-on-client-side

but that's assuming ALL of your data is already on the client.

If not, then you have to do your VB/C# scripts to SQL to be sorted from your JQUERY execution.
If you want that, could you please show me your entire page script?

Thanks
link
arcee123's gravatar image

arcee123

First, thanks for your help.  this has been making me insane so I really appreciate the help.  

As far as having all the data at the client, I tried to load my entire dataset in one of my first attempts but I got an error about character limits (I think from JSON) so I truncated my table down to 50 records.  

In any case, I'm at a doctors appt with my son but will definitely post anything you'd like.  when you say "page script", do you mean my controller or do you need the view and the controller?

thanks again
link
ttist25's gravatar image

ttist25

no, I meant the JQUERY call to instatiate the JQGRID....
But character limits are not table limits...they are field limits...
your JSON field lengths and JQGRID field lengths call don't match.

let's look at your html page with the JQGRID when you return.
link
arcee123's gravatar image

arcee123

Ok - I'm still a little confused but I think this is what you're looking for (again - I'm a real hack).  Here's what is in my GridController.vb

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
Public Function LinqGridData(ByVal sidx As String, ByVal sord As String, ByVal page As Integer, ByVal rows As Integer) As ActionResult
            Dim context As New GridDataContext
            Dim jsonData = New With { _
                .total = 10, _
                .page = page, _
                .records = context.Students_ts.Count(), _
                .rows = (From p In context.Students_ts _
                    Select New With {.id = p.id, .cell = {p.FIRST, p.LAST, p.INIT}}).ToArray()}

            Return Json(jsonData, JsonRequestBehavior.AllowGet)
        End Function


and this is the script from my Index.aspx:
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
<script type="text/javascript">
        jQuery(document).ready(function () {
            jQuery("#list").jqGrid({
                url: '/Grid/LinqGridData/',
                datatype: 'json',
                mtype: 'Get',
                colNames: ['Last', 'First', 'MI'],
                colModel: [
          { name: 'Last', index: 'LAST', width: 150, sort: 'true', align: 'left' },
          { name: 'First', index: 'FIRST', width: 150, align: 'left' },
          { name: 'MI', index: 'INIT', width: 20, align: 'left' }],

                pager: jQuery('#pager'),
                rowNum: 10,
                rowList: [5, 10, 20, 50],
                sortname: 'LAST',
                sortorder: "asc",
                viewrecords: true,
                imgpath: '',
                caption: 'Search Results'
            });
            jQuery("#list").jqGrid('navGrid', '#pager', { edit: true, add: true, del: true }); 
        }); 
    </script>


Then the grid is called with this little bit:
1:
2:
<table id="list" class="scroll" cellpadding="0" cellspacing="0"></table>
        <div id="pager" class="scroll" style="text-align:center;"></div>


This is the C# code from Phil Haack's example that's supposed to allow for sorting:
1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
public ActionResult DynamicGridData
    (string sidx, string sord, int page, int rows) {
  var context = new HaackOverflowDataContext();
  int pageIndex = Convert.ToInt32(page) - 1;
  int pageSize = rows;
  int totalRecords = context.Questions.Count();
  int totalPages = (int)Math.Ceiling((float)totalRecords / (float)pageSize);

  var questions = context.Questions
    .OrderBy(sidx + " " + sord)
    .Skip(pageIndex * pageSize)
    .Take(pageSize);

  var jsonData = new {
    total = totalPages,
    page = page,
    records = totalRecords,
    rows = (
      from question in questions
      select new {
        id = question.Id,
        cell = new string[] {
          question.Id.ToString(), question.Votes.ToString(), question.Title 
        }
    }).ToArray()
  };
  return Json(jsonData, JsonRequestBehavior.AllowGet);
}


I hope somewhere in there is what you were looking for.  Sorry for being so dense.  :)
link
ttist25's gravatar image

ttist25

ok...gotcha...what's the URL to your page?
link
arcee123's gravatar image

arcee123

The Index.aspx that holds the grid is in my /Views/Grid/ folder.  Here's my route in Global.asax.vb:

1:
2:
3:
4:
5:
routes.MapRoute( _
             "Default", _
             "{controller}/{action}/{id}", _
             New With {.controller = "Grid", .action = "Index", .id = UrlParameter.Optional} _
         )
link
ttist25's gravatar image

ttist25

ok...did you try the haaacked version? did it work?
link
arcee123's gravatar image

arcee123

replace Questions with Students_ts
and try that script...
link
arcee123's gravatar image

arcee123

well - my script works.  It gets data from my database but the sorting and paging don't work.  

I did download the haacked example and ran it but it doesn't load data when I use /Home/LinqDataGrid or /Home/DynamicDataGrid and I'm not sure why that won't run.  When I first ran it it said I needed to upgrade it because I'm using 2010 and I think it's made in 2008 plus the .net version was upgraded but there were no errors.  

Anyway - while I was looking back over our comments I think I might not have been clear.  My vb works in that it loads data from my Students_t table (after I cut the record set down from 60k to 50 records) but I can't get the sorting and paging to work.  

I had used the haacked example to get this far and I was hoping that I could continue down that path to implement the sorting but I ran into the difficulty translating from C# to VB.NET so I haven't been able to move forward.  

Thanks again for being so responsive.  

link
ttist25's gravatar image

ttist25

ok...you challenged me...
does your paging work?
link
arcee123's gravatar image

arcee123

no - the paging and the sorting don't work but I can get the first page of records in from my table.  If I try to page through or sort it doesn't work.  
link
ttist25's gravatar image

ttist25

I know what the problem is.  I its in your linq associations.
Somethings wrong with how you modified your haacked statements.  Show me your version

link
arcee123's gravatar image

arcee123

I'm making you a copy of my project so I can remove any identifying info.  I'll upload it here soon.
link
ttist25's gravatar image

ttist25

Ok - here's an example of what I've got so far.  It's got 15 records so we can test paging.  This only goes up to the "LinqGridData" part of Haack's example.  

The last part that has the sorting "DynamicGridData" is where I got tripped up.  

Hope this helps.  you're really going above and beyond.  Thanks!

EE blocked the file upload because of the .suo file - you can grab it here.
link
ttist25's gravatar image

ttist25

Shoot!  

http://www.kntnetworks.com/ForEE.zip

That should do it.  
link
ttist25's gravatar image

ttist25

Ok.  Give me some time to .look...Brb
link
arcee123's gravatar image

arcee123

Sorry it's taking me so long, I dont' have VS2010 with me right now...
I'm looking at your GridController.vb file,
and I dont' see

  var questions = context.Questions
    .OrderBy(sidx + " " + sord)
    .Skip(pageIndex * pageSize)
    .Take(pageSize);

what happened to it?
link
arcee123's gravatar image

arcee123

Hey - no problem.  Thanks for taking the time.  

If you look back at the original post, I was trying to get that piece implemented but couldn't get the conversion to vb.net setup.  The line I was trying to get straight was:
1:
Dim students = context.Students_ts.OrderBy(sidx & " " & sord).Skip(pageIndex * pageSize).Take(pageSize)



The error in visual studio is as follows:
"Overload resolution failed because no accessible 'OrderBy' can be called with these arguments: Extension method 'Public Function OrderBy(Of TKey)(keySelector As System.Linq.Expressions.Expression(Of System.Func(Of Students_t, TKey))) (and on and on ad nauseam) Specifying the data types explicitly might correct this error."  

I found that if I change the OrderBy to "OrderBy(sidx& " " &sord)" (removing spaces) it reduces the error in Visual Studio to:
"Comma, ')', or valid expression continuation expected."

and when I mouse over the sidx& it shows "Dim sidx As string" which I'm thinking is a good step in the right direction?


So - I've been trying to implement that "step" of the tutorial in VB.NET and that's where I've failed thus far.  
link
ttist25's gravatar image

ttist25

Woo Hoo!!!!  I've got it!  

A fully working ASP.NET, MVC, VB.NET, JSON, version of the jQGrid!  

You can get it here:
Yay

Ahhhhhhh...

Ok on to the next!
link
ttist25's gravatar image

ttist25

Thanks man.
link
ttist25's gravatar image

ttist25

Your answer
[hide preview]

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Tags:

Asked: 10/27/2011 04:14

Seen: 714 times

Last updated: 11/05/2011 01:17