Advanced Searching between Dates

Jun 12, 2012 at 11:40 AM

Hello Tomasz,

thanks a lot for a wonderful helper for jqGrid. I'm using this helper to build a MVC app for reports. I'm very much new to jQuery and MVC. The requirement of the report is to be able to filter records based on DateRange. I tried to use Advanced Search example to build the report. However I'm stuck at two things.

1. how to get datepicker in the Advance search?

2. I tried passing string Date values (ex: '6/122012') with operators like 'less' I keep getting "The binary operator LessThan is not defined for the types 'System.Nullable`1[System.DateTime]' and 'System.DateTime'." exception.

Can you please point me to an example where it shows how to implement DATE Range searches correctly?

 

Thanks

Ranganath

Coordinator
Jun 12, 2012 at 3:02 PM

Hi Ranganath,

Ad 1. You should use DataInit property of JqGridColumnSearchableAttribute:

[JqGridColumnSearchable(true, DataInit = "function(el) { setTimeout(function() { $(el).datepicker(); }, 200); }")]

Ad 2. The solution to this depends on what you are using as your database access technology (this is not related in any way to helper itself), but in general you can't compare DateTime and String in .Net, both objects needs to be DateTime. For example creating rule for Dynamic Linq Extensions might look like this:

String.Format("ShippedDate >= Convert.ToDateTime(\"{0}\")", "05/01/1998")

 

Regards,
Tomasz

Jun 13, 2012 at 3:48 PM
Edited Jun 13, 2012 at 3:49 PM

Thanks a ton Tomasz!!

DatePicker setting worked like a charm

I'm using EF to SQL Server 2008 R2. I incorporated your suggested way for formatting Dynamic Linq Extensions, but I encountered the following exception -

"LINQ to Entities does not recognize the method 'System.DateTime ToDateTime(System.String)' method, and this method cannot be translated into a store expression."

I googled and found this is something not supported in Linq To Entites.

Wonder if there are any workarounds available to get this working? Or should I write stored procedures?

Regards,
Ranganath