Dropdown within a DetailsView: show text based on stored value

[Cineno]

0 down vote favorite
share [g+] share [fb] share [tw]


I have a database table called Employees that has an ID number, First Name, Last Name, and SupervisorID for each employee. The SupervisorID stored for an employee equals the ID number of that employee's supervisor.

I want to have a DetailsView control that can edit these fields for an employee. The Supervisor field needs to be a dropdown that stores the SupervisorID as the value, and shows the supervisor's First Name and Last Name as it's text.

I've looked around a lot and was able to figure out how to create the dropdown to select and show SupervisorID's. Where I'm stuck is how do I change the code below to store the SupervisorID, but show the Supervisor's name as it's text?

Here's the code for the Supervisor dropdown within my DetailsList:

<asp:TemplateField HeaderText="Supervisor" SortExpression="SupervisorID">
<EditItemTemplate>
<aspropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource3" DataTextField="SupervisorID" DataValueField="SupervisorID" SelectedValue='<%# Bind("SupervisorID") %>'>
</aspropDownList>
<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:dbConnectionString %>" SelectCommand="SELECT [SupervisorID] FROM [Employees]"></asp:SqlDataSource>
</EditItemTemplate>
<ItemTemplate >
<asp:Label Runat="server" Text='<%# Bind("SupervisorID") %>' ID="Label1"></asp:Label>
</ItemTemplate>
</asp:TemplateField>

Any ideas for what I can change to accomplish my goal? Thanks for any help!

 

[jbenson001]

I just want to state that using the DataSource controls is not a good idea. But this is what you have to do:
You have to change your SelectCommand to include a column with the name.

   SelectCommand="SELECT [SupervisorID], [FirstName] + " " + [LastName] AS [Name] FROM [Employees]"

Then change the DataTextField to point to the Name column

I am not sure of the syntax for the select command as I do not use the DataSource controls.

 

[Cineno]

Thanks a lot for you help, that did it!

Can you tell me why I shouldn't use a DataSource control? I'm pretty new to this, so I'd like to know why.

I have on follow up question if you're able to help. After updating, the <Item Template> field is showing the SupevisorID, but I'd like for it to show the Supervisor's name. Is there a way to accomplish this?

Thanks again.

 

[jbenson001]

The datasource controls are good for very simple pages. However, once you need to do something more complicated, they are either very difficult to use if not impossible. Also, you cannot in any way debug them. If you get unwanted or no results, let's say, you can't step through code and see what is going on without setting up something more complicated like SQL Profiler,etc. You are best off writing a DAL of your own that you can control easily and maintain and debug.

If you look at your code you will see that you are binding your label to the SupervisorID rather than the Name field you created in the previous post.