Friday, March 16, 2012

SqlDateTime overflow Exception with NHibernate

SqlDateTime overflow. Must be between 1/1/1753 12:00:00 AM and 12/31/9999 11:59:59 PM.
Exception Stacktrace:
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)
What is NOT a problem:
As always, I opned SQL Profiler and tried solve the issue but it misguided me by adding few extra zeros in millisecond part of datetime (I don't want to discuss it and it is not the problem).

What IS (or was in my case) problem:
I added an extra Datetime filed which was nullable in database. But I was mapping it to DateTime in my database. When NHibernate was converting my unset Datetime to SQLdatetime for mysterious reason, it was trying to set DateTime.Min in SQL datetime datatype which is not allowed (see exception).

Solution:
Look for any Datetime in your mappings that should be nullabe (according to database). And then use
type="System.Nullable`1[[System.DateTime, mscorlib]], mscorlib" in your mapping.

4 comments:

Gabriel Fuller said...

Thank you so much! This helped me figure out my problem. I had a few DateTime field discrepancies between my Domain Class code, NHibernateMap and Database. Got them synced up and now all it well! :-) Thanks!

Yogee said...

Gabriel, I am glad you was able to solve the issue. Some problems are too weird to identify even though the solution is easy.

Venugopal Reddy said...

I have some solution.Plz try
Call function fixTableHeader by passing table id.

function tableScrollerParams () {

this.lastScrollTop = 0;
this.lastScrollLeft = 0;
}

tableScrollerParams.prototype.setLastScrollTop = function(lastScrollTop) { this.lastScrollTop = lastScrollTop;};
tableScrollerParams.prototype.getLastScrollTop = function(lastScrollTop) { return this.lastScrollTop;};

tableScrollerParams.prototype.setLastScrollLeft = function(lastScrollLeft) { this.lastScrollLeft = lastScrollLeft;};
tableScrollerParams.prototype.getLastScrollLeft = function(lastScrollLeft) { return this.lastScrollLeft;};


tableScrollerParams = new tableScrollerParams();
function fixTableHeader(id)
{
(function() {
var timer;
var scrollerId='#'+id+'_scroller';
$( scrollerId ).scroll(function() {
clearTimeout(timer);
timer = setTimeout( refresh , 150 );
});
var refresh = function () {
handleScrollEvent(id);
};
})();
}

function handleScrollEvent(id)
{

var table=document.getElementById(id);
var scroller = document.getElementById(id+"_scroller");

var rowLength = table.rows.length;
if(scroller.scrollLeft!=tableScrollerParams.getLastScrollLeft()){//On Horizontal Scroll
tableScrollerParams.setLastScrollLeft(scroller.scrollLeft);
}else if(scroller.scrollTop!=tableScrollerParams.getLastScrollTop()){//On Vertical Scroll
var headerRows = new Array();
var k=0;
for(var i=0; i=1)
insertTableHeader(id,headerRows);

tableScrollerParams.setLastScrollTop(scroller.scrollTop);
}//On Vertical Scroll end

}
function insertTableHeader(id,headerRows)
{
var table=document.getElementById(id);
var scroller = document.getElementById(id+"_scroller");
var rowLength = table.rows.length;

for(var i=0; iscroller.scrollTop){
var headerRowsLength = headerRows.length-1;
var row = table.rows[i+headerRowsLength];
insertAfter(row, headerRows);
scroller.scrollTop = headerRows[0].offsetTop;
break;
}
}//for loop end
}
function insertAfter(referenceNode, headerRows) {
for(var i=0; i=0; i-=1){
insertRowBefore(referenceNode, headerRows[i]);
referenceNode = headerRows[i];
}
}
function insertRowAfter(referenceNode, newNode) {
referenceNode.parentNode.insertBefore(newNode, referenceNode.nextSibling);
}
function insertRowBefore(referenceNode, newNode) {
referenceNode.parentNode.insertBefore(newNode, referenceNode);
}

Yogee said...

Venugopal, Your comment is out of scope of this post. Can you post at right place and provide more information on what you are trying to do.