使用列表框的asp:gridview过滤器无法进行多项选择 [英] asp:gridview filter using listbox cannot make multiple selection

问题描述

我有这个asp:gridview，其中使用mySql存储过程显示数据.我有一个名为ddlstatus的列表框，用于过滤数据.我使用viewstate显示从列表框中选择的数据.问题是我想在此列表框上进行多个选择，并显示对其进行的每个选择的数据，但是当它仅显示初始选择的数据时.

I have this asp:gridview in which I show data using mySql stored procedure. I have this listbox named ddlstatus which I use to filter the data. I use viewstate to show data that are selected from the listbox. The problem is I want to make multiple selection on this listbox and show data for each selection made on it, but when it only shows data for the initial selection.

以下是客户端代码:

<asp:Label ID="lblstat" Text="status" Visible="false" runat="server"></asp:Label>
<asp:ListBox ID="ddlstatus" runat="server" OnSelectedIndexChanged="DropDownChange" AutoPostBack="true" AppendDataBoundItems="true" SelectionMode="Multiple"></asp:ListBox>

<asp:GridView ID="gdvTM" runat="server" ControlStyle-Width="100%"  AutoGenerateColumns="False" DataKeyNames="ID" OnRowDeleting="gdvTM_RowDeleting" PageSize="5" CssClass="cssgridview" AlternatingRowStyle-BackColor="#d5d8dc">
    <Columns >
       <asp:TemplateField HeaderText="Current Status">
         <ItemTemplate >
             <asp:Label ID="lblcstat" runat="server" Text='<%# Eval("status") %>'></asp:Label>
        </ItemTemplate>
      </asp:TemplateField>
    </Columns>
</asp:GridView>

以下是服务器端代码:

private void BindDropDownList()
{
    PopulateDropDown(ddlstatus, lblstat.Text);
}
private void PopulateDropDown(ListBox ddl, string columnName)
{
    ddl.Items.Clear();
    ddl.DataSource = BindDropDown(columnName);
    ddl.DataTextField = columnName;
    ddl.DataValueField = columnName;
    ddl.DataBind();
    ddl.Items.Insert(0, new ListItem("Please select", "0"));
}
private void BindGrid()
{
    DataTable dt = new DataTable();
    String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
    MySqlConnection con = new MySqlConnection(strConnString);
    MySqlDataAdapter sda = new MySqlDataAdapter();
    MySqlCommand cmd = new MySqlCommand("GetTMData");
    cmd.CommandType = CommandType.StoredProcedure;  
    string statusVal = null;
    if (ViewState["stat"] != null && ViewState["stat"].ToString() != "0")
    {
        statusVal = ViewState["stat"].ToString();
    }
    cmd.Parameters.AddWithValue("statusVal", statusVal);
    cmd.Connection = con;
    sda.SelectCommand = cmd;
    sda.Fill(dt);
    gdvTM.DataSource = dt;
    int i = dt.Rows.Count;
    gdvTM.DataBind();
    this.BindDropDownList();
    TableCell cell = gdvTM.HeaderRow.Cells[0];
    setDropdownselectedItem(ViewState["stat"] != null ? (string)ViewState["stat"] : string.Empty, ddlstatus);
}
private void setDropdownselectedItem(string selectedvalue, ListBox ddl)
{
    if (!string.IsNullOrEmpty(selectedvalue))
    {
         ddl.Items.FindByValue(selectedvalue).Selected = true;

    }
}
protected void DropDownChange(object sender, EventArgs e)
 {
      ListBox dropdown = (ListBox)sender;
      string selectedValue = dropdown.SelectedItem.Value;
      switch (dropdown.ID.ToLower())
      {
          case "ddlstatus":
             ViewState["stat"] = selectedValue;
             break;
      }

      this.BindGrid();
 }

private DataTable BindDropDown(string columnName)
{
    string username = uName.Text;
    String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
    MySqlConnection con = new MySqlConnection(strConnString);
    MySqlCommand cmd = new MySqlCommand("SELECT DISTINCT (" + columnName + ") FROM approved WHERE tm = @tm AND " + columnName + " IS NOT NULL", con);
    MySqlDataAdapter sda = new MySqlDataAdapter(cmd);
    cmd.Parameters.AddWithValue("@tm", username);
    DataTable dt = new DataTable();
    sda.Fill(dt);
    return dt;
}

下面是MySql存储过程:

Below is the MySql stored procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetTMData`(in statusVal varchar(45))
BEGIN
SELECT *
   FROM approved
   WHERE (statusVal IS NULL
                OR status = statusVal)
         order by date desc;
END

我该如何做到这一点?预先感谢.

How can I make this happen? Thanks in advance.

推荐答案

首先，自动回发不允许您选择多个项目，因为要选择第二个项目，回发已经发生在第一个选定的项目上，所以

First of all, auto post back not allow you to select multiple items because upto select second item, the postback already happens by first selected item so,

您必须为列表框设置AutoPostBack="false"，

<asp:ListBox ID="ddlstatus" runat="server" AutoPostBack="false" AppendDataBoundItems="true" SelectionMode="Multiple"></asp:ListBox>

例如，要收集多个选定的项目，我们只需选择按钮即可，您可以在任意位置收集这些项目，

For collecting multiple selected items we simply choose button for instance, you can collect those items wherever you want,

然后添加一个按钮，该按钮将调用下面的代码

Then add one button that will call below code

<asp:Button ID="button1" runat="server" OnClick="button1_Click" Text="Click"/>

在按钮事件处理程序上方，添加以下代码，

On above button event handler add below code,

protected void button1_Click(object sender, EventArgs e)
{
    var selectedNames = ddlstatus.Items.Cast<ListItem>()
                         .Where(i => i.Selected)
                         .Select(i => i.Value)
                         .ToList();

    string selectedValue = string.Join("','", selectedNames);

    selectedValue = "'" + selectedValue + "'";

    ViewState["stat"] = selectedValue;
}

然后ViewState中用逗号分隔的项目将用于存储过程参数

Then the comma separated items in your ViewState will be used in your stored procedure parameter

string statusVal = null;
if (ViewState["stat"] != null && ViewState["stat"].ToString() != "0")
{
    statusVal = ViewState["stat"].ToString();
}
cmd.Parameters.AddWithValue("statusVal", statusVal);  //<= Now this string variable contains comma separated list box items values.

如果您在Page_Load上填充列表框，请确保将其填充到!Page.IsPostBack中，如

If you populate your list box on Page_Load then make sure that you should populate it into !Page.IsPostBack like

protected void Page_Load(object sender, EventArgs e)
{
    if (!Page.IsPostBack)
    {
        //Populate your list box here
    }
}

您的SP是

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetTMData1`(in statusVal varchar(255))
BEGIN

IF statusVal = '\'\'' THEN 
   select * from approved;
ELSE
  SET @sql = CONCAT('SELECT * FROM approved WHERE status IN (', statusVal, ')');
  PREPARE stmt FROM @sql;
  EXECUTE stmt;
  DEALLOCATE PREPARE stmt;    
END IF ;        
END

如果从下拉列表中选择多个项目，则SP的参数数据看起来像'\'apple\',\'banana\''.如果没有，则它看起来像'\''.

If you select multiple items from the dropdown then your SP's parameter data look like '\'apple\',\'banana\''. If not then it look like '\''.

这篇关于使用列表框的asp:gridview过滤器无法进行多项选择的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！