locked
Cascading ddl Query Stmt RRS feed

  • Question

  • User-1641868886 posted

    I have a Cascading dropdownlist on my MVC index page. First I bind a list of states with value = "State" (CA, LA, TN, etc). This binds with no problem.

    Second, I have a dropdownlist of "City". It needs to read "City" column and "State" column in a table "Providers". In this table, the "State" column has the same "State" value (CA, LA, TN, etc) as the value chosen in the "StateList" ddl. This is how I plan to get a ddl of cities for the selected state. Here is code, first the Index view and the partial view for the cities ddl:

    @if (ViewBag.StateList != null)
                    {
                        @Html.DropDownListFor(m => m.State, ViewBag.StateList as SelectList, "- - Select State - -", new { @class = "form-control" })
                    } 
    
    @Html.DropDownListFor(m => m.City, new SelectList(""), "- - Select City - -", new { @class = "form-control" })
    
    ...now the partial view for cities list...
    
    <option value="">-- Select City --</option>
    
    @if(ViewBag.CitiesList !=null)
    {
        foreach(var item in ViewBag.CitiesList)
        {
            <option value="@item.Value"></option>
        }
    
    }

    Here then are the controller actions for these:

    Cascading cs = new Cascading();
            public ActionResult Index()
            {
                ViewBag.StateList = new SelectList(cs.GetStatesList(), "State", "StateName");
                return View();
            }
    
            public ActionResult GetCities(string State)
            {
                ViewBag.CitiesList = new SelectList(cs.GetCityList(State), "City");
                return PartialView("DisplayCities");
    
            }

    ...and finally, the Cascading model:

    [Display(Name = "State")]
            public string State { get; set; }
            public string StateName { get; set; }
    
            
            public IEnumerable<Cascading> GetStatesList()
            {
                string query = "SELECT State, StateName FROM States";
                var result = con.Query<Cascading>(query);
                return result;
            }
    
    
            [Display(Name = "City")]
            public string City { get; set; }
    
            public IEnumerable<Cascading> GetCityList(string State)
            {
                string query = "SELECT City FROM Providers WHERE State = GetStatesList.SelectedValue";
                var result = con.Query<Cascading>(query);
                return result;
            }

    My debugger errors out on "GetCityList" at the point in my Query statement "...GetStatesList.SelectedValue". The error states:

    System.Data.SqlClient.SqlException: 'The multi-part identifier "GetStatesList.SelectedValue" could not be bound.' I'm trying to mash together a tutorial example presented in Entity Framework and make it work in my Dapper ORM interface. I know the query statement is wrong, and I have tried all iterations of the "State" ddl, "State" "StateList" and "GetStatesList" to see what will pick up the value for the "GetCityList" query.

    Is this JUST an SQL syntax issue? Any suggestions greatly appreciated.

    Saturday, January 26, 2019 8:38 PM

Answers

  • User-474980206 posted

    you really need to learn the sqlcommand syntax if you are going to use dapper.

    "SELECT City FROM Providers WHERE State = State.SelectedIndex";

    is not valid sql. try:

    public class Cascading
    {
       public string City {get; set;}
    }
    
    public IEnumerable<Cascading> GetCityList(string State)
    {
       return con.Query<Cascading>(
           "SELECT City FROM Providers WHERE State = @state",
           new {state = State}
       );
    }

    there is really no reason to return a partial view. just change action to return JSON:

    public ActionResult GetCities(string State)
    {
        return Json(cs.GetCityList(State)); 
    }

    and the ajax to:

    $.ajax({
      type: "post",
      url: "/Cas/GetCities
      data: {state: StateSymb}, 
      dataType: "json",
      success: function(response){
        $("#City")
          .empty()
          .append(response.map(function(r) {
             return $('<option>').text(r.State)[0];
          }));
      }
    });




     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, January 27, 2019 9:23 PM

