多选Organization时Policy Type与Coverage Type筛选无结果问题排查

我有一个支持多选的Organization ListBox，希望根据选中的Organization ID筛选对应的Policy Type和Coverage Type。单选时功能正常，但选中多个组织（如"1,2,5"）时无结果返回，筛选失效。相关代码如下：

HTML代码 #

<div class="col-lg-4 col-md-12 col-sm-12 col-xs-12">
    <div class="chosen-select-single mg-b-20">
        <label style="color: red;">*</label><label>Organization:</label>
        <br />
        <asp:ListBox ID="lst_organization" runat="server" SelectionMode="Multiple" CssClass="form-control" AutoPostBack="true" OnSelectedIndexChanged="ddl_organization_OnSelectedIndexChanged"></asp:ListBox>
        <asp:RequiredFieldValidator ID="rfv_organization" runat="server" ControlToValidate="lst_organization"
            Display="Dynamic" ValidationGroup="save" InitialValue="" ErrorMessage="Select Organization" ForeColor="Red"></asp:RequiredFieldValidator>
    </div>
</div>

后台C#代码 #

protected void ddl_organization_OnSelectedIndexChanged(object sender, EventArgs e)
{
    try
    {
        StringBuilder sc = new StringBuilder();

        if (lst_organization.SelectedValue != null)
        {
            if (lst_organization.SelectedIndex != 0 || lst_organization.SelectedIndex != -1)
            {
                //To get [Policy Type] as per [organization ID] -----
                cam.fill_DDL(ddl_policy_type, "POLICY_TYPE", "POLICY_TYPE_ID", "get_policy_type_as_per_organization",
                    lst_organization.SelectedValue.ToString().Trim(), "", "", "", "", "", "");

                //To get [Coverage Type] as per [organization ID] -----
                cam.fill_DDL(ddl_coverage_type, "COVERAGE_TYPE", "COVERAGE_TYPE_ID", "get_coverage_type_as_per_organization",
                   lst_organization.SelectedValue.ToString().Trim(), "", "", "", "", "", "");
            }
        }
    }
    catch (Exception ex)
    {
        string script = "alert(\"Exception in getting [Policy/Coverage Type] as per selected [Organization] - Insurance Policy Details :'" + ex.Message.ToString() + "'.\");";
        ScriptManager.RegisterStartupScript(this, GetType(), "Information", script, true);
    }
}

Coverage Type查询SQL #

select distinct mct.COVERAGE_TYPE,mct.COVERAGE_TYPE_ID,mo.ORGANIZATION
        from A_COVERAGE_TYPE_ORGANIZATION as acto
        inner join dbo.M_COVERAGE_TYPE as mct on acto.COVERAGE_TYPE_ID=mct.COVERAGE_TYPE_ID
        inner join dbo.M_ORGANIZATION as mo on acto.ORGANIZATION_ID=mo.ORGANIZATION_ID
        inner join dbo.M_DIVISION as md on mo.DIVISION_ID=md.DIVISION_ID
        where acto.ISDELETED='False' and acto.ORGANIZATION_ID=@SEARCH_ID1
        group by mct.COVERAGE_TYPE_ID, mct.COVERAGE_TYPE,mo.ORGANIZATION
        order by mct.COVERAGE_TYPE_ID asc

• 多选值未正确获取：lst_organization.SelectedValue仅返回第一个选中项的ID，无法获取所有选中的组织ID集合，导致传递给SQL的参数始终是单个ID。
• SQL不支持多ID匹配：当前SQL用acto.ORGANIZATION_ID=@SEARCH_ID1做等值匹配，只能处理单个ID，无法匹配多个组织ID。
• 无效的选中判断逻辑：lst_organization.SelectedIndex != 0 || lst_organization.SelectedIndex != -1这个条件恒为true，因为SelectedIndex不可能同时等于0和-1，起不到实际判断作用。

1. 修改后台代码，收集所有选中的组织ID #

遍历ListBox的Items集合，收集所有选中项的Value，拼接成逗号分隔的字符串传递给SQL：

protected void ddl_organization_OnSelectedIndexChanged(object sender, EventArgs e)
{
    try
    {
        List<string> selectedOrgIds = new List<string>();
        // 遍历所有选项，收集选中的ID
        foreach (ListItem item in lst_organization.Items)
        {
            if (item.Selected)
            {
                selectedOrgIds.Add(item.Value.Trim());
            }
        }

        if (selectedOrgIds.Count > 0)
        {
            string orgIdsStr = string.Join(",", selectedOrgIds);
            // 传递多ID字符串到下拉框填充方法
            cam.fill_DDL(ddl_policy_type, "POLICY_TYPE", "POLICY_TYPE_ID", "get_policy_type_as_per_organization",
                orgIdsStr, "", "", "", "", "", "");

            cam.fill_DDL(ddl_coverage_type, "COVERAGE_TYPE", "COVERAGE_TYPE_ID", "get_coverage_type_as_per_organization",
               orgIdsStr, "", "", "", "", "", "");
        }
        else
        {
            // 无选中项时清空下拉框
            ddl_policy_type.Items.Clear();
            ddl_coverage_type.Items.Clear();
        }
    }
    catch (Exception ex)
    {
        string script = $"alert(\"Exception in getting [Policy/Coverage Type] as per selected [Organization] - Insurance Policy Details :'{ex.Message}'.\");";
        ScriptManager.RegisterStartupScript(this, GetType(), "Information", script, true);
    }
}

2. 修改SQL查询，支持多ID的IN匹配 #

使用STRING_SPLIT函数拆分逗号分隔的ID字符串，配合IN子句实现多组织筛选（适用于SQL Server 2016及以上版本）：

select distinct mct.COVERAGE_TYPE,mct.COVERAGE_TYPE_ID,mo.ORGANIZATION
from A_COVERAGE_TYPE_ORGANIZATION as acto
inner join dbo.M_COVERAGE_TYPE as mct on acto.COVERAGE_TYPE_ID=mct.COVERAGE_TYPE_ID
inner join dbo.M_ORGANIZATION as mo on acto.ORGANIZATION_ID=mo.ORGANIZATION_ID
inner join dbo.M_DIVISION as md on mo.DIVISION_ID=md.DIVISION_ID
where acto.ISDELETED='False' 
and acto.ORGANIZATION_ID IN (SELECT TRY_CAST(value AS INT) FROM STRING_SPLIT(@SEARCH_ID1, ','))
group by mct.COVERAGE_TYPE_ID, mct.COVERAGE_TYPE,mo.ORGANIZATION
order by mct.COVERAGE_TYPE_ID asc

注意：如果使用旧版SQL Server（不支持STRING_SPLIT），可以替换为自定义字符串拆分函数，或者改用表值参数传递选中的ID集合。

3. 移除无效判断逻辑 #

原代码中if (lst_organization.SelectedIndex != 0 || lst_organization.SelectedIndex != -1)无实际作用，直接通过selectedOrgIds.Count > 0判断是否有选中项即可。

内容的提问来源于stack exchange，提问作者Rutuja Jadhav