This question is locked. New answers and comments are not allowed.
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!!!
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!!!
3 Answers, 1 is accepted
0
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:
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.
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.
0
Andrea
Top achievements
Rank 2
Iron
answered on 27 Nov 2009, 04:59 PM
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:
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
[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
0
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:
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.
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"
) +
"'"
;
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.