title: How to populate dropdownlist based on a DataTable
body: <p>I have the following code which populates a ASP.net label with values retrieved from a SP and a CachedTable:</p>
  
  <pre><code>public void UpdateDropDownList()
  {
      string strQuery = "";
  
      using (SqlConnection scCon = new SqlConnection(connString))
      {
          using (SqlCommand scCmd = new SqlCommand("cation", scCon))
          {
              SqlDataAdapter sda = new SqlDataAdapter();
              DataTable dt = new DataTable();
              scCmd.CommandType = CommandType.StoredProcedure;
  
              scCmd.Parameters.Add("@ation", SqlDbType.VarChar).Value = cation.SelectedItem.Value;
  
              sda.SelectCommand = scCmd;
              sda.Fill(dt);
  
              var distinctValues = dt.AsEnumerable()
                     .Select(row =&gt; row.Field&lt;string&gt;("Specialty"))
                     .Distinct();
              Label1.Text = "All Specialties&lt;br/&gt;";
              Label1.Text += string.Join("&lt;br /&gt;", distinctValues); //Displays all specialties related to the location
              var k = distinctValues.ToArray();
              strQuery = "(";
              for (int y = 0; y &lt; k.Length; y++)
              {
                  if (y == 0)
                  {
                      strQuery += @"[Specialty] = '" + k[y] + "'";
                  }
                  else
                  {
                      strQuery += @" OR [Specialty] = '" + k[y] + "'";
                  }
              }
              strQuery += @") AND ([Location] = '" + Location.SelectedItem.Value + "')";
              DataTable cacheTable2 = HttpContext.Current.Cache["cachedtable"] as DataTable; //first cached table
              DataTable filteredData2 = cacheTable2.Select(strQuery).CopyToDataTable&lt;DataRow&gt;();
  
              var distinctValues2 = filteredData2.AsEnumerable()
                     .Select(row =&gt; row.Field&lt;string&gt;("Name"))
                     .Distinct();
              Label1.Text += "All Providers&lt;br/&gt;";
              Label1.Text += string.Join("&lt;br /&gt;", distinctValues2); //Displays all providers related to the location
  
              strQuery = "([Specialty] = 'All Specialties'";
              for (int y = 0; y &lt; k.Length; y++)
              {
                  strQuery += @" OR [Specialty] = '" + k[y] + "'";
              }
              strQuery += ")";
              DataTable cacheTable3 = HttpContext.Current.Cache["cachedtable2"] as DataTable; //different cached table
              DataTable filteredData3 = cacheTable3.Select(strQuery).CopyToDataTable&lt;DataRow&gt;();
  
              var distinctValues3 = filteredData3.AsEnumerable()
                     .Select(row =&gt; row.Field&lt;string&gt;("Topic"))
                     .Distinct();
              Label1.Text += "All Topics&lt;br/&gt;";
              Label1.Text += string.Join("&lt;br /&gt;", distinctValues3); //Displays all topics related to the specialty of that location
  
              Session.Add("DTTableLocation", dt);
          }
      }
  }
  </code></pre>
  
  <p>I have four dropdownlist:</p>
  
  <pre><code>&lt;asp:DropDownList ID="Location" runat="server" AutoPostBack="true" OnSelectedIndexChanged="Location_SelectedIndexChanged"&gt;&lt;/asp:DropDownList&gt;
  &lt;asp:DropDownList ID="Specialty" runat="server" AutoPostBack="true"&gt;&lt;/asp:DropDownList&gt;
  &lt;asp:DropDownList ID="Name" runat="server" AutoPostBack="true"&gt;&lt;/asp:DropDownList&gt;
  &lt;asp:DropDownList ID="Topic" runat="server" AutoPostBack="true"&gt;&lt;/asp:DropDownList&gt;
  </code></pre>
  
  <p>The above C# code is executed for the <code>Location_SelectedIndexChanged</code>.</p>
  
  <p>How can I replace the following:</p>
  
  <pre><code>Label1.Text = "All Specialties&lt;br/&gt;";
  Label1.Text += string.Join("&lt;br /&gt;", distinctValues); //Displays all specialties related to the location
  </code></pre>
  
  <p>with populating the <code>Specialty</code> dropdownlist (The <code>DataField</code> and the <code>DataValue</code> is the value).</p>
  
  <pre><code>Label1.Text += "All Providers&lt;br/&gt;";
  Label1.Text += string.Join("&lt;br /&gt;", distinctValues2); //Displays all providers related to the location
  </code></pre>
  
  <p>with populating the <code>Name</code> dropdownlist (The <code>DataField</code> and the <code>DataValue</code> is the value).</p>
  
  <pre><code>Label1.Text += "All Topics&lt;br/&gt;";
  Label1.Text += string.Join("&lt;br /&gt;", distinctValues3); //Displays all topics related to the specialty of that location
  </code></pre>
  
  <p>with populating the <code>Topic</code> dropdownlist (The <code>DataField</code> and the <code>DataValue</code> is the value).</p>