How can you pass the results of many cascading dropdowns to "filter" another list? RSS

• • Reply
  

  Liquidmetal

  Member

  23 Points

  172 Posts

  Re: How can you pass the results of many cascading dropdowns to "filter" another list?

  Feb 26, 2013 01:01 PM|LINK

  Sorry, my db query was the source of the error and having corrected this, I do get the default page with the drop downs and when I submit the 4 values I get the Result page firing and opening up but I think the results are not being passed through. I will continue testing.

• • Reply
  

  Liquidmetal

  Member

  23 Points

  172 Posts

  Re: How can you pass the results of many cascading dropdowns to "filter" another list?

  Feb 26, 2013 01:06 PM|LINK

  after having corrected the qryFilteredLens to include BrandID, StyleID and DesignID, I am not getting any errors from the server. However, as I suspected the selected values are not being filtered to the Result page as I was hoping.

• • Reply
  

  GmGregori

  Contributor

  5446 Points

  735 Posts

  Re: How can you pass the results of many cascading dropdowns to "filter" another list?

  Feb 26, 2013 01:10 PM|LINK

  Look at the url to the result.cshtml page: are the values assigned to the querystrings right?

• • Reply
  

  Liquidmetal

  Member

  23 Points

  172 Posts

  Re: How can you pass the results of many cascading dropdowns to "filter" another list?

  Feb 26, 2013 01:36 PM|LINK

  Hi

  It is working and then it is not working. here is one url:

  http://localhost:24021/Result.cshtml?catid=1&brandid=2&styleid=2&designid=3

  in this example the list does not match the values I had selected - the results are totally different.

   

  However, for some results it is fine, like this one:

  http://localhost:24021/Result.cshtml?catid=5&brandid=3&styleid=4&designid=38

   

   I suspect that this is due to 'like' which is giving fuzzy results. What do you think?

