下拉列表作为 SqlDataSource 的筛选器

本文关键字：筛选 SqlDataSource 下拉列表 | 更新日期: 2023-09-27 17:55:19

im 试图向我的网格视图添加搜索功能。我已经成功添加了两个文本框，并且所有内容都按预期运行，但是当我尝试添加下拉列表时，它不知何故不起作用，我不太明白为什么。这是我的代码：

<form id="form1" runat="server">
    <div>
    </div>
    <div>
        <asp:Label ID="LabelSearchTitle" runat="server" Text="Search Title"></asp:Label>
        <asp:TextBox ID="TextBoxSearchTitle" MaxLength="50" runat="server" Width="16%"></asp:TextBox>
        <asp:Label ID="LabelSearchArtist" runat="server" Text="Search Artist"></asp:Label>
        <asp:TextBox ID="TextBoxSearchArtist" MaxLength="50" runat="server" Width="16%"></asp:TextBox>
        <asp:DropDownList ID="DDLSearch" runat="server" Width="16%">
            <asp:ListItem Selected="True" Value="0">select Genre</asp:ListItem>
            <asp:ListItem Value="1">Pop</asp:ListItem>
            <asp:ListItem Value="2">Rock</asp:ListItem>
            <asp:ListItem Value="3">Dance</asp:ListItem>
            <asp:ListItem Value="4">Latin</asp:ListItem>
        </asp:DropDownList>
        <asp:Button ID="ButtonSearch" runat="server" Text="Search" Width="18%" OnClick="ButtonSearch_Click" />
    </div>
    <asp:GridView Width="80%" ID="myGridView" runat="server" AllowPaging="True" AllowSorting="True" AutoGenerateColumns="False" AutoGenerateDeleteButton="True" AutoGenerateEditButton="True" CellPadding="4" DataKeyNames="ID" DataSourceID="SqlDataSource1" ForeColor="#333333" GridLines="None" ShowFooter="True" OnRowDeleting="grv_Delete" OnRowUpdating="grv_Update">
        <AlternatingRowStyle BackColor="White" />
        <Columns>
            <asp:TemplateField HeaderText="ID" InsertVisible="False" SortExpression="ID">
                <EditItemTemplate>
                    <asp:Label ID="Label1" runat="server" Text='<%# Eval("ID") %>'></asp:Label>
                </EditItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="Label1" runat="server" Text='<%# Bind("ID") %>'></asp:Label>
                </ItemTemplate>
                <FooterTemplate>
                    <asp:LinkButton ValidationGroup="Insert" ID="linkButtonInsert" OnClick="linkButtonInsert_Click" runat="server">Insert</asp:LinkButton>
                </FooterTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Title" SortExpression="Title">
                <EditItemTemplate>
                    <asp:TextBox ID="textBoxEditTitle" MaxLength="50" runat="server" Text='<%# Bind("Title") %>'></asp:TextBox>
                    <asp:RequiredFieldValidator ID="rfvEditTitle" runat="server" ErrorMessage="Title is required"
                        Text="*" ForeColor="Red" ControlToValidate="textBoxEditTitle"></asp:RequiredFieldValidator>
                </EditItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="Label2" runat="server" Text='<%# Bind("Title") %>'></asp:Label>
                </ItemTemplate>
                <FooterTemplate>
                    <asp:TextBox ID="textBoxInsertTitle" MaxLength="50" runat="server"></asp:TextBox>
                    <asp:RequiredFieldValidator ID="rfvInsertTitle" runat="server" ErrorMessage="Title is required"
                        Text="*" ForeColor="Red" ValidationGroup="Insert" ControlToValidate="textBoxInsertTitle"></asp:RequiredFieldValidator>
                </FooterTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Artist" SortExpression="Artist">
                <EditItemTemplate>
                    <asp:TextBox ID="TextBoxEditArtist" MaxLength="50" runat="server" Text='<%# Bind("Artist") %>'></asp:TextBox>
                    <asp:RequiredFieldValidator ID="rfvEditArtist" runat="server" ErrorMessage="Artist is required"
                        Text="*" ForeColor="Red" ControlToValidate="TextBoxEditArtist"></asp:RequiredFieldValidator>
                </EditItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="Label3" runat="server" Text='<%# Bind("Artist") %>'></asp:Label>
                </ItemTemplate>
                <FooterTemplate>
                    <asp:TextBox ID="textBoxInsertArtist" MaxLength="50" runat="server"></asp:TextBox>
                    <asp:RequiredFieldValidator ID="rfvInsertArtist" runat="server" ErrorMessage="Artist is required"
                        Text="*" ForeColor="Red" ValidationGroup="Insert" ControlToValidate="textBoxInsertArtist"></asp:RequiredFieldValidator>
                </FooterTemplate>
            </asp:TemplateField>
            <asp:TemplateField HeaderText="Genre" SortExpression="Genre">
                <EditItemTemplate>
                    <asp:DropDownList ID="ddlEditGenre" runat="server" SelectedValue='<%# Bind("Genre") %>'>
                        <asp:ListItem>select Genre</asp:ListItem>
                        <asp:ListItem>Pop</asp:ListItem>
                        <asp:ListItem>Rock</asp:ListItem>
                        <asp:ListItem>Dance</asp:ListItem>
                        <asp:ListItem>Latin</asp:ListItem>
                    </asp:DropDownList>
                    <asp:RequiredFieldValidator ID="rfvEditGenre" runat="server" ErrorMessage="Genre is required"
                        Text="*" ForeColor="Red" ControlToValidate="ddlEditGenre" InitialValue="select Genre"></asp:RequiredFieldValidator>
                </EditItemTemplate>
                <ItemTemplate>
                    <asp:Label ID="Label4" runat="server" Text='<%# Bind("Genre") %>'></asp:Label>
                </ItemTemplate>
                <FooterTemplate>
                    <asp:DropDownList ID="ddlInsertGenre" runat="server">
                        <asp:ListItem>select Genre</asp:ListItem>
                        <asp:ListItem>Pop</asp:ListItem>
                        <asp:ListItem>Rock</asp:ListItem>
                        <asp:ListItem>Dance</asp:ListItem>
                        <asp:ListItem>Latin</asp:ListItem>
                    </asp:DropDownList>
                    <asp:RequiredFieldValidator ID="rfvInsertGenre" runat="server" ErrorMessage="Genre is required" InitialValue="select Genre"
                        Text="*" ForeColor="Red" ValidationGroup="Insert" ControlToValidate="ddlInsertGenre"></asp:RequiredFieldValidator>
                </FooterTemplate>
            </asp:TemplateField>
        </Columns>
        <EditRowStyle BackColor="#2461BF" />
        <FooterStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <HeaderStyle BackColor="#507CD1" Font-Bold="True" ForeColor="White" />
        <PagerStyle BackColor="#2461BF" ForeColor="White" HorizontalAlign="Center" />
        <RowStyle BackColor="#EFF3FB" />
        <SelectedRowStyle BackColor="#D1DDF1" Font-Bold="True" ForeColor="#333333" />
        <SortedAscendingCellStyle BackColor="#F5F7FB" />
        <SortedAscendingHeaderStyle BackColor="#6D95E1" />
        <SortedDescendingCellStyle BackColor="#E9EBEF" />
        <SortedDescendingHeaderStyle BackColor="#4870BE" />
    </asp:GridView>
    <asp:Label ID="LabelAlreadyExists" runat="server" Text=""></asp:Label>
    <asp:ValidationSummary ValidationGroup="Insert" runat="server" ForeColor="Red"></asp:ValidationSummary>
    <asp:ValidationSummary runat="server" ForeColor="Red"></asp:ValidationSummary>
    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DBCS %>" SelectCommand="select tblSong.ID ,Title, Artist, Genre from tblSong JOIN tblArtist on ArtistId = tblArtist.ID
        JOIN tblGenre on GenreId = tblGenre.ID;"
        UpdateCommand="select tblSong.ID ,Title, Artist, Genre from tblSong JOIN tblArtist on ArtistId = tblArtist.ID JOIN tblGenre on GenreId = tblGenre.ID;"
        DeleteCommand="select tblSong.ID ,Title, Artist, Genre from tblSong JOIN tblArtist on ArtistId = tblArtist.ID JOIN tblGenre on GenreId = tblGenre.ID;"
        FilterExpression="Title LIKE '%{0}%' AND Artist LIKE '%{1}%' AND Genre LIKE '{2}'">
        <FilterParameters>
            <asp:ControlParameter ConvertEmptyStringToNull="false" ControlID="TextBoxSearchTitle" DefaultValue="" Name="Title" PropertyName="Text" />
            <asp:ControlParameter ConvertEmptyStringToNull="false" ControlID="TextBoxSearchArtist" DefaultValue="" Name="Artist" PropertyName="Text" />
            <asp:ControlParameter ControlID="DDLSearch" Type="String" Name="Genre" PropertyName="SelectedValue" />
        </FilterParameters>
    </asp:SqlDataSource>
