Cascading DropDownLists

Question

For my application, i need to incorporate some dropdownlists in order to display some table in function of the differents values selected in these lists. But when I select a value in one list, an other fills in function of the value and I don't know how to do that.

In my application, I only use Raw queries like that :

        string requeteAppli ="select distinct CD_APPLI, ORDRE_APPLI from dbo.REF_APPLI where CD_APPLI != 'PNB' order by ORDRE_APPLI";

With a function for execute them like that :

public List<DataRow> executerRequete(string query)
    {
        //Initialisation d'une variable Liste contenant des lignes de données où seront récupérés les résultats de la requête suivante.
        List<DataRow> liste = null;

        //Création d'une variable de connection où sont stockés toutes les informations nécessaires à la connexion à la base de données.
        //Ces informations sont stockées dans le fichier de config Web.config.
        string connString = ConfigurationManager.AppSettings["REF_ConnectionString"];

        //Création d'un objet instance de SqlConnection (classe permettant la connexion ouverte à une base de données) où est stocké le contenu de la variable connString.
        using (SqlConnection conn = new SqlConnection(connString))
        {
            //Création d'un objet de commande permettant de spécifier comment la commande sera inteprétée, ici en commande de texte SQL avec CommandType.Text.
            using (SqlCommand objCommand = new SqlCommand(query, conn))
            {
                //Création d'un objet de commande permettant de spécifier comment la commande sera inteprétée, ici en commande de texte SQL avec CommandType.Text.
                objCommand.CommandType = CommandType.Text;
                //Création d'un objet instance de DataTable qui va récupérer la résultat de la requête.
                DataTable dt = new DataTable();
                //Création d'un objet instance de SqlDataAdapter qui va effectuer le lien avec SQL Server afin de récupérer les données.
                SqlDataAdapter adp = new SqlDataAdapter(objCommand);
                //Ouverture de la connexion.
                conn.Open();
                //L'instruction FILL récupère les données de la source de données et les insère dans dt.
                adp.Fill(dt);
                //Vérification du contenu de dt.
                if (dt != null)
                {
                    //Remplissage de la liste.
                    liste = dt.AsEnumerable().ToList();
                }
            }
        }

        //Le résultat est retournée à l'action.
        return liste;
    }

The problem is with these queries, I've seen a lot of tutorials of how implement cascading dropdownlists with ASP.NET MVC and jQuery but it wasn't with this style of queries and I'm totally confused with them and I can't change them.

I get the result of these queries in my actions like that :

var queries = new query();
var items = rq.executerRequete(requeteIndex);
queries.Query2 = (from i in items2
                  select new Suivi { CD_APPLI = i.Field<String>("CD_APPLI") }).ToList();

My goal is to have a first list of Applications and when the user select one value an other list containing some date (depend of the application selected) fills in function. Here is the query which get the dates in function of the selected application :

var itemsDate = rq.executerRequete(requetePERIODE);
var periode = (from i in itemsDate
               where i.Field<String>("CD_APPLI").Trim() == appli.Trim()
               select new Suivi { PERIODE = i.Field<Int64>("PERIODE") });

I'm totally lost with these cascading dropdownlists and I really need your help :/ If you need code, i can give you this but even if i've tried some solutions for these lists, I can't give you an example of my code (javascript) because it doesn't work at all and i do anything, my code is simply a lot of bullshit...

Answer 1

serverside you fill the application dropdown with application id's and something to display

clientside you'll want to attach a function on the change event of your first dropdown list to fetch the data and fill the dates dropdown like

    $(document).ready(function(){
     $('#CboApplications').change(function() {
      function getDates() {
      $.ajax({
          type: "POST",
          url: "Reg_Form.aspx/GetDates",
          data: "{'applicationId':" + ( $('#CboApplications').val()) + "}",
          contentType: "application/json; charset=utf-8",
          global: false,
          async: false,
          dataType: "json",
          success: function(jsonObj) {
             for (var i = 0; i < jsonObj.Table.length; i++){
               listItems+= "<option value='" + jsonObj.Table[i].PERIODE+ "'>" + jsonObj.Table[i].PERIODE+ "</option>";
              }
             $("#cboDates").html(listItems);
        }
    });
    return false;
}
    });

to get the data you will need a web method or a web service with a web method that executes your query code

<WebMethod()> _
Public Sub GetDates(ByVal applicationId As String)
    //use correct qry based on applicationId 
    var itemsDate = rq.executerRequete(requetePERIODE);
    var periode = (from i in itemsDate
           where i.Field<String>("CD_APPLI").Trim() == appli.Trim()
           select new Suivi { PERIODE = i.Field<Int64>("PERIODE") });
    Return periode 
End Sub

this code wil not be 100% correct as i cant test it myself atm but the concept might help you

example webmethod in a page (important are static, WebMethod and ScriptMethod for ajax calls)

public partial class Products : System.Web.UI.Page 

   { 
    [System.Web.Services.WebMethod()] 
    [System.Web.Script.Services.ScriptMethod()] 
    public static List<Product> GetProducts(int cateogryID) 
    {
      // Put your logic here to get the Product list 
    }

Answer 2

Yeah !!! It works !!

The problem was with the query which get the result of the second list, I had to do that :

var periode = (from i in itemsDate
                       where i.Field<String>("CD_APPLI").Trim() == appli.Trim()
                       select new { value = i.Field<Int64>("PERIODE").ToString(), text = i.Field<Int64>("PERIODE").ToString() });

And not that :

            var periode = (from i in itemsDate
                       where i.Field<String>("CD_APPLI").Trim() == appli.Trim()
                       select new SelectListItem { Value = i.Field<Int64>("PERIODE").ToString(), Text = i.Field<Int64>("PERIODE").ToString() });

The rest of my code doesn't change relative to the second answer.

Now I'll try with a third list and if I have others problems, I'll get back here.

Really thanks for your help, ufosnowcat :D

Answer 3

as answer to the last comment

adding a please select would be 1 way to go; in your return function for the data of your 3d list insert on position 0 a value 'please select' forcing the user to trigger the action

an other option would be: 1) put the logic you now call in the .change function in a seperate function that way it is easier to call from several places

then where you bind the change function after you fill your current(3d) list check if there is only 1 item in the list if so call the function to fill the 4th list you could integrate this in the filling of each list: after the

$.each(months, function (index, periode) {... }));

put something like (check what you can do with period)

if (periode.count() == 1) {fillnextList();};