Hi all,
I am using Nhibernate 1.0.2 on MSSQL 2005 and .net framwork 2.0.
When I am saving data with field is datetime type then it's has exception.
here is sql generated from Nhibernate 1.0.2:

exec sp_executesql N'INSERT INTO dbo.OnLeave (DateFrom, PersonnelID, DateTo, Reason) VALUES (@p0, @p1, @p2, @p3); select
SCOPE_IDENTITY()',N'@p0 datetime,@p1 int,@p2 datetime,@p3 nvarchar(4000)',@p0=''2006-10-14 12:04:16:000'',@p1=NULL,@p2=''2006-10-14
12:04:16:000'',@p3=N'Comment'

We can easy consider problem is here in sql generate has redundant the ' sign (it should be only one ' sign).
And here is my mapping file:

<?xml version="1.0" encoding="utf-8" ?>
<hibernate-mapping xmlns="urn:nhibernate-mapping-2.0" schema="dbo">
<class name="OnLeave, DAL" table="OnLeave">
<id name="Id" type="Int32" column="ID" access="field.pascalcase-m-underscore">
<generator class="identity" />
</id>
<property name="DateFrom" column="DateFrom" type="DateTime" />
<property name="DateTo" column="DateTo" type="DateTime" />
<many-to-one name="Personnel" column="PersonnelID" class="Personnel, DAL" />
<property name="Reason" column="Reason" type="String" />
</class>
</hibernate-mapping>

is it a bug on Nhibernate 1.0.2 when using with Net framework 2.0 and SQL 2005? Please show me the way figured out this problem.
Any idea is welcome

This is the profiler output, isn't it? It mess up dates, but NH is ok in this regard.
What is the exception that you are getting?

hi, here is exception throw out when i run sql string on MSSQL 2005 (Standard version):

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '2006'.

And exception on my application throw out:

A first chance exception of type 'NHibernate.QueryException' occurred in NHibernate.dll
A first chance exception of type 'NHibernate.QueryException' occurred in NHibernate.dll
A first chance exception of type 'NHibernate.ADOException' occurred in NHibernate.dll

Any idea welcome.

That is _very_ strange.
What is the code that you are using?



The one above breaks for you?

Thanks for reply me,
Here my code, with personnel is master and onLeaves is details
I has m_ListOnLeave is a ArrayList

create OnLeave Object
aOnLeave = new OnLeave()
aOnLeave.Reason = txtReason.Text
aOnLeave.DateFrom = dateFrom.Value ' datetime picker control
aOnLeave.DateTo = dateTo.Value ' datetime picker control
aOnLeave.Personnel = m_Personnel ' I just fixed

Create Personnel Object
m_Personnel.SurName = txtSurname.Text
m_Personnel.OnLeaves = m_ListOnLeave // Array List of Onleave object
m_Personnel.StandBies = m_ListStandBies
m_Personnel.GivenName = txtGivenName.Text
m_Personnel.Department = cboDepartment.SelectedItem
m_Personnel.PhoneNo = txtPhoneNo.Text
m_Personnel.Title = cboTitle.SelectedItem
m_Personnel.Occupation = cboOccupation.SelectedItem
m_Personnel.PagerNo = txtPagerNo.Text
m_Personnel.Active = 1
m_BLLPersonnel.SavePersonnel(m_Personnel)
And now, The exception is throw out:
A first chance exception of type 'NHibernate.ADOException' occurred in NHibernate.dll

It's very strange,
I must be change from Datetime data type to become String data type.
And then I can save it into data.
Maybe I will try replace DateTime in .net framework to become NullablesDateTime.

when i took a look at the generated SQL the problem could be the date format. I had trouble with date queries which behave different on Sql-Server 2000 and 2005 in a multilingual software. We found that we have no trouble if we pass date fields always in american format. As we use SQL-queries in the application we use this code to convert the date fields:



Because you are using the stored procedure sp_executesql to insert the data you have to pay attention on your date fields as they are passed as string.

Regards
Klaus

But problem here is the store procedure auto generated by Nhibernate with version 1.0.2 Maybe NHibernate 1.0.2 does not completedly compatible with MSSQL 2005. Thank for your idea of luedi

I thought I had the same problem, but I realize the sql request was correctly executed by the the database.
The problem was in my nhibernate mapping, and the error message wasn't clear ...