如何消除下拉列表中显示的重复数据？在ASP.net中的FormView中？

I am having a little trouble to filter my data to eliminate duplicates. This is a data binding exercise and the question asks "to change Listing Agent ID into a dropdownlist that will display distinct Agent LastName in the edit mode"

我有一点麻烦来过滤我的数据以消除重复。这是一个数据绑定练习，问题是“将列表代理ID更改为将在编辑模式下显示不同的Agent LastName的下拉列表”

My coding follows:

我的编码如下：

<form id="form1" runat="server">
        <asp:FormView ID="FormView1" runat="server" AllowPaging="True" BackColor="#DEBA84" BorderColor="#DEBA84" BorderStyle="None" BorderWidth="1px" CellPadding="3" CellSpacing="2" DataKeyNames="ListingID" DataSourceID="SqlDataSource1" GridLines="Both">
            <EditItemTemplate>
                ListingID:
                <asp:Label ID="ListingIDLabel1" runat="server" Text='<%# Eval("ListingID") %>' />
                <br />
                PropertyID:
                <asp:TextBox ID="PropertyIDTextBox" runat="server" Text='<%# Bind("PropertyID") %>' />
                <br />
                ListingAgentID:
                <asp:TextBox ID="ListingAgentIDTextBox" runat="server" Text='<%# Bind("ListingAgentID", "{0:N}") %>' />
                <asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource1" DataTextField="LastName" DataValueField="ListingAgentID" SelectedValue='<%# Bind("ListingAgentID", "{0}") %>'>
                </asp:DropDownList>
                <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RedwoodConnectionString %>" SelectCommand="SELECT Listings.ListingAgentID, Agents.LastName, Listings.ListingID, Listings.PropertyID, Listings.SaleStatusID, Listings.BeginListDate, Listings.EndListDate, Listings.AskingPrice, Agents.AgentID FROM Agents INNER JOIN Listings ON Agents.AgentID = Listings.ListingAgentID" UpdateCommand="UPDATE Listings SET ListingAgentID = FROM Listings INNER JOIN Agents ON Listings.ListingAgentID = Agents.AgentID"></asp:SqlDataSource>
                <br />
                SaleStatusID:
                <asp:TextBox ID="SaleStatusIDTextBox" runat="server" Text='<%# Bind("SaleStatusID") %>' />
                <br />
                BeginListDate:
                <asp:TextBox ID="BeginListDateTextBox" runat="server" Text='<%# Bind("BeginListDate") %>' />
                <br />
                EndListDate:
                <asp:TextBox ID="EndListDateTextBox" runat="server" Text='<%# Bind("EndListDate") %>' />
                <br />
                AskingPrice:
                <asp:TextBox ID="AskingPriceTextBox" runat="server" Text='<%# Bind("AskingPrice") %>' />
                <br />
                <asp:LinkButton ID="UpdateButton" runat="server" CausesValidation="True" CommandName="Update" Text="Update" />
            &nbsp;<asp:LinkButton ID="UpdateCancelButton" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel" />
            </EditItemTemplate>
            <EditRowStyle BackColor="#738A9C" Font-Bold="True" ForeColor="White" />
            <FooterStyle BackColor="#F7DFB5" ForeColor="#8C4510" />
            <HeaderStyle BackColor="#A55129" Font-Bold="True" ForeColor="White" />
            <InsertItemTemplate>
                ListingID:
                <asp:TextBox ID="ListingIDTextBox" runat="server" Text='<%# Bind("ListingID") %>' />
                <br />
                PropertyID:
                <asp:TextBox ID="PropertyIDTextBox" runat="server" Text='<%# Bind("PropertyID") %>' />
                <br />
                ListingAgentID:
                <asp:TextBox ID="ListingAgentIDTextBox" runat="server" Text='<%# Bind("ListingAgentID") %>' />
                <br />
                SaleStatusID:
                <asp:TextBox ID="SaleStatusIDTextBox" runat="server" Text='<%# Bind("SaleStatusID") %>' />
                <br />
                BeginListDate:
                <asp:TextBox ID="BeginListDateTextBox" runat="server" Text='<%# Bind("BeginListDate") %>' />
                <br />
                EndListDate:
                <asp:TextBox ID="EndListDateTextBox" runat="server" Text='<%# Bind("EndListDate") %>' />
                <br />
                AskingPrice:
                <asp:TextBox ID="AskingPriceTextBox" runat="server" Text='<%# Bind("AskingPrice") %>' />
                <br />
                <asp:LinkButton ID="InsertButton" runat="server" CausesValidation="True" CommandName="Insert" Text="Insert" />
                &nbsp;<asp:LinkButton ID="InsertCancelButton" runat="server" CausesValidation="False" CommandName="Cancel" Text="Cancel" />
            </InsertItemTemplate>
            <ItemTemplate>
                ListingID:
                <asp:Label ID="ListingIDLabel" runat="server" Text='<%# Eval("ListingID") %>' />
                <br />
                PropertyID:
                <asp:Label ID="PropertyIDLabel" runat="server" Text='<%# Bind("PropertyID") %>' />
                <br />
                ListingAgentID:
                <asp:Label ID="ListingAgentIDLabel" runat="server" Text='<%# Bind("ListingAgentID") %>' />
                <br />
                SaleStatusID:
                <asp:Label ID="SaleStatusIDLabel" runat="server" Text='<%# Bind("SaleStatusID") %>' />
                <br />
                BeginListDate:
                <asp:Label ID="BeginListDateLabel" runat="server" Text='<%# Bind("BeginListDate") %>' />
                <br />
                EndListDate:
                <asp:Label ID="EndListDateLabel" runat="server" Text='<%# Bind("EndListDate") %>' />
                <br />
                AskingPrice:
                <asp:Label ID="AskingPriceLabel" runat="server" Text='<%# Bind("AskingPrice") %>' />
                <br />
                <asp:LinkButton ID="EditButton" runat="server" CausesValidation="False" CommandName="Edit" Text="Edit" />
                &nbsp;<asp:LinkButton ID="DeleteButton" runat="server" CausesValidation="False" CommandName="Delete" Text="Delete" />
                &nbsp;<asp:LinkButton ID="NewButton" runat="server" CausesValidation="False" CommandName="New" Text="New" />
            </ItemTemplate>
            <PagerSettings FirstPageText="First" LastPageText="Last" NextPageText="Next" />
            <PagerStyle ForeColor="#8C4510" HorizontalAlign="Center" />
            <RowStyle BackColor="#FFF7E7" ForeColor="#8C4510" />
        </asp:FormView>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:RedwoodConnectionString %>" DeleteCommand="DELETE FROM [Listings] WHERE [ListingID] = @ListingID" InsertCommand="INSERT INTO [Listings] ([ListingID], [PropertyID], [ListingAgentID], [SaleStatusID], [BeginListDate], [EndListDate], [AskingPrice]) VALUES (@ListingID, @PropertyID, @ListingAgentID, @SaleStatusID, @BeginListDate, @EndListDate, @AskingPrice)" SelectCommand="SELECT Listings.ListingAgentID, Agents.LastName, Listings.ListingID, Listings.PropertyID, Listings.SaleStatusID, Listings.BeginListDate, Listings.EndListDate, Listings.AskingPrice, Agents.AgentID FROM Agents INNER JOIN Listings ON Agents.AgentID = Listings.ListingAgentID" UpdateCommand="UPDATE [Listings] SET [PropertyID] = @PropertyID, [ListingAgentID] = @ListingAgentID, [SaleStatusID] = @SaleStatusID, [BeginListDate] = @BeginListDate, [EndListDate] = @EndListDate, [AskingPrice] = @AskingPrice WHERE [ListingID] = @ListingID">
            <DeleteParameters>
                <asp:Parameter Name="ListingID" Type="Int32" />
            </DeleteParameters>
            <InsertParameters>
                <asp:Parameter Name="ListingID" Type="Int32" />
                <asp:Parameter Name="PropertyID" Type="Int32" />
                <asp:Parameter Name="ListingAgentID" Type="Int32" />
                <asp:Parameter Name="SaleStatusID" Type="Int32" />
                <asp:Parameter Name="BeginListDate" Type="DateTime" />
                <asp:Parameter Name="EndListDate" Type="DateTime" />
                <asp:Parameter Name="AskingPrice" Type="Decimal" />
            </InsertParameters>
            <UpdateParameters>
                <asp:Parameter Name="PropertyID" Type="Int32" />
                <asp:Parameter Name="ListingAgentID" Type="Int32" />
                <asp:Parameter Name="SaleStatusID" Type="Int32" />
                <asp:Parameter Name="BeginListDate" Type="DateTime" />
                <asp:Parameter Name="EndListDate" Type="DateTime" />
                <asp:Parameter Name="AskingPrice" Type="Decimal" />
                <asp:Parameter Name="ListingID" Type="Int32" />
            </UpdateParameters>
        </asp:SqlDataSource>
    <div> 

Thanks for your time and your help... I've spent countless hours trying to figure this out!!! :-) you are legendary!!!

感谢您的时间和帮助......我花了无数个小时试图解决这个问题！ :-)你是传奇!!!

1 个解决方案

#1

User distinct and group by in your SqlDataSource by which you are filling dropdown list.

用户在您填写下拉列表的SqlDataSource中进行区分和分组。

SELECT 
    distinct
    Listings.ListingAgentID, 
    Agents.LastName, 
    Listings.ListingID, 
    Listings.PropertyID, 
    Listings.SaleStatusID, 
    Listings.BeginListDate, 
    Listings.EndListDate, 
    Listings.AskingPrice, 
    Agents.AgentID 
FROM 
    Agents 
INNER JOIN 
    Listings 
ON 
    Agents.AgentID = Listings.ListingAgentID
group by
    Listings.ListingAgentID, 
    Agents.LastName, 
    Listings.ListingID, 
    Listings.PropertyID, 
    Listings.SaleStatusID, 
    Listings.BeginListDate, 
    Listings.EndListDate, 
    Listings.AskingPrice, 
    Agents.AgentID