All replies

  • User-1641868886 posted

    Trying to simplify my issue...

    I have a bound ddl of States:

    @Html.DropDownListFor(m => m.State, ViewBag.StateList as SelectList, "- - Select State - -", new { @class = "form-control" })

    ...then a ddl for City that is filled from a PartialView:

    @Html.DropDownListFor(m => m.City, new SelectList(" "), "- - Select City - -", new { @class = "form-control" })
    
    ...and here is the PartialView:
    
    @model Groc_Site_Web.Models.Cascading
    <option value="">-- Select City --</option>
    
    @if(ViewBag.CitiesList !=null)
    {
        foreach(var item in ViewBag.CitiesList)
        {
            <option value="@item.Value"> </option>
        }
    
    }

    ...and a controller action to use the selected "State" to get a list of cities (from a table "Providers" which has addresses with "City" and "State" ("CA", "LA", "TN" etc). I am trying to use the string value of "State" to select by state from my "Providers" table:

    public ActionResult GetCities(string State)
            {
                ViewBag.CitiesList = new SelectList(cs.GetCityList(State), "City");
                return PartialView("DisplayCities");
    
            }

    ...now here is my "GetCityList" method from my "Cascading" model class:

    public IEnumerable<Cascading> GetCityList(string State)
            {
                
                string query = "SELECT City FROM Providers WHERE State = State.SelectedIndex";
                var result = con.Query<Cascading>(query);
                return result;
            }

    ...and here is the script on the "Index" page to send the selected "State" (var StateSymb) to the "GetCityList" method.

    <script>
        $(document).ready(function () {
            $("#State").change(function () {
                var StateSymb = $(this).val();
                debugger
                $.ajax({
                    type: "post",
                    url: "/Cas/GetCities?State=" + StateSymb,
                    contentType: "html",
                    success: function (response) {
                        debugger
                        $("#City").empty();
                        $("#City").append(response);
                    }
                })
            })
        })
    
    </script>

    My issue seems to be the way I have written the query string above in the "IEnumerable GetCityList" method. I get error:

     System.Data.SqlClient.SqlException: 'The multi-part identifier "GetStatesList.SelectedValue" could not be bound.'

    I have tried:  State.SelectedIndex, StateList.SelectedIndex, GetStatesList.SelectedIndex as well as all these but with ".SelectedValue."

    If I remove from the string query, "WHERE" clause entirely, then I can [naturally] get a result that gives me ALL cities in the "City" column.

    So, it seems that I am not aware how to get the query string to capture the option I am selecting from the "StateList" ddl. 

    I'd appreciate any suggestions, whether my query string is just wrong, or my controller action. Debugger picks up the chosen "StateSymb" (like "TN") all the way through the script.

    Thanks again!

    Sunday, January 27, 2019 7:33 PM
  • User-474980206 posted

    you really need to learn the sqlcommand syntax if you are going to use dapper.

    "SELECT City FROM Providers WHERE State = State.SelectedIndex";

    is not valid sql. try:

    public class Cascading
    {
       public string City {get; set;}
    }
    
    public IEnumerable<Cascading> GetCityList(string State)
    {
       return con.Query<Cascading>(
           "SELECT City FROM Providers WHERE State = @state",
           new {state = State}
       );
    }

    there is really no reason to return a partial view. just change action to return JSON:

    public ActionResult GetCities(string State)
    {
        return Json(cs.GetCityList(State)); 
    }

    and the ajax to:

    $.ajax({
      type: "post",
      url: "/Cas/GetCities
      data: {state: StateSymb}, 
      dataType: "json",
      success: function(response){
        $("#City")
          .empty()
          .append(response.map(function(r) {
             return $('<option>').text(r.State)[0];
          }));
      }
    });




     

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, January 27, 2019 9:23 PM
  • User-1641868886 posted

    Thanks greatly, Bruce. I was ALSO mashing together two different samples from tutorials, one with Json and the other with the partialView. Don't know why I chose the PartialView route, but I'm glad you walked me through the Json. I need to get better versed on that to simplify.

    So, it worked. And yes, I do need more work/practice on my C#, javascript, ans SqlServer coordination. I should have known that "@State" was the sql syntax, and that in a normal method, ".SelectedIndex" was C# code.

    When my "City" ddl populated, I had many duplicates. So I did realize very quickly I needed to change my sql to "SELECT DISTINCT...".

    Thanks again!

    Monday, January 28, 2019 6:14 PM