DataColumn.Expr ession with DateTime Datatype

[Matt F]

I'm trying to do something that seems like it should be pretty simple, but
haven't found a solution. I am trying to add a datacolumn to a datatable
that adds or subtracts a number of days based on another datetime column in
the table. See sample below. How do I do this? Any help would be greatly
appreciated. Thanks
Private Sub CreateDataTable ()
dt = New DataTable

Dim IntegerColumn As New DataColumn
IntegerColumn.D ataType = GetType(Integer )
IntegerColumn.C olumnName = "IntegerCol umn"
dt.Columns.Add( IntegerColumn)

Dim ComputedInteger Column As New DataColumn
ComputedInteger Column.DataType = GetType(Integer )
ComputedInteger Column.ColumnNa me = "ComputedIntege rColumn"
ComputedInteger Column.Expressi on = "IntegerCol umn + 2"
dt.Columns.Add( ComputedInteger Column)

Dim DateColumn As New DataColumn
DateColumn.Data Type = GetType(System. DateTime)
DateColumn.Colu mnName = "DateColumn "
dt.Columns.Add( DateColumn)

Dim ComputedDateCol umn As New DataColumn
ComputedDateCol umn.DataType = GetType(System. DateTime)
ComputedDateCol umn.ColumnName = "ComputedDateCo lumn"
ComputedDateCol umn.Expression = "DateColumn.Add Days(2)" ' this
doesn't work
ComputedDateCol umn.Expression = "DateColumn + 2" ' this
doesn't work either
dt.Columns.Add( ComputedDateCol umn)

Dim r As DataRow = dt.NewRow
r.Item("Integer Column") = 5
r.Item("DateCol umn") = Date.Today

dt.Rows.Add(r)

End Sub

[ClayB]

I do not think you will be able to do this using a simple Expression
column as there are no available functions to pick apart a date, and
the Convert function will only coerce a DateTime to a string, and
trying to pick apart a genericly formatted datetime string in an
Expression with the limited Substring function will be a challenge.
See http://msdn2.microsoft.com/en-us/lib...xpression.aspx.

If you are trying to display this data in a DataGridView, then one
solution would be to use an unbound column and handle the
CellValueNeeded event to provide this computed value on demand. (Ask
if you want more ifnormation on this idea.)
=============== ======
Clay Burch
Syncfusion, Inc.

[Linda Liu [MSFT]]

Hi Matt,

Transact-SQL provides some date and time functions, such as DateAdd,
DateDiff and so on. Unfortunately, these functions are not supported in the
expression of a DataColumn, which means we couldn't use DateAdd function in
the expression of a DataColumn.

I suggest you use the DateAdd function in the select sql statement to
retrieve the DataTable. The following is a sample.

SELECT DATEADD(day, 2, DateColumn) FROM YourTable

For more information on the DataAdd function, you may visit the following
link.

'DATEADD'
http://msdn2.microsoft.com/en-us/lib...9(SQL.80).aspx

Hope this helps.
If you have any question, please feel free to let me know.

Sincerely,
Linda Liu
Microsoft Online Community Support

=============== =============== =============== =====
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscripti...ult.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscripti...t/default.aspx.
=============== =============== =============== =====

This posting is provided "AS IS" with no warranties, and confers no rights.

[Matt F]

Linda,

The solution provided works great up to a point. The problem is after the
datatable is populated, I then bind controls on a form to the table.
Obviously, I don't want to have to requery the DB every time a date is
changed. Any suggestions for how to deal with this?

[Linda Liu [MSFT]]

Hi Matt,

Thank you for your feedback.

Do you bind two DateTimePicker controls on the form to the two datetime
columns in the datatable, respectively?

If so, I think a simple workaround is to subscribe the ValueChanged event
of the DateTimePicker that is bound to the 'DateColumn' column in the
datatable, and change the value of the DateTimePicker that is bound to the
'ComputedDateCo lumn' column.

You may call the AddDays method of DateTime to get the desired value for
the computed DateTimePicker.

Hope this helps.
If you have any question, please feel free to let me know.

Sincerely,
Linda Liu
Microsoft Online Community Support

[Matt F]

Sorry for the delay in response, I've had another issue that took my
attention away from this.

While the suggestion I beleive to be a good one, the problem is this is an
application where the controls are created dynamically at runtime and as
such, it's quite possible the date control bound to the ComputedDateCol umn
will be created before the date control bound to the DateColumn --- as such,
it's not to reliably subscribe to the ValueChanged event since the control
may or may not exist yet.

"Linda Liu [MSFT]" <v-****@online.mic rosoft.comwrote in message
news:rF******** ******@TK2MSFTN GHUB02.phx.gbl. ..

Hi Matt,

Thank you for your feedback.

Do you bind two DateTimePicker controls on the form to the two datetime
columns in the datatable, respectively?

