Object Provider and OQL using a date WHERE

[Ryan]

I have a dataform with a ObjectProvider and an ObjectView.  I am trying to do a select using a where clause (OQLStatement).  How do you use a date value in a OQL statement?

I have a RadTextBox dfDate.

DateTime dt = Convert.ToDate(dfDate.text);
objectProvider1.OQLStatement = "SELECT * FROM MuniBookingExtent AS x WHERE bookdate = " + dt;

The above does not work.  If anyone has any ideas, please let me know.  I am new to OQL but have used SQL for year!!!

[PetarP]

Hello Ryan,

To be able to use dates in your OQL queries Telerik OpenAccess ORM requires the key word timestamp to be used. Also please note that a special format of the date is required. Here is your code with the required modifications:

            DateTime dt = DateTime.Parse(dfDate.text);
            objectProvider1.OQLStatement = "SELECT * FROM MuniBookingExtent AS x WHERE bookdate = timestamp '" + dt.Date.ToString("yyyy'-'MM'-'dd'T'HH':'mm':'ss.fff") + "'";

Best wishes,
Petar
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.

[Andrea]

With latest OA version (1119) if I use the date format provided by Petar (yyyy'-'MM'-'dd'T'HH':'mm':'ss.fff) I receive an error:

[SQLException: La conversione di un tipo di dati varchar in datetime ha generato un valore non compreso nell'intervallo dei valori consentiti.]
   Telerik.OpenAccess.RT.Adonet2Generic.Impl.ResultSetImp.next() +161
   OpenAccessRuntime.Relational.fetch.FetchResultImp.rsNext() +25
   OpenAccessRuntime.Relational.fetch.FetchResultImp.hasNext() +77

[DataStoreException: Telerik.OpenAccess.RT.sql.SQLException: La conversione di un tipo di dati varchar in datetime ha generato un valore non compreso nell'intervallo dei valori consentiti.
   in Telerik.OpenAccess.RT.Adonet2Generic.Impl.ResultSetImp.next()
   in OpenAccessRuntime.Relational.fetch.FetchResultImp.rsNext()
   in OpenAccessRuntime.Relational.fetch.FetchResultImp.hasNext() ]
   Telerik.OpenAccess.SPI.Backends.ThrowException(Exception e) +4
   Telerik.OpenAccess.RT.ExceptionWrapper.Throw() +14
   OpenAccessRuntime.storagemanager.logging.LoggingStorageManager.fetchNextQueryResult(ApplicationContext context, RunningQuery runningQuery, Int32 skipAmount) +223
   OpenAccessRuntime.DataObjects.UnsynchronizedPMProxy.getNextQueryResult(QueryResultWrapper aQrs, Int32 skipAmount) +44
   OpenAccessRuntime.DataObjects.ForwardQueryResult.get_Item(Int32 indexParam) +256
   Telerik.OpenAccess.RT.ListEnumerator.setCurrent(Int32 _pos) +43
   Telerik.OpenAccess.RT.ListEnumerator.Move(Int32 relative) +100
   Telerik.OpenAccess.RT.ListEnumerator.MoveNext() +10
   Telerik.OpenAccess.RT.DataSource.Enumerator`1.System.Collections.IEnumerator.MoveNext() +13
   Telerik.Web.UI.GridResolveEnumerable.GetCollectionItemType(Boolean noItemsInEnumerator, Type& collectionItemType, Object& collectionFirstObject) +210
   Telerik.Web.UI.GridResolveEnumerable.ParseProperties() +90
   Telerik.Web.UI.GridResolveEnumerable.Initialize() +13
   Telerik.Web.UI.GridResolveEnumerable.EnsureInitialized() +20
   Telerik.Web.UI.GridEnumerableFromDataView..ctor(GridTableView owner, IEnumerable enumerable, Boolean CaseSensitive, Boolean autoGenerateColumns, GridColumnCollection presentColumns, String[] additionalField, Boolean retrieveAllFields) +158
   Telerik.Web.UI.GridDataSourceHelper.CreateGridEnumerable(GridTableView owner, IEnumerable enumerable, Boolean caseSensitive, Boolean autoGenerateColumns, GridColumnCollection presentColumns, String[] additionalField, Boolean retrieveAllFields) +129
   Telerik.Web.UI.GridDataSourceHelper.GetResolvedDataSource(GridTableView owner, Object dataSource, String dataMember, Boolean caseSensitive, Boolean autoGenerateColumns, GridColumnCollection presentColumns, String[] additionalField, Boolean retrieveAllFields) +383
   Telerik.Web.UI.GridTableView.get_ResolvedDataSource() +140
   Telerik.Web.UI.GridTableView.CreateControlHierarchy(Boolean useDataSource) +309
   Telerik.Web.UI.GridTableView.CreateChildControls(IEnumerable dataSource, Boolean useDataSource) +492
   System.Web.UI.WebControls.CompositeDataBoundControl.PerformDataBinding(IEnumerable data) +57
   System.Web.UI.WebControls.DataBoundControl.OnDataSourceViewSelectCallback(IEnumerable data) +114
   System.Web.UI.DataSourceView.Select(DataSourceSelectArguments arguments, DataSourceViewSelectCallback callback) +31
   System.Web.UI.WebControls.DataBoundControl.PerformSelect() +142
   Telerik.Web.UI.GridTableView.PerformSelect() +4
   System.Web.UI.WebControls.BaseDataBoundControl.DataBind() +73
   Telerik.Web.UI.GridTableView.DataBind() +221
   Telerik.Web.UI.RadGrid.DataBind() +80
   System.Web.UI.WebControls.BaseDataBoundControl.EnsureDataBound() +82
   System.Web.UI.WebControls.CompositeDataBoundControl.CreateChildControls() +72
   System.Web.UI.Control.EnsureChildControls() +87
   System.Web.UI.Control.FindControl(String id, Int32 pathOffset) +23
   System.Web.UI.Control.FindControl(String id) +12
   Telerik.Web.ChildControlHelper.FindControlRecursive(String ID, Control root) +182
   Telerik.Web.ChildControlHelper.FindControlRecursive(String ID, Control root) +227
   Telerik.Web.ChildControlHelper.FindControlRecursive(String ID, Control root) +227
   Telerik.Web.ChildControlHelper.FindControlRecursive(String ID, Control root) +227
   Telerik.Web.ChildControlHelper.FindControlRecursive(Control searcher, String ID) +186
   Telerik.Web.UI.RadAjaxControl.OnPagePreRender(Object sender, EventArgs e) +1085
   System.EventHandler.Invoke(Object sender, EventArgs e) +0
   System.Web.UI.Control.OnPreRender(EventArgs e) +8684102
   System.Web.UI.Adapters.ControlAdapter.OnPreRender(EventArgs e) +15
   System.Web.UI.Control.PreRenderRecursiveInternal() +8684029
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +842

In english: the conversion of varchar data to datetime generates a value out of range.

Before upgrade the string format worked! I hope isnt the old date format problem that I see since ACCESS + ASP (a disadvantage of being Italian).

Thanks

[Damyan Bogoev]

Hello Andrea,

This is a bug caused by the changes in the OQL parser we did regarding the culture specific dependencies. It is already fixed and will be available in the next build.
You could avoid the error by replacing the ‘T’  with a space symbol in the string:

DateTime dt = DateTime.Parse(dfDate.text);
objectProvider1.OQLStatement = "SELECT * FROM MuniBookingExtent AS x WHERE bookdate = timestamp '" + dt.Date.ToString("yyyy'-'MM'-'dd HH':'mm':'ss.fff") + "'";

When the fix is available both formats will work.
Hope that helps.

Greetings,
Damyan Bogoev
the Telerik team

Instantly find answers to your questions on the new Telerik Support Portal.
Watch a video on how to optimize your support resource searches and check out more tips on the blogs.