Data not displaying in dynamic drop down list asp.net & C# (no errors too)

Question

// aspx code

   <asp:DropDownList ID="DropDownList1" runat="server" DataTextField="fullname" DataValueField="username" AutoPostBack="True" Width="125px" Height="22px"></asp:DropDownList>

// Code behind

 SqlConnection myConnection = new SqlConnection("Server=xyz;Database=db;Uid=db;Pwd=12345");
 myConnection.Open();         
 SqlDataReader myReaderddl = null;
 SqlCommand myCommandddl = new SqlCommand("SELECT [username], [fullname] FROM [qa_users]", myConnection);
 myReaderddl = myCommandddl.ExecuteReader();
 myReaderddl.Read();
 ddlrep.DataSource = myReaderddl;                    
 ddlrep.DataValueField = "[username]";
 ddlrep.DataTextField = "[fullname]";
 ddlrep.DataBind();
 myReaderddl.Close();

Where in code are you putting this? Page_Load? Can you please provide markup describing DDL as well as context of code-behind? — Jaime Torres
– Jaime Torres, Commented Jul 10, 2012 at 12:41

Joshua · Accepted Answer · 2012-07-10 13:20:43Z

2

I haven't tested this code, but it should work.

using (var myConnection = new SqlConnection("Server=xyz;Database=db;Uid=db;Pwd=12345"))
{
    using (var myCommandddl = new SqlCommand("SELECT [username], [fullname] FROM [qa_users]", myConnection))
    {
        var table = new DataTable();

        using(var myAdapter = new SqlDataAdapter(myCommandddl))
            myAdapter.Fill(table);

        ddlrep.DataSource = table;
        ddlrep.DataValueField = "username";
        ddlrep.DataTextField = "fullname";
        ddlrep.DataBind();
    }
}

There's a couple changes I made that you should note:

Use of a USING statement. This offers a number of benefits, such as automatically calling Dispose() on your IDisposable objects, regardless of whether an Exception is thrown. In my opinion, it's a nice visual for showing when and where an object is being disposed. http://msdn.microsoft.com/en-us/library/yh598w02.aspx
Not using a SqlDataRader directly, rather, use of the SqlDataAdapter to fill the table: http://msdn.microsoft.com/en-us/library/879f39d8

answered Jul 10, 2012 at 13:20

Joshua

4,17927 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

user1442828 Over a year ago

This worked. im not sure what issue was. but worked fine. Thanks

Joshua Over a year ago

I think the issue was trying to assign a SqlDataReader to the DataSource property. The DataSource property expects an instance of IEnumerable, IListSource, IDataSource, or IHierarchicalDatasource.

Jaime Torres · Accepted Answer · 2012-07-10 13:06:13Z

0

Update your codebehind to look like the following:

if (!IsPostBack)
{

   SqlConnection myConnection = new SqlConnection("Server=xyz;Database=db;Uid=db;Pwd=12345");
    try 
    {
       myConnection.Open();         
       SqlDataReader myReaderddl = null;
       SqlCommand myCommandddl = new SqlCommand("SELECT [username], [fullname] FROM [qa_users]", myConnection);
       myReaderddl = myCommandddl.ExecuteReader();
       if (myReaderddl.HasRows)
       {
          ddlrep.DataSource = myReaderddl;                    
          ddlrep.DataValueField = "username";
          ddlrep.DataTextField = "fullname";
          ddlrep.DataBind();
       }
    }
    finally 
    {
       if (myConnection != null)
       {
          myConnection.Close();
       }
    }
}

edited Jul 10, 2012 at 13:06

answered Jul 10, 2012 at 12:46

Jaime Torres

10.5k1 gold badge50 silver badges56 bronze badges

7 Comments

Jaime Torres Over a year ago

When you break on myReaderddl = myCommanddl.ExecuteReader(), do you ever go into the if block? Is data being returned?

user1442828 Over a year ago

data is pulled but its not binding to the drop down list

user1442828 Over a year ago

Torres:: still not displaying

Jaime Torres Over a year ago

Just for giggles, instead of doing the binding, if you were to replace the binding with: while(myReaderddl.Read()) { ddlRep.Items.Add(new ListItem(myReaderddl["fullname"], myReaderddl["username"]));}, does it work? Does it iterate over anything?

user1442828 Over a year ago

either way its pulling data but not displaying.

|

Subhankar · Accepted Answer · 2012-07-10 13:22:17Z

0

Your DropDownlist Should be like this:

<asp:DropDownList ID="ddlrep" runat="server" Width="200px" ></asp:DropDownList>

and In code behind, in pageload or in any methode

SqlConnection myConnection = new SqlConnection("Server=xyz;Database=db;Uid=db;Pwd=12345");
 myConnection.Open();         
 SqlCommand myCommandddl = new SqlCommand("SELECT username, fullname FROM qa_users", myConnection);
 SqlDataReader myReaderddl = myCommandddl.ExecuteReader();
 if(myReaderddl.HasRows)
 {
 ddlrep.DataSource = myReaderddl;                    
 ddlrep.DataValueField = "username";
 ddlrep.DataTextField = "fullname";
 ddlrep.DataBind();
 }
 myReaderddl.Close();

in the pageload put the code under this block

if (!IsPostBack)
        {
        }

edited Jul 10, 2012 at 13:22

answered Jul 10, 2012 at 12:56

Subhankar

1266 bronze badges

12 Comments

Subhankar Over a year ago

if possible then debug it..and see its coming inside the if block or not...use the modified query remove Brackets..

user1442828 Over a year ago

I debugged, data is there but its not binding.

Subhankar Over a year ago

ok do one thing remove the dropdown list and paste the code what i mentioned for drop down..the first one.

user1442828 Over a year ago

I tried your previous code too. One thing is for sure its pulling data but its not displaying

user1442828 Over a year ago

SqlDataReader myReaderddl = null;SqlCommand myCommandddl = new SqlCommand("SELECT username, fullname FROM qa_users", myConnection); myReaderddl = myCommandddl.ExecuteReader(); myReaderddl.Read(); if (myReaderddl.HasRows) { ddlrep.DataSource = myReaderddl; ddlrep.DataValueField = "username"; ddlrep.DataTextField = "fullname"; ddlrep.DataBind(); }myReaderddl.Close();

|

Drakkainen · Accepted Answer · 2012-07-10 13:24:10Z

0

This is some of my old code so it is not pretty ( I use EF these days), but try somehting like this:

SqlCommand cmd = new SqlCommand("SELECT [username], [fullname] FROM [qa_users]", myConnection);

cmd.Connection.Open();
SqlDataReader data_table = null;
data_table = cmd.ExecuteReader();
ddlrep.DataSource = data_table;
ddlrep.DataValueField = "username";
ddlrep.DataTextField = "fullname";
ddlrep.DataBind();
data_table.Close();
cmd.Connection.Close();
cmd.Connection.Dispose();

I pulled this from a working project so it should work.

answered Jul 10, 2012 at 13:24

Drakkainen

1,14212 silver badges25 bronze badges

Collectives™ on Stack Overflow

Data not displaying in dynamic drop down list asp.net & C# (no errors too)

4 Answers 4

2 Comments

7 Comments

12 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

7 Comments

12 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related