How to databind a dropdownlist to a table, but get DataTextField from related table RRS feed

  • Question

  • In code, with LINQ to SQL you can easily reference a column in a related table, but it seems like you can't do this when databinding drop down list controls.

    In my case, I have a table called UnitOccupancy with a UnitID column that points to the primary key UnitID of the Units table. In code, if I want to access UnitCode in Units, from UnitOccupancy, I write Units.UnitCode. But I can't set the DataTextField of a DropDownList control to Units.UnitCode. It only seems to accept column references in the base table.

    For example, this does not work:

    < asp:DropDownList ID="unitDropdown" runat="server" DataSourceID="ldsUnitOccupancy"
                                DataValueField="UnitID" DataTextField="UnitCode" SelectedValue='<%# Bind("UnitID") %>' >
                            < / asp:DropDownList>

    By the way, this drop down list is being used in a Form View. I am using the Bind function to automate the updating of the table the Form View is bound to.

    You might think I should create a LinqDataSource that points to the Units table, but here I hit another problem. I need to restrict the units to those where the UnitOccupancy table fulfills some criteria. In SQL want to do this:

    select u.UnitID, u.UnitCode
    from Units u
    join UnitOccupancy uo on u.UnitID=uo.UnitID
    where uo.BranchID=@BranchID

    If my LinqDataSource points to UnitOccupancy the Where clause works, but I can't get UnitCode. If my LinqDataSource points to Units I can get UnitCode, but I don't know how to make the Where clause work.

    have also tried programmatically setting the DataSource of the DDL to a stored procedure (created as a method in the .dbml) that runs the above SQL. However I can't seem to get this approach to work if the Bind function is used in the DDL (which is essential for the DDL to automatically update the UnitID value of my record).

    Any suggestions?
    Wednesday, February 18, 2009 12:07 PM


  • Hi Laurence,

    You say that your DropDownList appears within a FormView. In this case, you can set the FormView's DataSource to select UnitOccupancy objects, and then set the DropDownList's DataSource to select Unit objects. When you navigate to the Edit view, the DropDownList will automatically select the correct UnitCode based on the UnitOccupancy's UnitID. This means that the join is unnecessary. Here is an example of what I mean using the Product -> Category relationship in the Northwind database.

    <asp:FormView ID="formView" runat="server" DataSourceID="products" DataKeyNames="ProductID">  
                SelectedValue='<%# Bind("CategoryID") %>' /> 
            <asp:Button ID="Button1" runat="server" Text="Update" CommandName="Update" /> 
            <asp:Button runat="server" Text="Edit" CommandName="Edit" /> 
        EnableUpdate="true" /> 

    Hope that helps,
    Wednesday, February 18, 2009 4:35 PM