- Home
- LLBLGen Pro
- Bugs & Issues
The conversion of a datetime data type to a smalldatetime data type resulted in an out-of-range value
Joined: 25-Oct-2005
A client gets this intermittent exception which we can't repro
System.Data.SqlClient.SqlException (0x80131904): The conversion of a datetime data type to a smalldatetime data type resulted in an out-of-range value.
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.SqlDataReader.TryHasMoreRows(Boolean& moreRows)
at System.Data.SqlClient.SqlDataReader.TryReadInternal(Boolean setTimeout, Boolean& more)
at System.Data.SqlClient.SqlDataReader.Read()
at Glimpse.Ado.AlternateType.GlimpseDbDataReader.Read()
at SD.LLBLGen.Pro.ORMSupportClasses.ProjectionUtils.FetchProjectionFromReader(List`1 valueProjectors, IGeneralDataProjector projector, IDataReader datasource, Int32 rowsToSkip, Int32 rowsToTake, Boolean clientSideLimitation, Boolean clientSideDistinctFiltering, Boolean clientSidePaging, UniqueList`1 stringCache, Dictionary`2 typeConvertersToRun, IRetrievalQuery queryExecuted)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, IRetrievalQuery queryToExecute, Dictionary`2 typeConvertersToRun)
at SD.LLBLGen.Pro.ORMSupportClasses.DataAccessAdapterCore.FetchProjection(List`1 valueProjectors, IGeneralDataProjector projector, QueryParameters parameters)
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProvider2.ExecuteValueListProjection(QueryExpression toExecute)
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProProviderBase.ExecuteExpression(Expression handledExpression, Type typeForPostProcessing)
at SD.LLBLGen.Pro.LinqSupportClasses.LLBLGenProQuery`1.System.Collections.Generic.IEnumerable<T>.GetEnumerator()
at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
at AQD.Model.HelperClasses.jqGrid.JqGridHelper.GetJsonDataForjqGrid[T](IQueryable`1 source, Int32 pageNum, Int32 pageSize, Int32 total, NameValueCollection queryString, Func`2 actionToApply)
at AQDPortal.MVC.Areas.FCA.Controllers.HomeController.SearchActionsGrid()
Don't really know where to look as there are no smalldatetimes anywhere. Also wondering why the SqlException isn't wrapped up in a ORMQueryExecutionException?
SQL when I run in locally
SELECT TOP(10 /* @p2 */) [LPA_L1].[ActionID],
[LPA_L1].[id],
[LPA_L1].[ActionTypeCode],
[LPA_L1].[ActionType],
[LPA_L1].[FindingNo],
[LPA_L1].[CauseNo],
[LPA_L1].[DueDateValue],
[LPA_L1].[ResponsibleDepartment],
[LPA_L1].[ResponsibleDepartmentId],
[LPA_L1].[ResponsibleOfficer],
[LPA_L1].[ActionStatusId],
[LPA_L1].[ActionStatus],
[LPA_L1].[Action],
[LPA_L1].[RegisteredBy],
[LPA_L1].[RegisteredByID],
[LPA_L1].[RegisteredOnValue],
[LPA_L1].[ActionResponseId],
[LPA_L1].[ActionResponse],
[LPA_L1].[ActionResponseText],
[LPA_L1].[TrackedById],
[LPA_L1].[TrackedBy],
[LPA_L1].[ClosedOnvalue],
[LPA_L1].[CompletedOnvalue],
[LPA_L1].[RecheckDueValue],
[LPA_L1].[AssignedTo],
[LPA_L1].[ReplacementActionID],
[LPA_L1].[OriginalDueDateValue],
[LPA_L1].[RiskNo],
[LPA_L1].[RiskCauseNo],
[LPA_L1].[LPFA_3],
[LPA_L1].[LPFA_4],
[LPA_L1].[FindingCategory],
[LPA_L1].[Severity],
[LPA_L1].[Likelihood],
[LPA_L1].[ExternalAuditRef],
[LPA_L1].[ExternalActionRef],
[LPA_L1].[OverDue],
[LPA_L1].[CheckedOutToWorkbench],
[LPA_L1].[Source]
FROM (SELECT DISTINCT [LPLA_1].[Action_ID] AS [ActionID],
[LPLA_1].[Action_ID_Sortable] AS [id],
[LPLA_1].[Action_Type_Code] AS [ActionTypeCode],
[LPLA_1].[Action_Type] AS [ActionType],
[LPLA_1].[Finding_No] AS [FindingNo],
[LPLA_1].[Cause_No] AS [CauseNo],
[LPLA_1].[Due_Date] AS [DueDateValue],
[LPLA_1].[Department] AS [ResponsibleDepartment],
[LPLA_1].[Responsible_Department] AS [ResponsibleDepartmentId],
[LPLA_1].[Assigned_To] AS [ResponsibleOfficer],
[LPLA_1].[Action_Status] AS [ActionStatusId],
[LPLA_1].[Action_Status_Text] AS [ActionStatus],
[LPLA_1].[Action],
[LPLA_1].[Registered_By_Name] AS [RegisteredBy],
[LPLA_1].[Registered_By_ID] AS [RegisteredByID],
[LPLA_1].[Registered_On] AS [RegisteredOnValue],
[LPLA_1].[Action_Response] AS [ActionResponseId],
[LPLA_1].[Action_Response_Description] AS [ActionResponse],
[LPLA_1].[Action_Response_Text] AS [ActionResponseText],
[LPLA_1].[To_Be_Tracked_By] AS [TrackedById],
[LPLA_1].[Tracked_By] AS [TrackedBy],
[LPLA_1].[Closed_On] AS [ClosedOnvalue],
[LPLA_1].[Completed_On] AS [CompletedOnvalue],
[LPLA_1].[Recheck_Due] AS [RecheckDueValue],
[LPLA_1].[Contact_Person] AS [AssignedTo],
[LPLA_1].[Replacement_Action_Id] AS [ReplacementActionID],
[LPLA_1].[Original_Due_Date] AS [OriginalDueDateValue],
[LPLA_1].[Risk_No] AS [RiskNo],
[LPLA_1].[Risk_Cause_No] AS [RiskCauseNo],
0 /* @p4 */ AS [LPFA_3],
0 /* @p6 */ AS [LPFA_4],
[LPLA_1].[Finding_Category] AS [FindingCategory],
[LPLA_1].[Severity],
[LPLA_1].[Likelihood],
[LPLA_1].[Ext_Audit_Reference] AS [ExternalAuditRef],
[LPLA_1].[Ext_Action_Reference] AS [ExternalActionRef],
[LPLA_1].[OverDue],
[LPLA_1].[CheckedOutTo] AS [CheckedOutToWorkbench],
[LPLA_1].[WRID] AS [Source]
FROM [AQD].[qaoc_Actn_ViewSecured] [LPLA_1]
WHERE (((([LPLA_1].[Staff_Member_ID] = 399 /* @p7 */))))) [LPA_L1]
ORDER BY [LPA_L1].[id] DESC
Version 4.2.16.0517
Any ideas?
Joined: 17-Aug-2003
About the exception not being wrapped: ORMQueryExecutionException is thrown when the SQL query throws an error. (so Command.Execute*Query). The error you run into occurs during the Datareader read, and those exceptions aren't wrapped. The error indeed looks like a Glimpse related issue, namely inside their own DbDataReader implementation.
Joined: 25-Oct-2005
We finally manage to repro with their database, the problem is in the view as simple
select * from [AQD].[qaoc_Actn_ViewSecured]
in SSMS gave Msg 242, Level 16, State 3, Line 1 The conversion of a datetime data type to a smalldatetime data type resulted in an out-of-range value.
And sure enough one field used a function which has smallDateTime params, which I missed when I first glanced at the view.
Sorry I bothered you, but I guess I can take away that if a database exception is not wrapped in a ORMQueryExecutionException then it is probably from a calculated field.
Joined: 17-Aug-2003
The execution of the query itself, if that fails (e.g. you do an update which violates an FK, you select from a table that doesn't exist, or an alias which is wrong) you'll get the ORMQueryExecutionException. If the query executes correct, but the consumption of the resultset fails, you'll get the raw exception that occurs. So in your case, the query itself succeeded, but consuming the resultset failed.