I get a SqlDateTime overflow that is dependent on the order I do queries on the same tables. The individual queries don't have the problem.

I have a query of a table that has a two many-to-one links to other tables, one of which has two many-to-one links to other tables.

I have two queries that each return one record transaction record that links to one security record.

If I execute each of those queries (and print some result) I have no issues, but if I execute one after the other I get the sqldatetime error.

All my DateTime definitions for the tables in question are DateTime? as the fields can accept nulls and often are.

The line that gets the exception is the second foreach statement.

Here are the queries:

IQuery query8 = session.CreateQuery("select a from DB_Transactions a where a.ID = :id ");
query8.SetString("id", "192524");

foreach (BDM_Controller.Source.ORM.DB_Transactions trnRec in query8.Enumerable())
{
writer.WriteLine(" id: " + trnRec.ID + " tcRec: " + trnRec.RecordEntryNumber + " acctN: " + trnRec.AccountNumber + " TaxCode: " + trnRec.TransactionAndTaxCode.Description + " secDesc: " + ((trnRec.Security != null) ? trnRec.Security.SecurityDescription : "n/a"));
}

IQuery query8b = session.CreateQuery("select a from DB_Transactions a where a.ID = :id ");
query8b.SetString("id", "180639");

foreach (BDM_Controller.Source.ORM.DB_Transactions trnRec2 in query8b.Enumerable())
{
writer.WriteLine(" id: " + trnRec2.ID + " tcRec: " + trnRec2.RecordEntryNumber + " acctN: " + trnRec2.AccountNumber + " TaxCode: " + trnRec2.TransactionAndTaxCode.Description + " secDesc: " + ((trnRec2.Security != null) ? trnRec2.Security.SecurityDescription : "n/a"));
}


Here is the error:



System.Data.SqlTypes.SqlTypeException: SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
at System.Data.SqlTypes.SqlDateTime.FromTimeSpan(TimeSpan value)
at System.Data.SqlTypes.SqlDateTime.FromDateTime(DateTime value)
at System.Data.SqlClient.MetaType.FromDateTime(DateTime dateTime, Byte cb)
at System.Data.SqlClient.TdsParser.WriteValue(Object value, MetaType type, Byte scale, Int32 actualLength, Int32 encodingByteSize, Int32 offset, TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.TdsExecuteRPC(_SqlRPC[] rpcArray, Int32 timeout, Boolean inSchema, SqlNotificationRequest notificationRequest, TdsParserStateObject stateObj, Boolean isCommandProc)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe)
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
at NHibernate.AdoNet.AbstractBatcher.ExecuteNonQuery(IDbCommand cmd)
at NHibernate.AdoNet.NonBatchingBatcher.AddToBatch(IExpectation expectation)
at NHibernate.Persister.Entity.AbstractEntityPersister.Update(Object id, Object[] fields, Object[] oldFields, Object rowId, Boolean[] includeProperty, Int32 j, Object oldVersion, Object obj, SqlCommandInfo sql, ISessionImplementor session)
at NHibernate.Persister.Entity.AbstractEntityPersister.UpdateOrInsert(Object id, Object[] fields, Object[] oldFields, Object rowId, Boolean[] includeProperty, Int32 j, Object oldVersion, Object obj, SqlCommandInfo sql, ISessionImplementor session)
at NHibernate.Persister.Entity.AbstractEntityPersister.Update(Object id, Object[] fields, Int32[] dirtyFields, Boolean hasDirtyCollection, Object[] oldFields, Object oldVersion, Object obj, Object rowId, ISessionImplementor session)
at NHibernate.Action.EntityUpdateAction.Execute()
at NHibernate.Engine.ActionQueue.Execute(IExecutable executable)
at NHibernate.Engine.ActionQueue.ExecuteActions(IList list)
at NHibernate.Engine.ActionQueue.ExecuteActions()
at NHibernate.Event.Default.AbstractFlushingEventListener.PerformExecutions(IEventSource session)
at NHibernate.Event.Default.DefaultAutoFlushEventListener.OnAutoFlush(AutoFlushEvent event)
at NHibernate.Impl.SessionImpl.AutoFlushIfRequired(ISet`1 querySpaces)
at NHibernate.Impl.SessionImpl.Enumerable(String query, QueryParameters queryParameters)
at NHibernate.Impl.QueryImpl.Enumerable()
at BDM_Controller.SchwabProvider.transformAccountFile(CustomersDataTable customersDataTable) in C:\VisualStudioApps\BDM\Source\BDM Controller\Providers\Schwab\SchwabProvider.cs:line 235
at BDM_Controller.SchwabProvider.transformFiles(DataSet ds) in C:\VisualStudioApps\BDM\Source\BDM Controller\Providers\Schwab\SchwabProvider.cs:line 87
at BDM_Controller.Controller.Run(String[] args) in C:\VisualStudioApps\BDM\Source\BDM Controller\Controller.cs:line 49
at BDM_Controller.Entry.Main(String[] args) in C:\VisualStudioApps\BDM\Source\BDM Controller\Entry.cs:line 28


Why would the order be an issue? Is there something to clear out some state needed between the two? Is there some other protection to be done for DateTime that are null? (assuming that is the problem as opposed to a bad data in the DB)