DataColumn Expressions

[Guest]

I have a typed DataSet with a DataTable where I added a column FullName that
simply returns the concatination of 2 other columns FirstName and LastName.
There is also a DataAdapter that returns this DataTable populated from the
DataBase.
If i Preview this data from the DataSet Designer, everything shows up fine
and that's the only case where the FullName expression shows up. If I call
the Adapter methods to populate the DataTable, the expression FullName column
is NULL.
Please help. All I want to do is have an extra column in a datatable with an
expression set in design time. This works if I add the column programatically
after the datatable is filled up.

 

[RobinS]

If it is not a bound column, you can format it in the CellFormatting event.
Like this:

Put this in your form_load:
AddHandler m_Grid.CellFormatting, AddressOf OnCellFormatting

Private Sub OnCellFormatting(ByVal sender As Object, _
ByVal e As DataGridViewCellFormattingEventArgs)
'The event arg that is passed in exposes a number of
' properties to let you know what cell is being rendered.
'You can use the ColumnIndex to determine which column
' the event is being fired for.
'Note: if you were really going to do something like this,
' you would look up the column once and save the col#,
' and then use that value in this routine. Accessing it
' using the name repeatedly will slow it down.
If e.ColumnIndex = m_Grid.Columns("FullName").Index
'Setting this to true signals the grid that this
' column is being dynamically updated. If you don't
' do this, you will get an infinite loop if you have
' also set the column to have its size automatically determined.
e.FormattingApplied = True
'Get the row being populated. Format this cell.
Dim row As DataGridViewRow = m_Grid.Rows(e.RowIndex)
e.Value = String.Format("{0} {1}", _
row.Cells("FirstName").Value, _
row.Cells("LastName").Value)
End If
End Sub


Robin S.

 

[Cor Ligthert [MVP]]

Lorenc,

I don't think that one of us can real help you with a piece of code. Be
aware as well that there are two methods that you are doing. One
concatentation is done in the SQL transaction code the other one is done in
the selection. You write in the subject Expression. So probably it is that,
can you show that?

Cor

 

[Guest]

Thank you RobinS and Cor for your responses.
My problem is a very simple case. I know I can format the column for the
fullname and I know I can do that in many ways through SQL, but it is very
handy if you have this computed column in the datatable and keep sql and
codebehind clean.
To replicate the problem, just create a simple typed DataSet and add a
datatable based on a database table, which has let's say 2 columns, first and
last name. As you know, you also get a table adapter that populates this
datatable. Now, add a column to the datatable(in the dataset design) called
fullname and set the expression on that column to FirstName + ' ' + LastName
in the property of the fullname column (I am using Visual Studio 2005).
Right click on the adapter (usually below the datatable) and select
preview, you will now see that fullname is calculated fine. Now, create a
webform and add a gridview with a datasource from this datatable. You will
now see that the fullname is empty (DBNull to be precise). Is this a bug?
I am frustrated with this one because this is how Microsoft suggests
creating computed columns and it is a very simple logic thing to do. Notice
that if you add a new column programmatically to the datatable and not from
the design it works fine. Why the difference?
Maybe I am doing something wrong, but this is one of those cases that you
spend days searching forums and the web for a solution to make something work
for a framework that it's life span is maybe 2-3 years.
What is funny and sad in the same time is that I have just establish a nice
way of creating a data access layer and a business logic layer that works for
my applications in .NET 1.1 based on best practices which are now obsolete
with .NET 2. Again, based on best practices I am trying to establish a nice
data access layer using typed datasets because they are so nice in .NET2 and
I bet that in .NET 3 that is coming up next year with that windows workflow
what I am doing now will be obsolete as well. This is what I call a worthy
experience.

 

[Cor Ligthert [MVP]]

Lorenc,

At least it is " " and not ' ', I have done this often, it should go.

Cor

 

[Guest]

Thanks Cor.

That's not it. You can't use " ", you will get a property is not valid
message right away, you have to use single quotes.

I did find a work around though. I added the following partial class which
extends the dataset class and it is all good. Again You can't put expression
value at the design time, you have to do it in code otherwise it gets lost.

public partial class ContactDataSet {
public partial class ContactDataTable
{
public override void EndInit()
{
base.EndInit();
FullNameColumn.Expression = "FirstName + ' ' + LastName";
}
}
}