title: Dropdownlist and Datareader
body: <p>After trying many solutions listed on the internet I am very confused now.  I have a C#/SQL web application for which I am simply trying to bind an ExecuteReader command to a Dropdownlist so the user can select a value.  This is a VS2008 project on an XP OS. </p>
  
  <p>How it works is after the user selects a table, I use this selection as an input parameter to a method from my Datamatch.aspx.cs file.  Then this Datamatch.aspx.cs file calls a method from my ADONET.cs class file.  Finally this method executes a SQL procedure to return the list of columns from that table.  (These are all tables in Adventureworks DB).  I know that this method returns successfully the list of columns if I execute this SP in SSMS.  However, I'm not sure how to tell if it works in VS or not.</p>
  
  <p>This should be simple.  How can I do this?  Here is some of my code.  The T-sQL stored proc:</p>
  
  <pre><code>CREATE PROCEDURE [dbo].[getColumnNames]
  @TableName VarChar(50) AS
  BEGIN
  SET NOCOUNT ON;
  SELECT col.name 'COLUMN_NAME' FROM sysobjects obj
  INNER JOIN syscolumns col ON obj.id = col.id
  WHERE obj.name = @TableName
  END
  </code></pre>
  
  <p>It gives me desired output when I execute following from SSMS: <code>exec getColumnNames 'AddressType'</code></p>
  
  <p>And the code from Datamatch.aspx.cs file currently is:</p>
  
  <p>private void CreateDropDownLists()
  {
      SqlDataReader dr2 = ADONET_methods.DisplayTableColumns(targettable);</p>
  
  <pre><code>int NumControls = targettable.Length;
  DropDownList ddl = new DropDownList();
  DataTable dt = new DataTable();
  
  dt.Load(dr2);
  
  ddl.DataValueField = "id";
  ddl.DataTextField = "text";
  ddl.DataSource = dt;
  ddl.DataBind();
  
  for (int counter = 0; counter &lt; NumberOfControls; counter++)
  {
      ddl.ID = "DropDownListID " + (counter + 1).ToString();
      btnSubmit.Style.Add("top", "auto");
      btnSubmit.Style.Add("left", "auto");
      btnSubmit.Style.Add("position", "absolute");
  
      if (counter &lt; 7)
      {
          ddl.Style["top"] = 100 * counter + 80 + "px";
          ddl.Style["left"] = 250 + "px";
          int bSubmitPosition = NumberOfControls * 100 + 80;
          btnSubmit.Style.Add("top", System.Convert.ToString(bSubmitPosition) + "px");
      }
      else if (counter &gt;= 7)
      {
          ddl.Style["top"] = 100 * counter - 620 + "px";
          ddl.Style["left"] = 550 + "px";
          int bSubmitPosition = NumberOfControls * 100 - 620;
          btnSubmit.Style.Add("top", System.Convert.ToString(bSubmitPosition) + "px");
      }
      ddl.SelectedIndexChanged += new EventHandler(SelectedIndexChanged);
      ddl_ht.Add(counter, ddl.SelectedValue);
  
      pnlDisplayData.Controls.Add(ddl);
      pnlDisplayData.Controls.Add(new LiteralControl("&lt;br&gt;&lt;br&gt;&lt;br&gt;"));
      pnlDisplayData.Visible = true;
      pnlDisplayData.FindControl(ddl.ID);
      //  dr.Close();
  }
  </code></pre>
  
  <p>}</p>
  
  <p>private void CreateLabels()
  {
      for (int counter = 0; counter &lt; NumberOfControls; counter++)
      {
          Label lbl = new Label();
          lbl.ID = "Label" + counter.ToString();
          lbl.Text = headers[counter];
          lbl.Style["position"] = "absolute";
          if (counter &lt; 7)
          {
              lbl.Style["top"] = 100 * counter + 50 + "px";
              lbl.Style["left"] = 250 + "px";
          }
          else if (counter >= 7)
          {
              lbl.Style["top"] = (100 * counter) - 650 + "px";
              lbl.Style["left"] = 550 + "px";
          }</p>
  
  <pre><code>    pnlDisplayData.Controls.Add(lbl);
      pnlDisplayData.Controls.Add(new LiteralControl("&lt;br&gt;&lt;br&gt;&lt;br&gt;"));
  }
  </code></pre>
  
  <p>}</p>
  
  <p>Where ADONET_methods.DisplayTableColumns(targettable) is:</p>
  
  <pre><code>public static SqlDataReader DisplayTableColumns(string tt)
      {
          SqlDataReader dr = null;
          string TableName = tt;
          string connString = "Server=(local);Database=AdventureWorks;Integrated Security = SSPI";
          string errorMsg;
          SqlConnection conn2 = new SqlConnection(connString);
          SqlCommand cmd = new SqlCommand("getColumnNames");  //conn2.CreateCommand();
  
          try
          {
              cmd.CommandType = CommandType.StoredProcedure;
              cmd.Connection = conn2;
              SqlParameter parm = new SqlParameter("@TableName", SqlDbType.VarChar);
              parm.Value = "Person." + TableName.Trim();
              parm.Direction = ParameterDirection.Input;
              cmd.Parameters.Add(parm);
              conn2.Open();
              dr = cmd.ExecuteReader();
  
          }
          catch (Exception ex)
          {
              errorMsg = ex.Message;
          }
          return dr;
      }
  </code></pre>
  
  <p>The CreateLabels method above correctly shows me the labels.  But the CreateDropDownLists method just shows me one dropdownlist with nothing in it.  In other words, it is not selectable. 
  So how can I verify that the datareader is returning all 4 columns and inspect their values?  I was able to find from datareader "COLUMN_NAME" but I don't know what properties to search to verify the column names.</p>