I am working on an ASP.NET MVC project with
NHibernate as the backend and am having some trouble getting some dates
to write back
to my SQL Server database tables.
These date fields are NOT nullable, so the many answers here about how
to setup nullable datetimes have not helped.
Basically when I try
to save the entity which has a DateAdded and a LastUpdated fields, I am getting a SqlDateTime overflow exception. I have had a similar problem in the past where I was trying
to write a datetime field into a smalldatetime column, updating the type on the column appeared
to fix the problem. My gut feeling is that its going
to be some problem with the table definition or some type of incompatible data types, and the overflow exception is a bit of a bum steer.
I have attached an example of the table definition and the query that
NHibernate is trying
to run, any help or suggestions would be greatly appreciated.
CREATE TABLE [dbo].[CustomPages](
[ID] [uniqueidentifier] NOT NULL,
[StoreID] [uniqueidentifier] NOT NULL,
[DateAdded] [datetime] NOT NULL,
[AddedByID] [uniqueidentifier] NOT NULL,
[LastUpdated] [datetime] NOT NULL,
[LastUpdatedByID] [uniqueidentifier] NOT NULL,
[Title] [nvarchar](150) NOT NULL,
[Term] [nvarchar](150) NOT NULL,
[Content] [ntext] NULL
)
exec sp_executesql N'INSERT INTO CustomPages (Title, Term, Content, LastUpdated, DateAdded, StoreID, LastUpdatedById, AddedById, ID) VALUES (@p0, @p1, @p2, @p3, @p4, @p5, @p6, @p7, @p8)',N'@p0
nvarchar(21),@p1 nvarchar(21),@p2 nvarchar(33),@p3 datetime,@p4 datetime,@p5 uniqueidentifier,@p6 uniqueidentifier,@p7 uniqueidentifier,@p8 uniqueidentifier',@p0=N'Size and Colour
Chart',@p1=N'size-and-colour-chart',@p2=N'This is the size and colour chart',@p3=''2009-03-14 14:29:37:000'',@p4=''2009-03-14
14:29:37:000'',@p5='48315F9F-0E00-4654-A2C0-62FB466E529D',@p6='1480221A-605A-4D72-B0E5-E1FE72C5D43C',@p7='1480221A-605A-4D72-B0E5-E1FE72C5D43C',@p8='1E421F9E-9A00-49CF-9180-DCD22FCE7F55'
In response the the answers/comments, I am using Fluent
NHibernate and the generated mapping is below
public CustomPageMap() {
WithTable("CustomPages");
Id( x => x.ID, "ID" )
.WithUnsavedValue(Guid.Empty)
. GeneratedBy.Guid();
References(x => x.Store, "StoreID");
Map(x => x.DateAdded, "DateAdded");
References(x => x.AddedBy, "AddedById");
Map(x => x.LastUpdated, "LastUpdated");
References(x => x.LastUpdatedBy, "LastUpdatedById");
Map(x => x.Title, "Title");
Map(x => x.Term, "Term");
Map(x => x.Content, "Content");
}
<?xml version="1.0" encoding="utf-8"?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.2" default-lazy="false" assembly="MyNamespace.Core" namespace="MyNamespace.Core">
<class name="CustomPage" table="CustomPages" xmlns="urn:nhibernate-mapping-2.2">
<id name="ID" column="ID" type="Guid" unsaved-value="00000000-0000-0000-0000-000000000000"><generator class="guid" /></id>
<property name="Title" column="Title" length="100" type="String"><column name="Title" /></property>
<property name="Term" column="Term" length="100" type="String"><column name="Term" /></property>
<property name="Content" column="Content" length="100" type="String"><column name="Content" /></property>
<property name="LastUpdated" column="LastUpdated" type="DateTime"><column name="LastUpdated" /></property>
<property name="DateAdded" column="DateAdded" type="DateTime"><column name="DateAdded" /></property>
<many-to-one name="Store" column="StoreID" /><many-to-one name="LastUpdatedBy" column="LastUpdatedById" />
<many-to-one name="AddedBy" column="AddedById" /></class></hibernate-mapping>
