Object Provider and OQL using a date WHERE

4 posts, 0 answers
  1. Ryan
    Ryan avatar
    10 posts
    Member since:
    May 2008

    Posted 09 Sep 2009 Link to this post

    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!!!

  2. PetarP
    Admin
    PetarP avatar
    754 posts

    Posted 11 Sep 2009 Link to this post

    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.
  3. DevCraft banner
  4. Andrea
    Andrea avatar
    64 posts
    Member since:
    Jan 2005

    Posted 27 Nov 2009 Link to this post

    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

  5. Damyan Bogoev
    Admin
    Damyan Bogoev avatar
    581 posts

    Posted 30 Nov 2009 Link to this post

    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.
Back to Top