Dropdownlist and Datareader

Question

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.

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.

This should be simple. How can I do this? Here is some of my code. The T-sQL stored proc:

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

It gives me desired output when I execute following from SSMS: exec getColumnNames 'AddressType'

And the code from Datamatch.aspx.cs file currently is:

private void CreateDropDownLists() { SqlDataReader dr2 = ADONET_methods.DisplayTableColumns(targettable);

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 < 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 < 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 >= 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("<br><br><br>"));
    pnlDisplayData.Visible = true;
    pnlDisplayData.FindControl(ddl.ID);
    //  dr.Close();
}

}

private void CreateLabels() { for (int counter = 0; counter < NumberOfControls; counter++) { Label lbl = new Label(); lbl.ID = "Label" + counter.ToString(); lbl.Text = headers[counter]; lbl.Style["position"] = "absolute"; if (counter < 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"; }

    pnlDisplayData.Controls.Add(lbl);
    pnlDisplayData.Controls.Add(new LiteralControl("<br><br><br>"));
}

}

Where ADONET_methods.DisplayTableColumns(targettable) is:

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;
    }

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.

Solution 1

It doesn't look like you're actually binding the dropdown anywhere in this code. You need to do something like this:

ddl.DataTextField = "COLUMN_NAME";
ddl.DataValueField = "COLUMN_NAME";
ddl.DataSource = dr.ExecuteReader();
ddl.DataBind();

Alternately, you can do this all in your page markup by using a SqlDataSource control.

  <asp:SqlDataSource
      id="SqlDataSource1"
      runat="server"
      DataSourceMode="DataSet"
      ConnectionString="myConnString"
      SelectCommand="myStoredProcedure"
      >
  </asp:SqlDataSource>

  <asp:MyDropDownList id="ddl" runat="server" DataSource="SqlDataSource1"
       DataTextField="COLUMN_NAME" DataValueField="COLUMN_NAME" />

Solution 2

In the ADONET_methods.DisplayTableColumns(targettable) method before returning dr, check if you get some value for dr.GetValue() using a breakpoint

string temp;
while(dr.Read())
{
    temp = dr.GetValue();  //use a breakpoint here
}

Also instead of using dataReader and loading it to dataTable, you can better use a dataAdapter to fill the dataTable directly

public static DataTable DisplayTableColumns(string tt) 
{ 
    Datatable dt = new DataTable();
    SqlDataAdapter da = new SqlDataAdapter();

    try 
    { 
        da.Fill(dt); 
    } 
    catch (Exception ex) 
    { 
        errorMsg = ex.Message; 
    } 

    string temp;
    foreach(DataRow row in dt.Rows)
    {
        foreach(DataColumn column in dt.Columns)
        {
            temp = (row[column]);  // use your breakpoint here
        }
    }

    return dt; 
} 

Comments

[salvationishere]

Checkout my edited question above now. Thanks for your tips!

[salvationishere]

Thank you guys for all your helps! But I found the real problem was that I was giving this SQL stored procedure the wrong input value: "Person.AddressType" instead of "AddressType". I fixed this now and now this is working correctly!

[Roman]

@salvationishere: This solution is still an improvement over returning a raw DataReader. If at all an option, you should try to use Linq to SQL.