<asp:Table ID="Table1" runat="server" CssClass="controlTable" >
<asp:TableRow ID="ControlRow">
<asp:TableCell>
<asp:DropDownList ID="Location" runat="server" AutoPostBack="true" Width="150px" OnSelectedIndexChanged="Location_SelectedIndexChanged" CssClass="chosen-select"></asp:DropDownList>
</asp:TableCell>
<asp:TableCell>
<asp:DropDownList ID="Specialty" runat="server" AutoPostBack="true" Width="150px" OnSelectedIndexChanged="Specialty_SelectedIndexChanged" CssClass="chosen-select"></asp:DropDownList>
</asp:TableCell>
</asp:TableRow>
</asp:Table>
The two dropdowns are populated by SQL table. The following code shows how the dropdowns are filtered left-to-right and right-to-left (which isn't working):
protected void Location_SelectedIndexChanged(object sender, EventArgs e)
{
string LocationDDLValue = Location.SelectedItem.Value;
string query = string.Empty;
foreach (TableCell cellCtrl in ControlRow.Controls)
{
foreach (var ctrl in cellCtrl.Controls)
{
if (ctrl is DropDownList)
{
DropDownList ctrlDDL = (DropDownList)ctrl;
if (ctrlDDL.SelectedIndex > 0 && ctrlDDL.ID.ToString() != "Topic")
{
if (query == string.Empty)
{
query = query + string.Format("{0} = '{1}'", ctrlDDL.ID.ToString(), ctrlDDL.SelectedItem.Value);
}
else
{
query = query + string.Format(" AND {0} = '{1}'", ctrlDDL.ID.ToString(), ctrlDDL.SelectedItem.Value);
}
}
}
}
}
DataTable cacheTable = HttpContext.Current.Cache["cachedtable"] as DataTable;
DataTable filteredData = cacheTable.Select(query).CopyToDataTable<DataRow>();
if (Specialty.SelectedIndex == 0)
{
filteredData.DefaultView.Sort = "Specialty ASC";
Specialty.DataSource = filteredData.DefaultView.ToTable(true, "Specialty");
Specialty.DataTextField = "Specialty";
Specialty.DataValueField = "Specialty";
Specialty.DataBind();
Specialty.Items.Insert(0, new ListItem("All Specialties", "All Specialties"));
}
}
protected void Specialty_SelectedIndexChanged(object sender, EventArgs e)
{
string query = string.Empty;
foreach (TableCell cellCtrl in ControlRow.Controls)
{
foreach (var ctrl in cellCtrl.Controls)
{
if (ctrl is DropDownList)
{
DropDownList ctrlDDL = (DropDownList)ctrl;
if (ctrlDDL.SelectedIndex > 0 && ctrlDDL.ID.ToString() != "Topic")
{
if (query == string.Empty)
{
query = query + string.Format("{0} = '{1}'", ctrlDDL.ID.ToString(), ctrlDDL.SelectedItem.Value);
}
else
{
query = query + string.Format(" AND {0} = '{1}'", ctrlDDL.ID.ToString(), ctrlDDL.SelectedItem.Value);
}
}
}
}
}
try
{
DataTable cacheTable = HttpContext.Current.Cache["cachedtable"] as DataTable;
DataTable filteredData = cacheTable.Select(query).CopyToDataTable<DataRow>();
if (Location.SelectedIndex == 0)
{
filteredData.DefaultView.Sort = "Location ASC";
Location.DataSource = filteredData.DefaultView.ToTable(true, "Location");
Location.DataTextField = "Location";
Location.DataValueField = "Location";
Location.DataBind();
Location.Items.Insert(0, new ListItem("All Locations", "All Locations"));
}
}
catch (Exception ex)
{
string error = ex.Message;
}
}
Sample SQL table that is used to populate and then used to filter:
All Locations All Specialties
210 internal medicine
210 obgyn
90 obgyn
90 maternal medicine
65 maternal medicine
65 urgent care
When the page loads the `location` and the `specialty` dropdowns are populated with distinct value from their respective column in the table above.
If `210` is selected from the `location` dropdown, the `specialty` dropdown has the following entries:
All Specialties (selected)
internal medicine
obgyn
If `90` is selected from the `location` dropdown, the `specialty` dropdown has the following entries:
All Specialties (selected)
obgyn
maternal medicine
While `90` is selected from the `location` dropdown, the `specialty` dropdown can be changed to any of the selection and it is fine. But if I change the `specialty` dropdown to `obgyn` the `location` dropdown isn't re-filtered to only display which has `obgyn` as the specialty.
The reason why I get the following error, if `65` is selected from the `location` dropdown, if `obgyn` is still selected from `specialty` dropdown:
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.InvalidOperationException: The source contains no DataRows.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[InvalidOperationException: The source contains no DataRows.]
System.Data.DataTableExtensions.LoadTableFromEnumerable(IEnumerable`1 source, DataTable table, Nullable`1 options, FillErrorEventHandler errorHandler) +280
System.Data.DataTableExtensions.CopyToDataTable(IEnumerable`1 source) +97
BC.VisualWebPart1.VisualWebPart1.Location_SelectedIndexChanged(Object sender, EventArgs e) +1821
System.Web.UI.WebControls.ListControl.OnSelectedIndexChanged(EventArgs e) +130
System.Web.UI.Page.RaiseChangedEvents() +105
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +3572
It is because, `obgyn` is not a specialty for location `65`.
How can I modify my code so:
If `210` is selected from the `location` dropdown, the `specialty` dropdown has the following entries:
All Specialties (selected)
internal medicine
obgyn
If `obgyn` is selected in `specialty` dropdown from the choices above, the `location` dropdown then filters to show the following entries:
All Locations (selected)
210
90
As you can see `65` has been removed from `location` dropdown, because it doesn't have `obgyn` as a specialty.
Full C# code is here: https://dotnetfiddle.net/oC4jmB
I would like to know how to do the "else" for the drop down if the selected index is not 0.