Cant update DateTime columns in dd/MM/yyyy format. RSS

• • Reply
  

  warrenroscoe

  Member

  37 Points

  8 Posts

  Cant update DateTime columns in dd/MM/yyyy format.

  Jul 04, 2006 04:00 PM|LINK

  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??

  [:)]

• • Reply
  

  phuff

  Contributor

  2700 Points

  547 Posts

  Microsoft

  Re: Cant update DateTime columns in dd/MM/yyyy format.

  Jul 05, 2006 08:09 PM|LINK

  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...

  Polita Paulus
  
  This posting is provided "AS IS" with no warranties, and confers no rights.
• • Reply
  

  warrenroscoe

  Member

  37 Points

  8 Posts

  Re: Cant update DateTime columns in dd/MM/yyyy format.

  Jul 06, 2006 03:43 PM|LINK

  Hmmm. Surely since the standard datagrid displays my dates in correct format, and updates correctly without any extra coding, there is something wrong with the LINQ/BLINQ generated solution??