</form>

当我运行此代码时，网格视图不可见。我看到的只是两个标签，两个textboes和第二个中的下拉列表。谁能向我解释为什么？

提前感谢！

编辑：

    <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:DBCS %>" SelectCommand="select tblSong.ID ,Title, Artist, Genre from tblSong JOIN tblArtist on ArtistId = tblArtist.ID
        JOIN tblGenre on GenreId = tblGenre.ID;"
        UpdateCommand="select tblSong.ID ,Title, Artist, Genre from tblSong JOIN tblArtist on ArtistId = tblArtist.ID JOIN tblGenre on GenreId = tblGenre.ID;"
        DeleteCommand="select tblSong.ID ,Title, Artist, Genre from tblSong JOIN tblArtist on ArtistId = tblArtist.ID JOIN tblGenre on GenreId = tblGenre.ID;"
        FilterExpression="Title LIKE '%{0}%' AND Artist LIKE '%{1}%' AND GenreId ={2}">
        <FilterParameters>
            <asp:ControlParameter ConvertEmptyStringToNull="false" ControlID="TextBoxSearchTitle" DefaultValue="" Name="Title" PropertyName="Text" />
            <asp:ControlParameter ConvertEmptyStringToNull="false" ControlID="TextBoxSearchArtist" DefaultValue="" Name="Artist" PropertyName="Text" />
            <asp:ControlParameter ControlID="DDLSearch" Type="Int16" Name="Genre" PropertyName="SelectedValue" />
        </FilterParameters>
    </asp:SqlDataSource>

下拉列表作为 SqlDataSource 的筛选器

DDLSearch 过滤器基于 SelectedValue ，它使用 GenreID {1,2,3,4} 进行过滤，而不是流派名称。将FilterExpression更改为使用 ID 而不是名称：

AND GenreID ='{2}'

此外，如果 GenreID 是数据库中的整数，则将 DDLSearch ControlParameter 更改为使用整数数据类型而不是 String，并更改 FilterExpression 以删除单个刻度，以便 SQL Server 在查找 GenreID 时不会执行从 int 到 varchar 的隐式转换。

AND GenreID ={2}

另请注意，如果您计划在tblSong表中包含大量数据，那么最好改用SelectParameters。 SelectParameters将在将结果返回到 ASP.NET （msdn.microsoft.com/en-us/library/z72eefad.aspx）之前在 SQL Server 端进行筛选。另一方面，FilterExpressions将从SQL Server获取所有记录，然后在 ASP.NET 端缓存结果和过滤器。