使用多个列上的下拉列表筛选 asp 网格视图标头

Question

所以我尝试为 gridview 添加多个过滤器，并用列的不同数据填充下拉列表，但它给了我一个例外，System.ArgumentException：在集合中找不到参数"siteValue"。我添加了 mysql 存储过程以及下面的代码。

<asp:GridView ID="gdvTM" runat="server" AutoGenerateColumns="False" AllowPaging="true" OnPageIndexChanging="gdvTM_PageIndexChanging" DataKeyNames="ID" PageSize="10" CssClass="cssgridview" AlternatingRowStyle-BackColor="#d5d8dc" >
<Columns >
<asp:TemplateField   HeaderText="Agent login">
<ItemTemplate>
<asp:Label ID="lbllogin" runat="server" Text='<%# Eval("agentlogin") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField >
<HeaderTemplate>
Site:
<asp:DropDownList ID="ddlgvsite" runat="server" OnSelectedIndexChanged="DropDownChange" AutoPostBack="true" AppendDataBoundItems="true">
</asp:DropDownList>
</HeaderTemplate>
<ItemTemplate>
<asp:Label ID="lblsite" runat="server" Text='<%# Eval("site") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField>
<HeaderTemplate>
Skill:
<asp:DropDownList ID="ddlgvskill" runat="server" OnSelectedIndexChanged="DropDownChange" AutoPostBack="true" AppendDataBoundItems="true">
</asp:DropDownList>
</HeaderTemplate>
<ItemTemplate>
<asp:Label ID="lblskill" runat="server" Text='<%# Eval("skill") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Shift">
<ItemTemplate>
<asp:Label ID="lblshift" runat="server" Text='<%# Eval("shift") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="TM">
<ItemTemplate>
<asp:Label ID="lbltm" runat="server" Text='<%# Eval("tm") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="GrpM">
<ItemTemplate>
<asp:Label ID="lblGrpM" runat="server" Text='<%# Eval("grpM") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="OpsM">
<ItemTemplate>
<asp:Label ID="lblOpsM" runat="server" Text='<%# Eval("opsM") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Leave type">
<ItemTemplate>
<asp:Label ID="lbltype" runat="server" Text='<%# Eval("leavetype") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Date">
<ItemTemplate >
<asp:Label ID="lbldate" runat="server" Text='<%# Eval("date", "{0:dd/MM/yyyy}") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Time">
<ItemTemplate>
<asp:Label ID="lbltime" runat="server" Text='<%# Eval("time") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Reason" ControlStyle-Width="300px">
<ItemTemplate>
<asp:Label ID="lblreason" runat="server" Text='<%# Eval("reason") %>'></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Columns>
</asp:GridView>

以下是服务器端代码

protected void Page_Load(object sender, EventArgs e)
{
if (!IsPostBack)
{
BindGrid();
}
}
private void BindDropDownList()
{
TableCell cells = gdvTM.HeaderRow.Cells[0];
PopulateDropDown((cells.FindControl("ddlgvsite") as DropDownList), (cells.FindControl("lblsite") as Label).Text);
PopulateDropDown((cells.FindControl("ddlgvskill") as DropDownList), (cells.FindControl("lblskill") as Label).Text);
}
private void PopulateDropDown(DropDownList ddl, string columnName)
{
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("GetApprovedData");
cmd.CommandType = CommandType.StoredProcedure;
if (ViewState["Site"] != null && ViewState["Site"].ToString() != "0")
{
cmd.Parameters.AddWithValue("@siteValue", ViewState["Site"].ToString());
}
if (ViewState["Skill"] != null && ViewState["Skill"].ToString() != "0")
{
cmd.Parameters.AddWithValue("@skillValue", ViewState["Skill"].ToString());
}
cmd.Connection = con;
sda.SelectCommand = cmd;
sda.Fill(dt);
gdvTM.DataSource = dt;
gdvTM.DataBind();
this.BindDropDownList();
TableCell cell = gdvTM.HeaderRow.Cells[0];
setDropdownselectedItem(ViewState["Site"] != null ? (string)ViewState["Site"] : string.Empty, cell.FindControl("ddlgvsite") as DropDownList);
setDropdownselectedItem(ViewState["Skill"] != null ? (string)ViewState["Skill"] : string.Empty, cell.FindControl("ddlgvskill") as DropDownList);
}
private void setDropdownselectedItem(string selectedvalue, DropDownList ddl)
{
if (!string.IsNullOrEmpty(selectedvalue))
{
ddl.Items.FindByValue(selectedvalue).Selected = true;
}
}
protected void DropDownChange(object sender, EventArgs e)
{
DropDownList dropdown = (DropDownList)sender;
string selectedValue = dropdown.SelectedItem.Value;
switch (dropdown.ID.ToLower())
{
case "ddlgvsite":
ViewState["Site"] = selectedValue;
break;
case "ddlgvskill":
ViewState["Skill"] = selectedValue;
break;
}
this.BindGrid();
}
private DataTable BindDropDown(string columnName)
{
String strConnString = System.Configuration.ConfigurationManager.ConnectionStrings["connStr"].ConnectionString;
MySqlConnection con = new MySqlConnection(strConnString);
MySqlCommand cmd = new MySqlCommand("SELECT DISTINCT " + columnName + " FROM approved WHERE " + columnName + " IS NOT NULL", con);
MySqlDataAdapter sda = new MySqlDataAdapter(cmd);
DataTable dt = new DataTable();
sda.Fill(dt);
return dt;
}
protected void gdvTM_PageIndexChanging(object sender, GridViewPageEventArgs e)
{
gdvTM.PageIndex = e.NewPageIndex;
this.BindGrid();
}

下面是 MySql 存储过程

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetApprovedData`
(in  siteValue varchar(45), in skillValue varchar(100))
BEGIN
select *
from approved
where site = siteValue or siteValue is null and 
skill = skillValue or skillValue is null;
END

请让我知道我做错了什么。提前谢谢....

Answer 1

1(你必须改变你的存储过程，比如

CREATE DEFINER=`root`@`localhost` PROCEDURE `GetApprovedData`(in siteValue varchar(45),
in skillValue varchar(100))
BEGIN
IF siteValue IS NULL and skillValue IS NULL THEN 
select * from approved;
ELSEIF siteValue IS NULL and skillValue IS NOT NULL THEN
select * from approved where skill = skillValue;
ELSEIF siteValue IS NOT NULL and skillValue IS NULL THEN
select * from approved where site = siteValue;
ELSE        
select * from approved where site = siteValue and skill = skillValue;
END IF; 
END

2( 在您的BindGrid函数中进行以下更改

private void BindGrid()
{
//Your rest of code is same here
string siteValue = null;
string skillValue = null;
if (ViewState["Site"] != null && ViewState["Site"].ToString() != "0")
{
siteValue = ViewState["Site"].ToString();
}
if (ViewState["Skill"] != null && ViewState["Skill"].ToString() != "0")
{
skillValue = ViewState["Skill"].ToString();
}
cmd.Parameters.AddWithValue("siteValue", siteValue);
cmd.Parameters.AddWithValue("skillValue", skillValue);
//Your rest of code is same here
}