Transforming Values on a databound control

[Denaes]

Can this even be done with a databound control in .Net?

 

What I mean by data transformation, in case it's a different syntax here, is when you take one value and display it as another value.

 

Say I have a table of Users. There is a data column called Status. Two of the more common statuses stored in this field are "A" and "I". So when I display this column, I'm supposed to display Active (for "A") and Inactive (for "I").

 

This is relatively easy if I don't use databound controls. I'm currently using a SQL Server 2005 database converted from another database (Progress) with SQL Server 2005 Developer Edition & Visual Studio 2005 Professional and Component One 2005 Suite.

 

In the other database language this datastructure came from, on each databound control, you could define that "A" becomes "Active" and "I" becomes "Inactive" in both directions (to/from the database).

 

My best guess (well it would work, but I'd rather not do it) as of now, to keep things databound, would be to create another table called "Status" with the letter as the primary key and the descriptive word as another field, then query the data from both tables with a relationship bound on the letter.

 

Any help or leads on this would be greatly appreciated :D

[Nerseus]

If this is a readonly grid, I generally have the SQL engine do the transposing for me. It generally makes sense since the lookup/replacement of A for Active is stored in a table anyway. If it's not and this is a hard-coded thing, you might still use SQL to do it with a CASE statement (SQL Server only maybe).

 

If the above won't work then I like your option - defining a relationship and having it display "automatically".

 

Another option might be an expression column that does the replace. Or, what I've done when all else fails, manually add another column to the DataSet in code and populate manually AFTER getting data from the DB. This gives you the most control.

 

-ner

[Denaes]

Ok, I havn't found any work arounds to having a ValueMember and not having a combo databound.

 

Yeah, these combos will handle both display and entry (i/o) so I would have to change the value both ways - or do a search/replace with a bunch of rows on tables to make the ValueMember which is saved match the text (ie, Active saves the string "Active" rather than A).

 

I could have created 5 relational child tables with values, but that seemed a little silly since the largest of these tables would have 5 values and only the Status Table would see use elsewhere.

 

My solution is easy to work with, though I'm sure I'm breaking normalization rules somehow.

 

I just created one table called ComboValueItems with 4 fields: ID (just a unique primary key, just in case), ComboDropdownItem, ValueMember and Identifier.

 

ComboDropdownItem is the value to be shown in comboboxes (or other collection controls).

 

ValueMember is the value to be saved to the database and/or loaded from the database.

 

Identifier is a name that corelates to a control.

 

So I just have one table and create a few queries to select ComboDropdownItem & ValueMember based on Identifier and hook them up to the combo/list boxes.

 

It works and this isn't the final production... we don't even have a fully relational SQL database yet. Still in conversion from a not quite relational Progress database. So I'm sure all these tables will change anyways - but we need pretty screens to show management.