如何基于过滤后的GridView更新下拉列表
本文关键字：GridView 更新 下拉列表 何基于 过滤 | 更新日期: 2023-09-27 18:18:40
我有以下代码，这是一个GridView，在它的正上方我有DropDownList，允许我过滤:
<table class="taskGridView">
<tr>
<td style="width: 25%;">
<asp:DropDownList ID="ddlTaskName" CssClass="chosen-select" DataSourceID="dsPopulateTaskName" AutoPostBack="true" DataValueField="Task Name" runat="server" Width="100%" Font-Size="11px" AppendDataBoundItems="true" OnSelectedIndexChanged="ddlTaskName_onSelectIndexChanged">
<asp:ListItem Text="All" Value="%"></asp:ListItem>
</asp:DropDownList>
<asp:SqlDataSource ID="dsPopulateTaskName" runat="server" ConnectionString="<%$ ConnectionStrings:gvConnString %>" SelectCommand="SELECT DISTINCT [ATTR2739] 'Task Name' FROM HSI.RMOBJECTINSTANCE1224 CT INNER JOIN HSI.RMOBJECTINSTANCE1232 S ON CT.ATTR2846 = S.ATTR2821 INNER JOIN HSI.USERACCOUNT UA ON S.FK2852 = (UA.USERNUM * -1) WHERE CT.ACTIVESTATUS = 0"></asp:SqlDataSource>
</td>
<td style="width: 20%;">
<asp:DropDownList ID="ddlService" CssClass="chosen-select" DataSourceID="dsPopulateService" AutoPostBack="true" DataValueField="Service" runat="server" Width="100%" Font-Size="11px" AppendDataBoundItems="true" OnSelectedIndexChanged="ddlService_onSelectIndexChanged">
<asp:ListItem Text="All" Value="%"></asp:ListItem>
</asp:DropDownList>
<asp:SqlDataSource ID="dsPopulateService" runat="server" ConnectionString="<%$ ConnectionStrings:gvConnString %>" SelectCommand="SELECT DISTINCT [ATTR2846] 'Service' FROM HSI.RMOBJECTINSTANCE1224 CT INNER JOIN HSI.RMOBJECTINSTANCE1232 S ON CT.ATTR2846 = S.ATTR2821 INNER JOIN HSI.USERACCOUNT UA ON S.FK2852 = (UA.USERNUM * -1) WHERE CT.ACTIVESTATUS = 0"></asp:SqlDataSource>
</td>
<td style="width: 11%;">
<asp:DropDownList ID="ddlStatus" CssClass="chosen-select" DataSourceID="dsPopulateStatus" AutoPostBack="true" DataValueField="Status" runat="server" Width="100%" Font-Size="11px" AppendDataBoundItems="true" OnSelectedIndexChanged="ddlStatus_onSelectIndexChanged">
<asp:ListItem Text="All" Value="%"></asp:ListItem>
</asp:DropDownList>
<asp:SqlDataSource ID="dsPopulateStatus" runat="server" ConnectionString="<%$ ConnectionStrings:gvConnString %>" SelectCommand="SELECT DISTINCT [ATTR2812] 'Status' FROM HSI.RMOBJECTINSTANCE1224 CT INNER JOIN HSI.RMOBJECTINSTANCE1232 S ON CT.ATTR2846 = S.ATTR2821 INNER JOIN HSI.USERACCOUNT UA ON S.FK2852 = (UA.USERNUM * -1) WHERE CT.ACTIVESTATUS = 0"></asp:SqlDataSource>
</td>
<td style="width: 14%;">
<asp:DropDownList ID="ddlDueDate" CssClass="chosen-select" DataSourceID="dsPopulateDueDate" AutoPostBack="true" DataValueField="Due Date" runat="server" Width="100%" Font-Size="11px" AppendDataBoundItems="true" OnSelectedIndexChanged="ddlDueDate_onSelectIndexChanged">
<asp:ListItem Text="All" Value="%"></asp:ListItem>
</asp:DropDownList>
<asp:SqlDataSource ID="dsPopulateDueDate" runat="server" ConnectionString="<%$ ConnectionStrings:gvConnString %>" SelectCommand="SELECT DISTINCT CONVERT(VARCHAR(14), [ATTR2752], 110) 'Due Date' FROM HSI.RMOBJECTINSTANCE1224 CT INNER JOIN HSI.RMOBJECTINSTANCE1232 S ON CT.ATTR2846 = S.ATTR2821 INNER JOIN HSI.USERACCOUNT UA ON S.FK2852 = (UA.USERNUM * -1) WHERE CT.ACTIVESTATUS = 0"></asp:SqlDataSource>
</td>
<td style="width: 15%;">
<asp:DropDownList ID="ddlOwner" CssClass="chosen-select" DataSourceID="dsPopulateOwner" AutoPostBack="true" DataValueField="Owner" runat="server" Width="100%" Font-Size="11px" AppendDataBoundItems="true" OnSelectedIndexChanged="ddlOwner_onSelectIndexChanged">
<asp:ListItem Text="All" Value="%"></asp:ListItem>
</asp:DropDownList>
<asp:SqlDataSource ID="dsPopulateOwner" runat="server" ConnectionString="<%$ ConnectionStrings:gvConnString %>" SelectCommand="SELECT DISTINCT [REALNAME] 'Owner' FROM HSI.RMOBJECTINSTANCE1224 CT INNER JOIN HSI.RMOBJECTINSTANCE1232 S ON CT.ATTR2846 = S.ATTR2821 INNER JOIN HSI.USERACCOUNT UA ON S.FK2852 = (UA.USERNUM * -1) WHERE CT.ACTIVESTATUS = 0"></asp:SqlDataSource>
</td>
<td style="width: 15%;">
<asp:DropDownList ID="ddlClient" CssClass="chosen-select" DataSourceID="dsPopulateClient" AutoPostBack="true" DataValueField="Client" runat="server" Width="100%" Font-Size="11px" AppendDataBoundItems="true" OnSelectedIndexChanged="ddlClient_onSelectIndexChanged">
<asp:ListItem Text="All" Value="%"></asp:ListItem>
</asp:DropDownList>
<asp:SqlDataSource ID="dsPopulateClient" runat="server" ConnectionString="<%$ ConnectionStrings:gvConnString %>" SelectCommand="SELECT DISTINCT [ATTR2799] 'Client' FROM HSI.RMOBJECTINSTANCE1224 CT INNER JOIN HSI.RMOBJECTINSTANCE1232 S ON CT.ATTR2846 = S.ATTR2821 INNER JOIN HSI.USERACCOUNT UA ON S.FK2852 = (UA.USERNUM * -1) WHERE CT.ACTIVESTATUS = 0"></asp:SqlDataSource>
</td>
</tr>
</table>
<asp:GridView ShowHeaderWhenEmpty="false" AlternatingRowStyle-BackColor="#EBE9E9" AutoGenerateColumns="false" OnSorting="yourTasksGV_Sorting" AllowSorting="true" ID="yourTasksGV" runat="server" ClientIDMode="Static" EmptyDataText="There is no data to display" OnRowDataBound="yourTasksGV_RowDataBound" OnRowCreated="yourTasksGV_RowCreated">
<Columns>
<asp:HyperLinkField Target="_blank" DataNavigateUrlFields="Task Detail" DataTextField="Task Name" DataNavigateUrlFormatString="" HeaderText="Task Detail" SortExpression="Task Name" ItemStyle-Width="25%" ItemStyle-CssClass="taskTableColumn" />
<asp:BoundField DataField="Service" HeaderText="Service" SortExpression="Service" ItemStyle-Width="20%" ItemStyle-CssClass="taskTableColumn" />
<asp:BoundField DataField="Status" HeaderText="Status" SortExpression="Status" ItemStyle-Width="10%" ItemStyle-CssClass="taskTableColumn" />
<asp:BoundField DataField="Due Date" HeaderText="Due Date" SortExpression="Due Date" ItemStyle-Width="15%" ItemStyle-CssClass="taskTableColumn" />
<asp:BoundField DataField="Owner" HeaderText="Owner" SortExpression="Owner" ItemStyle-Width="15%" ItemStyle-CssClass="taskTableColumn" />
<asp:BoundField DataField="Client" HeaderText="Client" SortExpression="Client" ItemStyle-Width="15%" ItemStyle-CssClass="taskTableColumn" />
</Columns>
</asp:GridView>
public void PullData(string sortExp, string sortDir)
{
string query = "";
DataTable taskData = new DataTable();
connString = @""; //my connection string
if (ddlTaskName.SelectedIndex > 0)
{
strClause += " AND CT.ATTR2739 = '" + ddlTaskName.SelectedItem.Text + "'";
}
else
{
strClause += " AND CT.ATTR2739 LIKE '%'";
}
if (ddlService.SelectedIndex > 0)
{
strClause += " AND CT.ATTR2846 = '" + ddlService.SelectedItem.Text + "'";
}
else
{
strClause += " AND CT.ATTR2846 LIKE '%'";
}
if (ddlStatus.SelectedIndex > 0)
{
strClause += " AND CT.ATTR2812 = '" + ddlStatus.SelectedItem.Text + "'";
}
else
{
strClause += " AND CT.ATTR2812 LIKE '%'";
}
if (ddlDueDate.SelectedIndex > 0)
{
strClause += " AND CONVERT(VARCHAR(14), CT.ATTR2752, 110) = '" + ddlDueDate.SelectedItem.Text + "'";
}
else
{
strClause += " AND CONVERT(VARCHAR(14), CT.ATTR2752, 110) LIKE '%'";
}
if (ddlOwner.SelectedIndex > 0)
{
strClause += " AND UA.REALNAME = '" + ddlOwner.SelectedItem.Text + "'";
}
else
{
strClause += " AND UA.REALNAME LIKE '%'";
}
if (ddlClient.SelectedIndex > 0)
{
strClause += " AND CT.ATTR2799 = '" + ddlClient.SelectedItem.Text + "'";
}
else
{
strClause += " AND CT.ATTR2799 LIKE '%'";
}
if (ddlTaskName.SelectedIndex == 0 && ddlService.SelectedIndex == 0 && ddlStatus.SelectedIndex == 0 && ddlDueDate.SelectedIndex == 0 && ddlOwner.SelectedIndex == 0 && ddlClient.SelectedIndex == 0)
{
query = strMainQuery + " WHERE CT.ACTIVESTATUS = 0";
}
else
{
query = strMainQuery + " WHERE CT.ACTIVESTATUS = 0" + strClause;
}
using (SqlConnection conn = new SqlConnection(connString))
{
try
{
SqlCommand cmd = new SqlCommand(query, conn);
// create data adapter
SqlDataAdapter da = new SqlDataAdapter(query, conn);
// this will query your database and return the result to your datatable
DataSet myDataSet = new DataSet();
da.Fill(myDataSet);
DataView myDataView = new DataView();
myDataView = myDataSet.Tables[0].DefaultView;
if (sortExp != string.Empty)
{
//MessageBox.Show(sortExp);
//MessageBox.Show(sortDir);
myDataView.Sort = string.Format("{0} {1}", sortExp, sortDir);
}
yourTasksGV.DataSource = myDataView;
yourTasksGV.DataBind();
TasksUpdatePanel.Update();
conn.Close();
}
catch (Exception ex)
{
string error = ex.Message;
}
}
}
protected void ddlTaskName_onSelectIndexChanged(object sender, EventArgs e)
{
PullData(ViewState["sortExp"].ToString(), ViewState["sortOrder"].ToString());
}
protected void ddlOwner_onSelectIndexChanged(object sender, EventArgs e)
{
PullData(ViewState["sortExp"].ToString(), ViewState["sortOrder"].ToString());
}
protected void ddlService_onSelectIndexChanged(object sender, EventArgs e)
{
PullData(ViewState["sortExp"].ToString(), ViewState["sortOrder"].ToString());
}
protected void ddlStatus_onSelectIndexChanged(object sender, EventArgs e)
{
PullData(ViewState["sortExp"].ToString(), ViewState["sortOrder"].ToString());
}
protected void ddlDueDate_onSelectIndexChanged(object sender, EventArgs e)
{
PullData(ViewState["sortExp"].ToString(), ViewState["sortOrder"].ToString());
}
protected void ddlClient_onSelectIndexChanged(object sender, EventArgs e)
{
PullData(ViewState["sortExp"].ToString(), ViewState["sortOrder"].ToString());
}
public string sortOrder
{
get
{
if (ViewState["sortOrder"].ToString() == "Desc")
{
ViewState["sortOrder"] = "Asc";
}
else
{
ViewState["sortOrder"] = "Desc";
}
return ViewState["sortOrder"].ToString();
}
set
{
ViewState["sortOrder"] = value;
}
}
现在发生的事情是，当页面加载时，GridView被填充，DropDownList也被填充。当我从一个DropDownList中过滤GridView时，我如何修改我的ASP.net页面或后面的代码，以便DropDownList的其余部分基于过滤的GridView进行更改?
现在DropDownList是硬编码的，但我希望它是动态的，所以DropDownList显示来自更新的GridView的数据。
更新:我将PullData函数更新为:
public void PullData(string sortExp, string sortDir)
{
string query = "";
DataTable taskData = new DataTable();
connString = @""; //connection string
if (ddlTaskName.SelectedIndex > 0)
{
strClause += " AND CT.ATTR2739 = '" + ddlTaskName.SelectedItem.Text + "'";
}
else
{
strClause += " AND CT.ATTR2739 LIKE '%'";
}
if (ddlService.SelectedIndex > 0)
{
strClause += " AND CT.ATTR2846 = '" + ddlService.SelectedItem.Text + "'";
}
else
{
strClause += " AND CT.ATTR2846 LIKE '%'";
}
if (ddlStatus.SelectedIndex > 0)
{
strClause += " AND CT.ATTR2812 = '" + ddlStatus.SelectedItem.Text + "'";
}
else
{
strClause += " AND CT.ATTR2812 LIKE '%'";
}
if (ddlDueDate.SelectedIndex > 0)
{
strClause += " AND CONVERT(VARCHAR(14), CT.ATTR2752, 110) = '" + ddlDueDate.SelectedItem.Text + "'";
}
else
{
strClause += " AND CONVERT(VARCHAR(14), CT.ATTR2752, 110) LIKE '%'";
}
if (ddlOwner.SelectedIndex > 0)
{
strClause += " AND UA.REALNAME = '" + ddlOwner.SelectedItem.Text + "'";
}
else
{
strClause += " AND UA.REALNAME LIKE '%'";
}
if (ddlClient.SelectedIndex > 0)
{
strClause += " AND C.ATTR2815 = '" + ddlClient.SelectedItem.Text + "'";
}
else
{
strClause += " AND C.ATTR2815 LIKE '%'";
}
if (ddlSite.SelectedIndex > 0)
{
strClause += " AND SI.ATTR2819 = '" + ddlSite.SelectedItem.Text + "'";
}
else
{
strClause += " AND SI.ATTR2819 LIKE '%'";
}
if (ddlPractice.SelectedIndex > 0)
{
strClause += " AND PR.ATTR2817 = '" + ddlPractice.SelectedItem.Text + "'";
}
else
{
strClause += " AND PR.ATTR2817 LIKE '%'";
}
if (ddlProvider.SelectedIndex > 0)
{
strClause += " AND P.ATTR2919 = '" + ddlProvider.SelectedItem.Text + "'";
}
else
{
strClause += " AND P.ATTR2919 LIKE '%'";
}
if (ddlTaskName.SelectedIndex == 0 && ddlService.SelectedIndex == 0 && ddlStatus.SelectedIndex == 0 && ddlDueDate.SelectedIndex == 0 && ddlOwner.SelectedIndex == 0 && ddlClient.SelectedIndex == 0 && ddlSite.SelectedIndex == 0 && ddlPractice.SelectedIndex == 0 && ddlProvider.SelectedIndex == 0)
{
query = strMainQuery + " WHERE CT.ACTIVESTATUS = 0";
}
else
{
query = strMainQuery + " WHERE CT.ACTIVESTATUS = 0" + strClause;
}
using (SqlConnection conn = new SqlConnection(connString))
{
try
{
SqlCommand cmd = new SqlCommand(query, conn);
// create data adapter
SqlDataAdapter da = new SqlDataAdapter(query, conn);
// this will query your database and return the result to your datatable
DataSet myDataSet = new DataSet();
da.Fill(myDataSet);
myDataView = new DataView();
myDataView = myDataSet.Tables[0].DefaultView;
if (sortExp != string.Empty)
{
//MessageBox.Show(sortExp);
//MessageBox.Show(sortDir);
myDataView.Sort = string.Format("{0} {1}", sortExp, sortDir);
}
yourTasksGV.DataSource = myDataView;
yourTasksGV.DataBind();
DataTable dt = myDataView.ToTable(true, "Task Name");
var rows = (from DataRow dRow in dt.Rows select new { Name = dRow["Task Name"], ID = dRow["Task Detail"] }).Distinct();
ddlTaskName.DataSource = rows;
ddlTaskName.DataTextField = "Task Name";
ddlTaskName.DataValueField = "Task Detail";
ddlTaskName.DataBind();
TasksUpdatePanel.Update();
conn.Close();
}
catch (Exception ex)
{
string error = ex.Message;
}
}
}
显示错误:
{System.InvalidOperationException: Both DataSource and DataSourceID are defined on 'ddlTaskName'. Remove one definition.
at System.Web.UI.WebControls.DataBoundControl.EnsureSingleDataSource()
at System.Web.UI.WebControls.DataBoundControl.ConnectToDataSourceView()
at System.Web.UI.WebControls.DataBoundControl.GetData()
at System.Web.UI.WebControls.ListControl.OnDataBinding(EventArgs e)
at System.Web.UI.WebControls.ListControl.PerformSelect()
at System.Web.UI.WebControls.BaseDataBoundControl.DataBind()
at OB.Pages.YT.PullData(String sortExp, String sortDir) in Tasks.aspx.cs:line 210}
从你的问题我所理解的是你想要关联每个下拉与gridview的各自列。如果我是对的，你可以这样做
ddlTaskName.DataSource = myDataView;
ddlTaskName.DataTextField = "Name";//Name of column which you want to display as text
ddlTaskName.DataValueField = "ID";//Name of column which you want to be Value of dropdown list items.
ddlTaskName.DataBind();
您必须为您的PullData方法中的每个下拉菜单都这样做。
更新1如果你的DataValueFiled和datatextfield是相同的，你可以尝试以下两种方法
DataTable dt = myDataView.ToTable(true, "Name");
ddlTaskName.DataSource = dt;
ddlTaskName.DataTextField = "Name";
ddlTaskName.DataValueField = "Name";
ddlTaskName.DataBind();
这将只选择一列，即Name column。如果你有不同的DataValueField和datatextfield，你可以试试这个
var rows = (from DataRow dRow in dt.Rows select new { Name = dRow["Name"], ID = dRow["ID"] }).Distinct();
ddlTaskName.DataSource = rows;
ddlTaskName.DataTextField = "Name";
ddlTaskName.DataValueField = "ID";
ddlTaskName.DataBind();
=========================== 更新2 ===============
Ok，给你
现在你的第一个下拉菜单看起来像这样
<asp:DropDownList ID="ddlTaskName" CssClass="chosen-select" AutoPostBack="true" runat="server" Width="100%" Font-Size="11px" AppendDataBoundItems="true" OnSelectedIndexChanged="ddlTaskName_onSelectIndexChanged"></asp:DropDownList>
现在在页面加载时你可以用你想要的数据填充它像这样
ddlTaskName.DataSource = "your dataset ot datatable or dataview name";
ddlTaskName.DataTextField = "Name";
ddlTaskName.DataValueField = "ID";
ddlTaskName.DataBind();
在Pull data方法中你会这样做
var rows = (from DataRow dRow in dt.Rows select new { Name = dRow["Name"], ID = dRow["ID"] }).Distinct();
ddlTaskName.DataSource = rows;
ddlTaskName.DataTextField = "Name";
ddlTaskName.DataValueField = "ID";
ddlTaskName.DataBind();
这里的dt是DataTable对象，如果你正在填充一个数据集，你可以像这样使用myDataSet。表[0]代替dt.
要添加一个选项"All"，你可以这样做
ddlTest.Items.Insert(0, new ListItem("All"));
这将在顶部插入"All"，你可以改变它的位置