?
Solved

jqgrid sorting in vb.net mvc app

Posted on 2011-10-27
21
Medium Priority
?
892 Views
Last Modified: 2012-05-12
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!    
0
Comment
Question by:ttist25
  • 11
  • 10
21 Comments
 
LVL 9

Expert Comment

by:Evan Cutler
ID: 37040508
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
 
LVL 1

Author Comment

by:ttist25
ID: 37040787
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
 
LVL 9

Expert Comment

by:Evan Cutler
ID: 37041025
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
LVL 1

Author Comment

by:ttist25
ID: 37041226
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
 
LVL 9

Expert Comment

by:Evan Cutler
ID: 37041266
ok...gotcha...what's the URL to your page?
0
 
LVL 1

Author Comment

by:ttist25
ID: 37041430
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
 
LVL 9

Expert Comment

by:Evan Cutler
ID: 37041502
ok...did you try the haaacked version? did it work?
0
 
LVL 9

Expert Comment

by:Evan Cutler
ID: 37041508
replace Questions with Students_ts
and try that script...
0
 
LVL 9

Expert Comment

by:Evan Cutler
ID: 37041516
0
 
LVL 1

Author Comment

by:ttist25
ID: 37041639
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
 
LVL 9

Expert Comment

by:Evan Cutler
ID: 37041665
ok...you challenged me...
does your paging work?
0
 
LVL 1

Author Comment

by:ttist25
ID: 37041784
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
 
LVL 9

Expert Comment

by:Evan Cutler
ID: 37041818
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
 
LVL 1

Author Comment

by:ttist25
ID: 37042067
I'm making you a copy of my project so I can remove any identifying info.  I'll upload it here soon.
0
 
LVL 1

Author Comment

by:ttist25
ID: 37042168
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
 
LVL 1

Author Comment

by:ttist25
ID: 37042170
Shoot!  

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

That should do it.  
0
 
LVL 9

Expert Comment

by:Evan Cutler
ID: 37042234
Ok.  Give me some time to .look...Brb
0
 
LVL 9

Assisted Solution

by:Evan Cutler
Evan Cutler earned 2000 total points
ID: 37045123
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
 
LVL 1

Accepted Solution

by:
ttist25 earned 0 total points
ID: 37045404
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
 
LVL 1

Author Comment

by:ttist25
ID: 37057692
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
 
LVL 1

Author Closing Comment

by:ttist25
ID: 37087281
Thanks man.
0

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just a quick little trick I learned recently.  Now that I'm using jQuery with abandon in my asp.net applications, I have grown tired of the following syntax:      (CODE) I suppose it just offends my sense of decency to put inline VBScript on a…
Today I had a very interesting conundrum that had to get solved quickly. Needless to say, it wasn't resolved quickly because when we needed it we were very rushed, but as soon as the conference call was over and I took a step back I saw the correct …
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…
Suggested Courses

750 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question