If so, I think a simple workaround is to subscribe the ValueChanged event
of the DateTimePicker that is bound to the 'DateColumn' column in the
datatable, and change the value of the DateTimePicker that is bound to the
'ComputedDateCo lumn' column.

You may call the AddDays method of DateTime to get the desired value for
the computed DateTimePicker.

Hope this helps.
If you have any question, please feel free to let me know.

Sincerely,
Linda Liu
Microsoft Online Community Support

[Matt F]

Another problem with working from the data layer instead of the data
layer --- I need the ability to do things like set the sort and filter
properties of my bindingsource based on values from the ComputedDateCol umn.

"Matt F" <mf************ ****@nospam.nos pamwrote in message
news:O9******** *****@TK2MSFTNG P04.phx.gbl...

Sorry for the delay in response, I've had another issue that took my
attention away from this.

While the suggestion I beleive to be a good one, the problem is this is an
application where the controls are created dynamically at runtime and as
such, it's quite possible the date control bound to the ComputedDateCol umn
will be created before the date control bound to the DateColumn --- as
such, it's not to reliably subscribe to the ValueChanged event since the
control may or may not exist yet.

"Linda Liu [MSFT]" <v-****@online.mic rosoft.comwrote in message
news:rF******** ******@TK2MSFTN GHUB02.phx.gbl. ..

>Hi Matt,

Thank you for your feedback.

Do you bind two DateTimePicker controls on the form to the two datetime
columns in the datatable, respectively?

If so, I think a simple workaround is to subscribe the ValueChanged event
of the DateTimePicker that is bound to the 'DateColumn' column in the
datatable, and change the value of the DateTimePicker that is bound to
the
'ComputedDateC olumn' column.

You may call the AddDays method of DateTime to get the desired value for
the computed DateTimePicker.

Hope this helps.
If you have any question, please feel free to let me know.

Sincerely,
Linda Liu
Microsoft Online Community Support

[Linda Liu [MSFT]]

Hi Matt,

Thank you for your feedback.

I understand you scenario.

I think an alternative to handle the ValueChanged event of the
DateTimePicker control is to handle the ColumnChanged event of the
DataTable, instead. (Of course, we still need to use the Transact-SQL
function 'DateAdd' in the select command for the initial query.)

The following is a sample.

Public Class Form1

Public Sub New()

' This call is required by the Windows Form Designer.
InitializeCompo nent()

' Add any initialization after the InitializeCompo nent() call.
AddHandler Me.DataSet11.Ta ble1.ColumnChan ged, AddressOf
Table1_ColumnCh anged
End Sub

Sub Table1_ColumnCh anged(ByVal sender As Object, ByVal e As
DataColumnChang eEventArgs)
If (e.Column.Colum nName = "DateColumn ") Then
e.Row("Computed DateColumn") =
Convert.ToDateT ime(e.ProposedV alue).AddDays(2 )
' to ensure the
DateTimePicker control bound to the ComputedDateCol umn to display the new
value immediately, call the corresponding BindingManagerB ase's Refresh
method
CType(BindingCo ntext(DataSet11 , "Table1"), CurrencyManager ).Refresh()
End If
End Sub
End Class
Hope this helps.
If you have any question, please feel free to let me know.

Sincerely,
Linda Liu
Microsoft Online Community Support

[Linda Liu [MSFT]]

Hi Matt,

To sort or filter data in a data source, we could use DataView or
BindingSource as the media data source.

That is, set the DataView's Table property to the DataTable, or set the
DataSource and the DataMember properties of the BindingSource to the
DataSet and the DataTable, respectively. And then bind the controls to the
DataView or BindingSource.

As for DataView, we could use its Sort and RowFilter properties to sort and
filter the data.

As for BindingSource, we could use its Sort and Filter properties to sort
and filter the data.

Hope this helps.
If you have any question, please feel free to let me know.
Sincerely,
Linda Liu
Microsoft Online Community Support

[Matt F]

Linda,

Thanks for the clarification --- I'm already using the sort and filter
properties of the datasource and all is working well --- this is why the
calculations must be peformed at the data level and not the control level.

Thanks

"Linda Liu [MSFT]" <v-****@online.mic rosoft.comwrote in message
news:Xe******** ******@TK2MSFTN GHUB02.phx.gbl. ..

Hi Matt,

To sort or filter data in a data source, we could use DataView or
BindingSource as the media data source.

That is, set the DataView's Table property to the DataTable, or set the
DataSource and the DataMember properties of the BindingSource to the
DataSet and the DataTable, respectively. And then bind the controls to the
DataView or BindingSource.

As for DataView, we could use its Sort and RowFilter properties to sort
and
filter the data.

As for BindingSource, we could use its Sort and Filter properties to sort
and filter the data.

Hope this helps.
If you have any question, please feel free to let me know.
Sincerely,
Linda Liu
Microsoft Online Community Support