Multi-Filter wiht Ajax & onChange

Question

User-1238176707 posted

We have and ASP.NET app using AJAX/javascript to update a form which contains three dropdown lists. Each of the lists are fed from different columns from a table. We are trying to have the content filtered whereby when I select 1 value in column A, columns B & C will automatically be filtering accordingly. Similarly, when selecting B, column C will be further filtered.

We were looking to create an "excel-like" filtering method, whereby a user can select any column (A/B/C) in any order, and the values will be automatically updated in the opposing two columns. Just like in Excel you can filter from within any column and all of the other columns are updated.

The problem we have is that since we are using the onChange javascript function attached to each dropdown, if we update the list values each time another column's value is selected, it will create a loop of changes ping-ponging from 1 list to another, to another, to another . . . ., since each time the values will change and it will create an infinite loop. Is there a way alternatively to only call an "onChange" function when a user selects a value in the list, and NOT when the list values are updated? Or possibly another idea?

Thanks,

Reuvy

Answer 1

User-1838255255 posted

Hi Reuvy,

According to your description, as far as I know, this problem that you meet is related to your database. You could refer to the following code:

Database:

Aspx:

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
    <script type="text/javascript">
        $(document).ready(function () {
            $("#ddl1").change(function () {
                var ddl1 = $('#ddl1 option:selected').val();
                debugger;
                $.ajax({
                    type: "POST",
                    url: "ajaxfilter.aspx/GetCustomers",
                    data: '{class1:"' + ddl1 + '"}',
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: function (r) {
                        var value = String(r.d);
                        var arr = new Array();
                        arr = value.split(";");
                        var arrt = new Array();
                        arrt = arr[0].split(",");
                        var arrs = new Array();
                        arrs = arr[1].split(",");
                        $("#ddl2").empty();
                        for (var i = 0; i < arrt.length-1; i++) {
                            $("#ddl2").append("<option>" + arrt[i] + "</option>");
                        };
                        $("#ddl3").empty();
                        for (var j = 0; j < arrs.length-1; j++) {
                            $("#ddl3").append("<option>" + arrs[j] + "</option");
                        }
                    }
                });
            });
            $("#ddl2").change(function () {
                var ddl2 = $('#ddl2 option:selected').val();
                debugger;
                $.ajax({
                    type: "POST",
                    url: "ajaxfilter.aspx/GetCustomers2",
                    data: '{tea1:"' + ddl2 + '"}',
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: function (r) {
                        var value = String(r.d);
                        var arr = new Array();
                        arr = value.split(";");
                        var arrc = new Array();
                        arrc = arr[0].split(",");
                        var arrs = new Array();
                        arrs = arr[1].split(",");
                        $("#ddl1").empty();
                        for (var i = 0; i < arrc.length - 1; i++) {
                            $("#ddl1").append("<option>" + arrc[i] + "</option>");
                        };
                        $("#ddl3").empty();
                        for (var j = 0; j < arrs.length - 1; j++) {
                            $("#ddl3").append("<option>" + arrs[j] + "</option");
                        }
                    }
                });
            });
            $("#ddl3").change(function () {
                var ddl3 = $('#ddl3 option:selected').val();
                debugger;
                $.ajax({
                    type: "POST",
                    url: "ajaxfilter.aspx/GetCustomers3",
                    data: '{stu1:"' + ddl3 + '"}',
                    contentType: "application/json; charset=utf-8",
                    dataType: "json",
                    success: function (r) {
                        var value = String(r.d);
                        var arr = new Array();
                        arr = value.split(";");
                        var arrc = new Array();
                        arrc = arr[0].split(",");
                        var arrt = new Array();
                        arrt = arr[1].split(",");
                        $("#ddl1").empty();
                        for (var i = 0; i < arrc.length - 1; i++) {
                            $("#ddl1").append("<option>" + arrc[i] + "</option>");
                        };
                        $("#ddl2").empty();
                        for (var j = 0; j < arrt.length - 1; j++) {
                            $("#ddl2").append("<option>" + arrt[j] + "</option");
                        }
                    }
                });
            });
        });
    </script>

<div>
            <asp:DropDownList ID="ddl1" runat="server" DataSourceID="SqlDataSource1" DataTextField="class" DataValueField="class" AppendDataBoundItems="true">
                <asp:ListItem Text="select" Value="select"></asp:ListItem>
           </asp:DropDownList>
            <asp:DropDownList ID="ddl2" runat="server" DataSourceID="SqlDataSource2" DataTextField="teacher" DataValueField="teacher" AppendDataBoundItems="true">
                <asp:ListItem Text="select" Value="select"></asp:ListItem>
            </asp:DropDownList>
            <asp:DropDownList ID="ddl3" runat="server" DataSourceID="SqlDataSource3" DataTextField="student" DataValueField="student" AppendDataBoundItems="true">
                <asp:ListItem Text="select" Value="select"></asp:ListItem>
            </asp:DropDownList>
            <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT DISTINCT class FROM [ddlTable1]"></asp:SqlDataSource>
            <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT DISTINCT teacher FROM [ddlTable1]"></asp:SqlDataSource>
            <asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT student FROM [ddlTable1]"></asp:SqlDataSource>
        </div>
    </form>

Aspx.cs:

[System.Web.Services.WebMethod]
        //drop1
        public static string GetCustomers(string class1)
        {
            string a = class1.Trim();
            string tea = "";
            string stu = "";
            string teastu = "";
            string query = "SELECT teacher,student FROM ddlTable1 where class=@Class";
            string constr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand(query))
                {

                    cmd.Parameters.AddWithValue("@Class", a);
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = con;
                    con.Open();
                    using (SqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            tea += sdr["teacher"].ToString()+",";
                            stu += sdr["student"].ToString() + ",";
                        }
                    }
                    con.Close();
                    teastu = tea + ";" + stu;
                    return teastu;
                }
            }
        }

        [System.Web.Services.WebMethod]
        //drop2
        public static string GetCustomers2(string tea1)
        {
            string a = tea1.Trim();
            string cla = "";
            string stu = "";
            string clastu = "";
            string query = "SELECT distinct class,student FROM ddlTable1 where teacher=@Tea";
            string constr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand(query))
                {

                    cmd.Parameters.AddWithValue("@Tea", a);
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = con;
                    con.Open();
                    using (SqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            cla += sdr["class"].ToString() + ",";
                            stu += sdr["student"].ToString() + ",";
                        }
                    }
                    con.Close();
                    clastu = cla + ";" + stu;
                    return clastu;
                }
            }
        }

        [System.Web.Services.WebMethod]
        //drop3
        public static string GetCustomers3(string stu1)
        {
            string a = stu1.Trim();
            string cla = "";
            string tea = "";
            string clatea = "";
            string query = "SELECT class,teacher FROM ddlTable1 where student=@Stu";
            string constr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
            using (SqlConnection con = new SqlConnection(constr))
            {
                using (SqlCommand cmd = new SqlCommand(query))
                {

                    cmd.Parameters.AddWithValue("@Stu", a);
                    cmd.CommandType = CommandType.Text;
                    cmd.Connection = con;
                    con.Open();
                    using (SqlDataReader sdr = cmd.ExecuteReader())
                    {
                        while (sdr.Read())
                        {
                            cla += sdr["class"].ToString() + ",";
                            tea += sdr["teacher"].ToString() + ",";
                        }
                    }
                    con.Close();
                    clatea = cla + ";" + tea;
                    return clatea;
                }
            }
        }

Output:

Best Regards,

Eric Du