jqgrid sorting in vb.net mvc app

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:
var questions = context.Questions
    .OrderBy(sidx + " " + sord)
    .Skip(pageIndex * pageSize)
    .Take(pageSize);

Open in new window


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

Open in new window


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!    
LVL 1
ttist25Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Evan CutlerVolunteer Chief Information OfficerCommented:
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
0
ttist25Author Commented:
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
0
Evan CutlerVolunteer Chief Information OfficerCommented:
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.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

ttist25Author Commented:
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

        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

Open in new window


and this is the script from my Index.aspx:
    <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>  

Open in new window


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

Open in new window



This is the C# code from Phil Haack's example that's supposed to allow for sorting:
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);
}

Open in new window


I hope somewhere in there is what you were looking for.  Sorry for being so dense.  :)
0
Evan CutlerVolunteer Chief Information OfficerCommented:
ok...gotcha...what's the URL to your page?
0
ttist25Author Commented:
The Index.aspx that holds the grid is in my /Views/Grid/ folder.  Here's my route in Global.asax.vb:

        routes.MapRoute( _
             "Default", _
             "{controller}/{action}/{id}", _
             New With {.controller = "Grid", .action = "Index", .id = UrlParameter.Optional} _
         )

Open in new window

0
Evan CutlerVolunteer Chief Information OfficerCommented:
ok...did you try the haaacked version? did it work?
0
Evan CutlerVolunteer Chief Information OfficerCommented:
replace Questions with Students_ts
and try that script...
0
Evan CutlerVolunteer Chief Information OfficerCommented:
0
ttist25Author Commented:
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.  

0
Evan CutlerVolunteer Chief Information OfficerCommented:
ok...you challenged me...
does your paging work?
0
ttist25Author Commented:
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.  
0
Evan CutlerVolunteer Chief Information OfficerCommented:
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

0
ttist25Author Commented:
I'm making you a copy of my project so I can remove any identifying info.  I'll upload it here soon.
0
ttist25Author Commented:
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.
0
ttist25Author Commented:
Shoot!  

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

That should do it.  
0
Evan CutlerVolunteer Chief Information OfficerCommented:
Ok.  Give me some time to .look...Brb
0
Evan CutlerVolunteer Chief Information OfficerCommented:
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?
0
ttist25Author Commented:
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:
Dim students = context.Students_ts.OrderBy(sidx & " " & sord).Skip(pageIndex * pageSize).Take(pageSize)

Open in new window




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.  
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ttist25Author Commented:
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!
0
ttist25Author Commented:
Thanks man.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.