• • Reply
  

  GmGregori

  Contributor

  5446 Points

  735 Posts

  Re: How can you pass the results of many cascading dropdowns to "filter" another list?

  Feb 26, 2013 01:51 PM|LINK

  Try replacing

  if(!designid.IsEmpty() ) {
          selectCommand = "SELECT * FROM dbo.qryFilteredLens WHERE DesignID LIKE @0";
          searchTerm = "%" + designid + "%";

  with

      if(!designid.IsEmpty() ) {  
          selectCommand = @"SELECT * FROM dbo.qryFilteredLens WHERE CatID = @0 AND BrandID LIKE @1
                  AND StyleID LIKE @2 AND DesignID LIKE @3"; 
  

  and

  var selectedData = db.Query(selectCommand, searchTerm); 

  with

  var selectedData = db.Query(selectCommand, catid, "%" + brandid + "%", "%" + styleid + "%", "%" + designid + "%"); 

  If it works, there are some other edits to accomplish
  
  
  
  

• • Reply
  

  Liquidmetal

  Member

  23 Points

  172 Posts

  Re: How can you pass the results of many cascading dropdowns to "filter" another list?

  Feb 26, 2013 01:55 PM|LINK

  In this example:

  http://localhost:24021/Result.cshtml?catid=7&brandid=4&styleid=4&designid=

  it has ignored my catid and brandid values and has given me all lens that are styleid=4

  I have changed the likes to exact values like this:

  @{ 
      Layout = "~/_SiteLayout.cshtml"; 
      Page.Title = "Lens";     
   
      var catid = Request.QueryString["catid"]; 
      var brandid = Request.QueryString["brandid"]; 
      var styleid = Request.QueryString["styleid"];  
      var designid = Request.QueryString["designid"];  
   
      var selectCommand = "SELECT * FROM dbo.qryFilteredLens"; 
      var searchTerm = ""; 
       
      if(!designid.IsEmpty() ) {  
          selectCommand = "SELECT * FROM dbo.qryFilteredLens WHERE DesignID = @0"; 
          searchTerm = designid;  
      } else if(!styleid.IsEmpty() ) {  
          selectCommand = "SELECT * FROM dbo.qryFilteredLens WHERE StyleID = @0"; 
          searchTerm = styleid;  
      } else if(!brandid.IsEmpty() ) {  
          selectCommand = "SELECT * FROM dbo.qryFilteredLens WHERE BrandID = @0"; 
          searchTerm = brandid;  
      } else if(!catid.IsEmpty() ) {  
          selectCommand = "SELECT * FROM dbo.qryFilteredLens WHERE CatID = @0"; 
          searchTerm = catid; 
      } 
   
      var db = Database.Open("A-LensCatFE-01SQL"); 
      var selectedData = db.Query(selectCommand, searchTerm); 
      var grid = new WebGrid(source: selectedData, defaultSort: "CatID", rowsPerPage:25); 
  } 
   
  <div> 
      @grid.GetHtml(tableStyle: "grid",  
      headerStyle: "head",  
      alternatingRowStyle: "alt")  
  </div>

  This has gotten rid of the fuzzy results but I am confused why it would ignore the some of the values?

  Further testing reveals that that if the last selected item has products in other catalogues or brands or styles

  the results will be to display all those lenses regardless of the other selections. 

  • Edited by Liquidmetal on Feb 26, 2013 02:07 PM
• • Reply
  

  GmGregori

  Contributor

  5446 Points

  735 Posts

  Re: How can you pass the results of many cascading dropdowns to "filter" another list?

  Feb 26, 2013 02:36 PM|LINK

  Liquidmetal

  the results will be to display all those lenses regardless of the other selections

  Yes, the other selections aren't executed.

  This is precisely why I have suggested to you a new query in the previous reply. If you don't need the 'like' operator (and I agree with you) you can replace

  if(!designid.IsEmpty() ) {
          selectCommand = "SELECT * FROM dbo.qryFilteredLens WHERE DesignID LIKE @0";
          searchTerm = "%" + designid + "%";

  with

      if(!designid.IsEmpty() ) {   
          selectCommand = @"SELECT * FROM dbo.qryFilteredLens WHERE CatID = @0 AND BrandID = @1 
                  AND StyleID = @2 AND DesignID = @3"; 

  and

  var selectedData = db.Query(selectCommand, searchTerm); 

  with

  var selectedData = db.Query(selectCommand, catid, brandid, styleid, designid); 
   

  • Edited by GmGregori on Feb 26, 2013 02:37 PM
• • Reply
  

  Liquidmetal

  Member

  23 Points

  172 Posts

  Re: How can you pass the results of many cascading dropdowns to "filter" another list?

  Feb 26, 2013 03:01 PM|LINK

  I really appreciate what you have done here for me. It is incredible you are able to visualise my project, when in reality you are blind to my data and examples.

  Let me summarise what I have done. I have followed you examples this is what I have now in Results page:

  @{ 
      Layout = "~/_SiteLayout.cshtml"; 
      Page.Title = "Filtered Lens";     
   
      var catid = Request.QueryString["catid"]; 
      var brandid = Request.QueryString["brandid"]; 
      var styleid = Request.QueryString["styleid"];  
      var designid = Request.QueryString["designid"];  
   
      var selectCommand = "SELECT * FROM dbo.qryFilteredLens"; 
      var searchTerm = ""; 
       
       if(!designid.IsEmpty() ) {    
          selectCommand = @"SELECT * FROM dbo.qryFilteredLens WHERE CatID = @0 AND BrandID = @1 AND StyleID = @2 AND DesignID = @3"; 
      } else if(!styleid.IsEmpty() ) {  
          selectCommand = "SELECT * FROM dbo.qryFilteredLens WHERE StyleID = @0"; 
          searchTerm = styleid;  
      } else if(!brandid.IsEmpty() ) {  
          selectCommand = "SELECT * FROM dbo.qryFilteredLens WHERE BrandID = @0"; 
          searchTerm = brandid;  
      } else if(!catid.IsEmpty() ) {  
          selectCommand = "SELECT * FROM dbo.qryFilteredLens WHERE CatID = @0"; 
          searchTerm = catid; 
      } 
   
      var db = Database.Open("A-LensCatFE-01SQL"); 
      var selectedData = db.Query(selectCommand, catid, brandid, styleid, designid);  
      var grid = new WebGrid(source: selectedData, defaultSort: "CatID", rowsPerPage:25); 
  } 
   
  <div> 
      @grid.GetHtml(tableStyle: "grid",  
      headerStyle: "head",  
      alternatingRowStyle: "alt")  
  </div>

  I hope I have followed your suggestions properly. I will now test and report back. Thanks.

   

  • Marked as answer by Liquidmetal on Feb 28, 2013 12:20 PM
• • Reply
  

  Liquidmetal

  Member

  23 Points

  172 Posts

  Re: How can you pass the results of many cascading dropdowns to "filter" another list?

  Feb 26, 2013 03:13 PM|LINK

  I have been testing this with your new code and it is much more stable and definite in the results. I have one problem that I have been trying to resolve and that is the last drop down is dependant on 2 values not 1, ie BrandID and StyleID together. Currently the cascade is limited by StyleID alone and this is generating values in the list that should not be in the list. So when you select an item in the last drop down that do not belong to the total combination the result now is null. Which is good. However, would like to limit my values to only those that are needed. I trust you can help me there too. Once again, thanks alot.

  edit:

  Since my post above, the results have gone bonkers and seem to be pulling data for different catid, brandid etc. I may select catid=3 and brandid=2 and I get results for other catalogues or brands. Sorry to report this, I think I may not have followed your samples properly; this is my current code:

  Results.cshtml:

  @{ 
      Layout = "~/_SiteLayout.cshtml"; 
      Page.Title = "Filtered Lens";     
   
      var catid = Request.QueryString["catid"]; 
      var brandid = Request.QueryString["brandid"]; 
      var styleid = Request.QueryString["styleid"];  
      var designid = Request.QueryString["designid"];  
   
      var selectCommand = "SELECT * FROM dbo.qryFilteredLens"; 
      var searchTerm = ""; 
       
       if(!designid.IsEmpty() ) {    
          selectCommand = @"SELECT * FROM dbo.qryFilteredLens WHERE CatID = @0 AND BrandID = @1 AND StyleID = @2 AND DesignID = @3"; 
      } else if(!styleid.IsEmpty() ) {  
          selectCommand = "SELECT * FROM dbo.qryFilteredLens WHERE StyleID = @0"; 
          searchTerm = styleid;  
      } else if(!brandid.IsEmpty() ) {  
          selectCommand = "SELECT * FROM dbo.qryFilteredLens WHERE BrandID = @0"; 
          searchTerm = brandid;  
      } else if(!catid.IsEmpty() ) {  
          selectCommand = "SELECT * FROM dbo.qryFilteredLens WHERE CatID = @0"; 
          searchTerm = catid; 
      } 
   
      var db = Database.Open("A-LensCatFE-01SQL"); 
      var selectedData = db.Query(selectCommand, catid, brandid, styleid, designid);  
      var grid = new WebGrid(source: selectedData, defaultSort: "CatID", rowsPerPage:25); 
  } 
   
  <div> 
      @grid.GetHtml(tableStyle: "grid",  
      headerStyle: "head",  
      alternatingRowStyle: "alt")  
  </div>

  • Edited by Liquidmetal on Feb 26, 2013 03:37 PM
• • Reply
  

  GmGregori

  Contributor

  5446 Points

  735 Posts

  Re: How can you pass the results of many cascading dropdowns to "filter" another list?

  Feb 26, 2013 04:52 PM|LINK

  Try with this Result.cshtml file

  @{
      Layout = "~/_SiteLayout.cshtml";
      Page.Title = "Filtered Lens";
   
      var catid = Request.QueryString["catid"];
      var brandid = Request.QueryString["brandid"];
      var styleid = Request.QueryString["styleid"];
      var designid = Request.QueryString["designid"];
   
      var selectCommand = "SELECT * FROM dbo.qryFilteredLens";
       
       if(!designid.IsEmpty() ) {
          selectCommand = @"SELECT * FROM dbo.qryFilteredLens WHERE CatID = @0 AND BrandID = @1
                    AND StyleID = @2 AND DesignID = @3";
      } else if(!styleid.IsEmpty() ) {
          selectCommand = @"SELECT * FROM dbo.qryFilteredLens WHERE CatID = @0 AND BrandID = @1
                    AND StyleID = @2";
      } else if(!brandid.IsEmpty() ) {
          selectCommand = "SELECT * FROM dbo.qryFilteredLens WHERE CatID = @0 AND BrandID = @1";
      } else if(!catid.IsEmpty() ) {
          selectCommand = "SELECT * FROM dbo.qryFilteredLens WHERE CatID = @0";
      }
   
      var db = Database.Open("A-LensCatFE-01SQL");
      var selectedData = db.Query(selectCommand, catid, brandid, styleid, designid);
      var grid = new WebGrid(source: selectedData, defaultSort: "CatID", rowsPerPage:25);
  }
  
  <div>
      <p>catid = @catid - brandid = @brandid - styleid = @styleid - designid = @designid</p>
  <div>
      @grid.GetHtml(tableStyle: "grid",
      headerStyle: "head",
      alternatingRowStyle: "alt")
  </div>

  I have arranged all the queries and displayed at the beginning of the page the parameter values used to create the products list.

  You could try to query your db in SSMS with this parameters and look at the result.

  • Edited by GmGregori on Feb 26, 2013 04:54 PM
  • Marked as answer by Liquidmetal on Feb 28, 2013 12:21 PM