locked
Listview filtering RRS feed

  • Question

  • User-1662567569 posted

    I'm trying to filter a listview using any combination of four dropdown lists. I'm calling the code below to bind the the listview onselectedindexchanged of any of the four dropdowns. It does filter the listview but producues some unexpected results when resetting dropdowns. I'm assuming that there's a better way to write the filterexpression rather than using four separate filters?

    using (SqlDataSource varListviewDataSource = new SqlDataSource())
        {
            string custStr = custFiltDD.SelectedValue;
            varListviewDataSource.ID = "varListviewDataSource";
            varListviewDataSource.ConnectionString = ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString;
            varListviewDataSource.SelectCommand = "SELECT retDetails.varID, retDetails.status, retDetails.approvalStatus, job.jobName, customer.custID, customer.custName, retDetails.quoteRef, retDetails.retCat, retDetails.approvalRef, retDetails.fullDesc, retDetails.jobID, retDetails.description, retDetails.value, CONCAT(contact.contactForename, ' ', contact.contactSurname) AS Name, retStatus.retStatusDesc, retCats.retCatDesc FROM retDetails INNER JOIN contact ON retDetails.requestor = contact.contactID INNER JOIN retStatus ON retDetails.status = retStatus.retStatusID INNER JOIN retCats ON retDetails.retCat = retCats.retCatID  INNER JOIN job ON retDetails.jobID = job.jobID INNER JOIN customer ON job.custID = customer.custID";
    
            if (custFiltDD.SelectedValue != "")
            {
                varListviewDataSource.FilterExpression = "custID = '" + custFiltDD.SelectedValue + "'";
            }
            if (jobFiltDD.SelectedValue != "")
            {
                varListviewDataSource.FilterExpression = "jobID = '" + jobFiltDD.SelectedValue + "'";
            }
            if (appFiltDD.SelectedValue != "")
            {
                varListviewDataSource.FilterExpression = "approvalStatus = '" + appFiltDD.SelectedValue + "'";
           }
            if (statFiltDD.SelectedValue != "")
            {
                varListviewDataSource.FilterExpression = "status = '" + statFiltDD.SelectedValue + "'";
            }
            varListview.DataSource = varListviewDataSource;
            varListview.DataBind();
        }
    Wednesday, July 26, 2017 2:17 PM

Answers

  • User-335504541 posted

    Hi wk1980,

    I think you could try to create the FilterExpression by your value of the dropdown lists, then set it to the SqlDataSource.

    For example:

                using (SqlDataSource varListviewDataSource = new SqlDataSource())
                {
                    string custStr = custFiltDD.SelectedValue;
                    varListviewDataSource.ID = "varListviewDataSource";
                    varListviewDataSource.SelectCommand = "SELECT retDetails.varID, retDetails.status, retDetails.approvalStatus, job.jobName, customer.custID, customer.custName, retDetails.quoteRef, retDetails.retCat, retDetails.approvalRef, retDetails.fullDesc, retDetails.jobID, retDetails.description, retDetails.value, CONCAT(contact.contactForename, ' ', contact.contactSurname) AS Name, retStatus.retStatusDesc, retCats.retCatDesc FROM retDetails INNER JOIN contact ON retDetails.requestor = contact.contactID INNER JOIN retStatus ON retDetails.status = retStatus.retStatusID INNER JOIN retCats ON retDetails.retCat = retCats.retCatID  INNER JOIN job ON retDetails.jobID = job.jobID INNER JOIN customer ON job.custID = customer.custID";
                    string Filter = "";        
               
                    if (custFiltDD.SelectedValue != "")
                    {
                        Filter= Filter+ "custID = '" + custFiltDD.SelectedValue + "'and"; 
                    }
                    if (jobFiltDD.SelectedValue != "")
                    {
                        Filter = Filter + " jobID  = '" + jobFiltDD.SelectedValue + "'and"; //Do not miss the " " before the parameter name
                    }
                    if (appFiltDD.SelectedValue != "")
                    {
                        Filter = Filter + " approvalStatus  = '" + appFiltDD.SelectedValue + "'and";
                    }
                    if (statFiltDD.SelectedValue != "")
                    {
                        Filter = Filter + " status  = '" + statFiltDD.SelectedValue + "'and";
                    }
                    if (Filter.Length > 0)
                    {
                        Filter  = Filter.Remove(Filter.Length - 3);
                        varListviewDataSource.FilterExpression = Filter;
                    }
                    varListview.DataSource = varListviewDataSource;
                    varListview.DataBind();
                }

    Best Regards,

    Billy

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 27, 2017 4:37 AM

All replies

  • User-335504541 posted

    Hi wk1980,

    I think you could try to create the FilterExpression by your value of the dropdown lists, then set it to the SqlDataSource.

    For example:

                using (SqlDataSource varListviewDataSource = new SqlDataSource())
                {
                    string custStr = custFiltDD.SelectedValue;
                    varListviewDataSource.ID = "varListviewDataSource";
                    varListviewDataSource.SelectCommand = "SELECT retDetails.varID, retDetails.status, retDetails.approvalStatus, job.jobName, customer.custID, customer.custName, retDetails.quoteRef, retDetails.retCat, retDetails.approvalRef, retDetails.fullDesc, retDetails.jobID, retDetails.description, retDetails.value, CONCAT(contact.contactForename, ' ', contact.contactSurname) AS Name, retStatus.retStatusDesc, retCats.retCatDesc FROM retDetails INNER JOIN contact ON retDetails.requestor = contact.contactID INNER JOIN retStatus ON retDetails.status = retStatus.retStatusID INNER JOIN retCats ON retDetails.retCat = retCats.retCatID  INNER JOIN job ON retDetails.jobID = job.jobID INNER JOIN customer ON job.custID = customer.custID";
                    string Filter = "";        
               
                    if (custFiltDD.SelectedValue != "")
                    {
                        Filter= Filter+ "custID = '" + custFiltDD.SelectedValue + "'and"; 
                    }
                    if (jobFiltDD.SelectedValue != "")
                    {
                        Filter = Filter + " jobID  = '" + jobFiltDD.SelectedValue + "'and"; //Do not miss the " " before the parameter name
                    }
                    if (appFiltDD.SelectedValue != "")
                    {
                        Filter = Filter + " approvalStatus  = '" + appFiltDD.SelectedValue + "'and";
                    }
                    if (statFiltDD.SelectedValue != "")
                    {
                        Filter = Filter + " status  = '" + statFiltDD.SelectedValue + "'and";
                    }
                    if (Filter.Length > 0)
                    {
                        Filter  = Filter.Remove(Filter.Length - 3);
                        varListviewDataSource.FilterExpression = Filter;
                    }
                    varListview.DataSource = varListviewDataSource;
                    varListview.DataBind();
                }

    Best Regards,

    Billy

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, July 27, 2017 4:37 AM
  • User-1662567569 posted

    Thanks very much Billy, works perfectly!

    Friday, July 28, 2017 9:46 AM