Many, many times I’ve had the situation where I want to bind a data-set or data-view to a DropDownList, CheckBoxList, or other ListItem-based control, but I need the display text set through the DataTextField property to use multiple data fields from the result set, and not just the 1 field that it is designed to support.
For example, I might have a user’s name separated into 2 distinct data fields: User_Name_First and User_Name_Last, but I want it displayed in the DropDownMenu as “User_Name_Last, User_Name_First”.
Maybe you can relate. What are the options?
1. Modify your database query to merge the fields in the SELECT. This is likely the most efficient solution, but not always the most practical depending on your environment.
2. Add a new DataColumn to the result set, enumerate through the results, and set the value of the new DataColumn upon each iteration. Does the job, but not very efficient or elegant.
How about this… go ahead and add a new DataColumn to the result set, but make it an Expression Column. Example:
oDataSet.Tables(0).Columns.Add(new DataColumn(“MyNewFieldName”, System.Type.GetType(“System.String”), “CurrentField1 + ‘, ‘ + CurrentField2″)
I finally stumbled upon this solution (as simple as it seems) and it is such a relief! I feel like a goof since it never occurred to me until now, but its not the first time, and it certainly won’t be the last. Many online searches yielded several comments on the first 2 options above, but not much else. Now that I am on this side of the fence, I find it curious as to why there isn’t much discussion on this technique. Maybe there is a lot of overhead to it? Not sure, but certainly seems better than iterating through the result set record-by-record. Seems like a no-brainer to me.
It is like a pain killer. An elegant solution.
Thank you very much. It save me a lots of time.
Great solution. I looked so hard to find a solution that was simple to implement but couldn’t find one until I ran into yours. Thanks!