DataColumn.Expression 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.DataType = GetType(Integer)
IntegerColumn.ColumnName = "IntegerColumn"
dt.Columns.Add(IntegerColumn)

Dim ComputedIntegerColumn As New DataColumn
ComputedIntegerColumn.DataType = GetType(Integer)
ComputedIntegerColumn.ColumnName = "ComputedIntegerColumn"
ComputedIntegerColumn.Expression = "IntegerColumn + 2"
dt.Columns.Add(ComputedIntegerColumn)

Dim DateColumn As New DataColumn
DateColumn.DataType = GetType(System.DateTime)
DateColumn.ColumnName = "DateColumn"
dt.Columns.Add(DateColumn)

Dim ComputedDateColumn As New DataColumn
ComputedDateColumn.DataType = GetType(System.DateTime)
ComputedDateColumn.ColumnName = "ComputedDateColumn"
ComputedDateColumn.Expression = "DateColumn.AddDays(2)" ' this
doesn't work
ComputedDateColumn.Expression = "DateColumn + 2" ' this
doesn't work either
dt.Columns.Add(ComputedDateColumn)

Dim r As DataRow = dt.NewRow
r.Item("IntegerColumn") = 5
r.Item("DateColumn") = 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/library/system.data.datacolumn.expression.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/library/aa237919(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/subscriptions/managednewsgroups/default.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/subscriptions/support/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
'ComputedDateColumn' 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 ComputedDateColumn
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.

 

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

 

[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.
InitializeComponent()

' Add any initialization after the InitializeComponent() call.
AddHandler Me.DataSet11.Table1.ColumnChanged, AddressOf
Table1_ColumnChanged
End Sub

Sub Table1_ColumnChanged(ByVal sender As Object, ByVal e As
DataColumnChangeEventArgs)
If (e.Column.ColumnName = "DateColumn") Then
e.Row("ComputedDateColumn") =
Convert.ToDateTime(e.ProposedValue).AddDays(2)
' to ensure the
DateTimePicker control bound to the ComputedDateColumn to display the new
value immediately, call the corresponding BindingManagerBase's Refresh
method
CType(BindingContext(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]]

Hi Matt,

Thank you for your prompt response.

How about my solution of handling the ColumnChanged event of the data
table? Is your problem solved now?

If the problem is still not solved or you have any question, please feel
free to let me know.

Thank you for using our MSDN Managed Newsgroup Support Service!


Sincerely,
Linda Liu
Microsoft Online Community Support

 

[Matt F]

The problem has been taken care of.... thanks for your help, it's certainly
appreciated.