SmartDate Localisation

SmartDate Localisation

Old forum URL: forums.lhotka.net/forums/t/6878.aspx


alex.williamson@grampianfasteners.com posted on Thursday, April 30, 2009

I hope this hasn't been submitted before. I've searched for it, but can't find a thread that the same. If there is one, please just link me to it - if not, I hope I can get some answers to what I think might be a simple problem.

I am developing a WinForms application with CSLA and I am trying to follow the new Expert VB 2008 Business Objects book. I have a BO that inherits from BB. I have a Due Date (smartdate) property as declared below:

_
Public Class IIR
Inherits BusinessBase(Of IIR)

Private Shared DueProperty As PropertyInfo(Of SmartDate) = RegisterProperty(New PropertyInfo(Of SmartDate)("Due"))

and the property get sets:


Public Property Due() As String
Get
Return GetPropertyConvert(Of SmartDate, String)(DueProperty)
End Get
Set(ByVal value As String)
SetPropertyConvert(Of SmartDate, String)(DueProperty, value)
End Set
End Property

My system localisation is set to British English - and this will affect the language and display of the (standard) Calendar Drop Down control on the WinForm. The problem occurs when I try to insert/update values to the database. The database field is set as a datetime (Windows SQL Express 2008) and allows nulls.

I get the sql exception error "Error converting data type nvarchar to datetime" which means the format of the String from the Due Date property is incorrect (it's in the wrong format). In fact the string that represents the date is 17/04/2009. I know this because I popped an alert in the DataPortal_Insert is called:

MsgBox(ReadPropertyConvert(Of SmartDate, String)(DueProperty))

So I think the problem is the translation between the Datetime to String. I tested my theory by changing the system wide Regional Settings to American English, and there were no exceptions.

I then tried changing the SQL language using

SET LANGUAGE British

but this has had no affect. The format in the Database will only accept an America (months first mm/dd/yyyy) format as far as I can tell, is there a setting or config I can quickly change?

ajj3085 replied on Thursday, April 30, 2009

I think the problem is in your data access code. You should be passing the SmartDate.Date value (which is a DateTime) to your data layer, not a string. DateTimes in sql and .Net are backed by interger values, so the locale formatting shouldn't be an issue at all.

Can we see some DataPortal_Insert code?

alex.williamson@grampianfasteners.com replied on Thursday, April 30, 2009

Absolutely, sorry I should have added that to my previous post.

The stored procedure I use is:

PROCEDURE [dbo].[AddIIR]
(
@id uniqueidentifier,
@description varchar(MAX),
@due datetime,
@priority int,
@status int,
@investigator uniqueidentifier,
@originator uniqueidentifier,
@recommendedaction varchar(MAX),
@reviewdate datetime,
@reviewnotes varchar(MAX),
@completed datetime,
@newlastchanged timestamp output
)
AS
INSERT INTO
IIR
(
Id,
Description,
Due,
Priority,
Status,
Investigator,
Originator,
RecommendedAction,
ReviewDate,
ReviewNotes,
Completed
)
VALUES
(
@id,
@description,
@due,
@priority,
@status,
@investigator,
@originator,
@recommendedaction,
@reviewdate,
@reviewnotes,
@completed
)

SELECT @newlastchanged=LastChanged FROM IIR WHERE Id=@id
RETURN

I have got it down to be the dates as I removed all other fields until it was just the Due Date.

I'm not using LINQ yet, I am trying to follow the pattern in the 2005 book, so the DataPortal_Create is:

_
Protected Overrides Sub DataPortal_Insert()
Using cn As New SqlConnection(My.Resources.Connection)
cn.Open()
Using cm As SqlCommand = cn.CreateCommand
cm.CommandText = "AddIIR"
DoInsertUpdate(cm)
End Using
End Using
End Sub

Private Sub DoInsertUpdate(ByVal cm As SqlCommand)
cm.CommandType = CommandType.StoredProcedure
With cm
With .Parameters
.AddWithValue("@id", ReadProperty(IdProperty))
.AddWithValue("@description", ReadProperty(DescriptionProperty))
.AddWithValue("@due", ReadPropertyConvert(Of SmartDate, String)(DueProperty))
MsgBox(ReadPropertyConvert(Of SmartDate, String)(DueProperty))
.AddWithValue("@priority", 0)
.AddWithValue("@status", ReadProperty(StatusProperty))
.AddWithValue("@investigator", Guid.NewGuid)
.AddWithValue("@originator", Guid.NewGuid)
.AddWithValue("@recommendedaction", ReadProperty(RecommendedActionProperty))
.AddWithValue("@reviewdate", ReviewDate)
.AddWithValue("@reviewnotes", ReadProperty(ReviewNotesProperty))
.AddWithValue("@completed", Completed)
Dim param As New SqlParameter("@newlastchanged", SqlDbType.Timestamp)
param.Direction = ParameterDirection.Output
.Add(param)
End With

.ExecuteNonQuery()

_lastChanged = CType(.Parameters("@newlastchanged").Value, Byte())
End With
End Sub

ajj3085 replied on Thursday, April 30, 2009

Ok, your code confirms what I thought. Try this for your due date

if ( ReadProperty( DueProperty ).IsEmpty ) {
.AddWithValue("@due", DBNull.Value )
}
else {
.AddWithValue("@due", ReadProperty( DueProperty).Date )
}

alex.williamson@grampianfasteners.com replied on Thursday, April 30, 2009

ajj3085:
Ok, your code confirms what I thought. Try this for your due date

if ( ReadProperty( DueProperty ).IsEmpty ) {
.AddWithValue("@due", DBNull.Value )
}
else {
.AddWithValue("@due", ReadProperty( DueProperty).Date )
}


Brilliant that was correct. Thank you *very* much.

Seconds before you replied I tried

.AddWithValue("@due", ReadProperty(DueProperty).Date)

and that worked. It also had to be not null, so your code fixes both issues. I didn't really want to use an if else in the DataPortal_Insert so I set the default value it in the DataPortal_Create:

_
Protected Overrides Sub DataPortal_Create()
LoadProperty(IdProperty, Guid.NewGuid)
Due = CStr(DateTime.Today)
ReviewDate = CStr(DateTime.Today)
Completed = CStr(DateTime.Today)
LoadProperty(StatusProperty, IIRStatusListNameValue.DefaultStatus)
ValidationRules.CheckRules()
End Sub

Again thank you very much.

ajj3085 replied on Thursday, April 30, 2009

Great, glad it's working now.

A little confused though.. if Due has to be "not null," then my if else is not needed (provided you have a business rule that invalidates the BO if Due IsEmpty).

Oh.. and sorry for the C# / VB.Net mix... C# is my native language, so I didn't think twice when wrapping the VB code I copied. Sorry if that was confusing for anyone.. :-)

Copyright (c) Marimer LLC