I am having trouble updating a simple DateTime column. My server is set up as English UK formats, UK location, GMT timezone.

A normal GridView control displays and updates my dates without any extra formatting (e.g. HtmlEncode="false" DataFormatString="{0:dd/MM/yyyy}" ApplyFormatInEditMode=true), but when using a Blinq form, my dd and mm values get transposed when updating. Strangely, if I update them directly in SQL, they display correctly, both in the default viewing mode and in edit mode.

SQL Profiler displays the following:

GridView

exec sp_executesql N'UPDATE [AllColumnDataTypes] SET [clmInt] = @clmInt, [clmDateTime] = @clmDateTime, [clmSmallDateTime] = @clmSmallDateTime WHERE
[clmBigint] = @clmBigint',N'@clmInt int,@clmDateTime datetime,@clmSmallDateTime datetime,@clmBigint bigint',@clmInt=1,@clmDateTime=''2006-04-03
00:00:00:000'',@clmSmallDateTime=''2006-02-01 00:00:00:000'',@clmBigint=1

Blinq

exec sp_executesql N'UPDATE [AllColumnDataTypes]
SET [clmDateTime] = @p14, [clmSmallDateTime] = @p15
FROM [AllColumnDataTypes]
WHERE ([clmInt] = @p0) AND ([clmSmallInt] = @p1) AND ([clmTinyInt] = @p2) AND ([clmFloat] = @p3) AND ([clmReal] = @p4) AND ([clmMoney] = @p5) AND
([clmSmallMoney] = @p6) AND ([clmChar] = @p7) AND ([clmNChar] = @p8) AND ([clmVarChar] = @p9) AND ([clmNVarChar] = @p10) AND ([clmGuid] IS NULL) AND
([clmDecimal] = @p11) AND ([clmNumeric] = @p12) AND ([clmBigint] = @p13)
SELECT NULL AS [EMPTY]
FROM [AllColumnDataTypes] AS [t1]
WHERE ((@@ROWCOUNT) > 0) AND ([t1].[clmBigint] = @p16)',N'@p0 int,@p1 smallint,@p2 tinyint,@p3 float,@p4 real,@p5 decimal(5,4),@p6 decimal(5,4),@p7
nvarchar(255),@p8 nvarchar(255),@p9 nvarchar(1),@p10 nvarchar(1),@p11 decimal(1,0),@p12 decimal(1,0),@p13 bigint,@p14 datetime,@p15 datetime,@p16
bigint',@p0=1,@p1=2,@p2=1,@p3=1,@p4=1,@p5=1.0000,@p6=1.0000,@p7=N'1                                                                                                                                                                                                                                                             
',@p8=N'1                                                                                                                                                                                                                                                             
',@p9=N'1',@p10=N'1',@p11=1,@p12=1,@p13=1,@p14=''2006-01-02 00:00:00:000'',@p15=''2006-03-04 00:00:00:000'',@p16=1

Thus it seems that the dates are being formatted incorrectly in the Blinq version. They should be

,@p14=''2006-02-01 00:00:00:000'',@p15=''2006-04-03 00:00:00:000''

Is there a known workaround for this, or am I doing something wrong??

Warren, when a format string is applied to the value in a BoundField, the BoundField can't remove the formatting when it retrieves values from the field.  To do this manually, handle the RowUpdating event on the GridView or the ItemUpdating event on the DetailsView.  The handler should look like this:

protected void OrdersGridView_RowUpdating(object sender, GridViewUpdateEventArgs e) {

    DateTime orderDate = DateTime.ParseExact((string)e.NewValues["OrderDate"], "dd/MM/yyyy", System.Globalization.CultureInfo.CurrentCulture);

    e.NewValues["OrderDate"] = orderDate;

    DateTime oldOrderDate = DateTime.ParseExact((string)e.OldValues["OrderDate"], "dd/MM/yyyy", System.Globalization.CultureInfo.CurrentCulture);

    e.OldValues["OrderDate"] = oldOrderDate;

In the first two lines, I parse the new value entered by the user and set this value in the NewValues dictionary that gets handed to the ObjectDataSource.  In the second two lines I do the same thing for the original value, in case you're using optimistic concurrency.  You should do this for each DateTime field in your table.

Hope this helps...

thanks very much it works correctly

 see http://forums.asp.net/p/888283/3179097.aspx#3179097