[Mono-bugs] [Bug 500987] New: SqlCommand with DateTime parameter throws exception "Error converting data type varchar to datetime" (impacts NHibernate and Castle ActiveRecord)

[bugzilla_noreply at novell.com]

http://bugzilla.novell.com/show_bug.cgi?id=500987


           Summary: SqlCommand with DateTime parameter throws exception
                    "Error converting data type varchar to datetime"
                    (impacts NHibernate and Castle ActiveRecord)
    Classification: Mono
           Product: Mono: Class Libraries
           Version: 2.4.x
          Platform: All
        OS/Version: All
            Status: NEW
          Severity: Critical
          Priority: P5 - None
         Component: Sys.Data.SqlClient
        AssignedTo: mono-bugs at lists.ximian.com
        ReportedBy: loic.nageleisen at gmail.com
         QAContact: mono-bugs at lists.ximian.com
          Found By: ---


User-Agent:       Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10_5_6; en-us)
AppleWebKit/528.16 (KHTML, like Gecko) Version/4.0 Safari/528.16

An absolutely trivial class connecting remotely to either MSSQL 2000 on Windows
2000 or MSSQL 2005 on Windows 2003 contains the following statements. The code
is run locally either on a Windows XP machine or a Mac OSX 10.5.6 machine.

This works:

            cmd = new SqlCommand("INSERT INTO TestModel (Name, Date) VALUES
('ADO', '01/04/2009 00:01')", connection);


This fails:

            cmd = new SqlCommand("INSERT INTO TestModel (Name, Date) VALUES
(@p1, @p2)", connection);
            cmd.Parameters.Add("@p1", SqlDbType.VarChar);
            cmd.Parameters["@p1"].Value = "ADOp";
            cmd.Parameters.Add("@p2", SqlDbType.DateTime);
            cmd.Parameters["@p2"].Value = DateTime.Parse("01/04/2009 00:05");
            Console.WriteLine(cmd.CommandText);
            cmd.ExecuteNonQuery();


The above example makes sole use of Mono provided classes, but as NHibernate
makes heavy use of parametrized queries, it prevents anything NHibernate-based
(like Castle ActiveRecord) to do anything useful with Mono as soon as dates are
involved.

Please note that:
- SQL2000 language is set to French
- SQL2005 language is set to English
- Both server OSs and software are french versions.
- Mac OS X cient is set to en_GB locale
- Windows client is french
- the given dates surely can be ambiguous depending on various locale settings
but should be at least misinterpreted as either 01/04/2009 or 04/01/2009 and
safely converted.
- setting Thread.CurrentThread.CurrentCulture to a new Culture("fr-FR") en-US
or en-GB does not change a thing
- manipulating DateTimeFormat.ShortDatePattern does not change anything either


Reproducible: Always

Steps to Reproduce:
1a. use the above code in a trivial class connectiong to a database
or
1b. use NHibernate with a DateTime field
or
1c. use Castle ActiveRecord with a DateTime property
2. execute query involving DateTime field, like SQL INSERT or NH
session.save(model) or AR model.create()
3. observe exception being raised
Actual Results:  
Exception raised.

Expected Results:  
Query execution.

Exception stack:

Unhandled Exception: System.Data.SqlClient.SqlException: Error converting data
type varchar to datetime.
  at System.Data.SqlClient.SqlConnection.ErrorHandler (System.Object sender,
Mono.Data.Tds.Protocol.TdsInternalErrorMessageEventArgs e) [0x00000] 
  at Mono.Data.Tds.Protocol.Tds.OnTdsErrorMessage
(Mono.Data.Tds.Protocol.TdsInternalErrorMessageEventArgs e) [0x00000] 
  at Mono.Data.Tds.Protocol.Tds.ProcessMessage (TdsPacketSubType subType)
[0x00000] 
  at Mono.Data.Tds.Protocol.Tds.ProcessSubPacket () [0x00000] 
  at Mono.Data.Tds.Protocol.Tds.NextResult () [0x00000] 
  at Mono.Data.Tds.Protocol.Tds.SkipToEnd () [0x00000] 
  at Mono.Data.Tds.Protocol.Tds.ExecuteQuery (System.String sql, Int32 timeout,
Boolean wantResults) [0x00000] 
  at Mono.Data.Tds.Protocol.Tds70.Execute (System.String commandText,
Mono.Data.Tds.TdsMetaParameterCollection parameters, Int32 timeout, Boolean
wantResults) [0x00000] 
  at System.Data.SqlClient.SqlCommand.Execute (Boolean wantResults) [0x00000] 
  at System.Data.SqlClient.SqlCommand.ExecuteNonQuery () [0x00000] 
  at (wrapper remoting-invoke-with-check)
System.Data.SqlClient.SqlCommand:ExecuteNonQuery ()
  at Dev.Nageleisen.TestCase.TestCase.Main (System.String[] args) [0x00000]

-- 
Configure bugmail: http://bugzilla.novell.com/userprefs.cgi?tab=email
------- You are receiving this mail because: -------
You are the QA contact for the bug.
You are the assignee for the bug.