DropDownList not working with SqlDataReader

Question

Hi i am trying to get DropDownList to work with SqlDataReader but its not populating the DropDownlist. The TextBox.Text Reader is working though.

Here is the code I am using:

    using (SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["connectionString"].ConnectionString))
    {
        SqlCommand command =
        new SqlCommand("SELECT * FROM [datarep].[dbo].[OrderHeader] WHERE [OrderNumber] = '"+OrderNumber+"'", con);
        con.Open();

        SqlDataReader read = command.ExecuteReader();

        while (read.Read())
        {
            TextBox2.Text = (read["CreatedDate"].ToString());
            TextBox3.Text = (read["CreatedBy"].ToString());
            CompanyStored = (read["CustomerID"].ToString());
            TextBox7.Text = (read["Store_Number"].ToString());

            DropDownList1.DataTextField = (read["Year"].ToString());
            DropDownList1.DataBind();



        }
        read.Close();
    }

how many items are being added to the DropDownList..? also move your DropDownList1.DataBind();` out side of the While loop and lookup how to assign DataSource to the DropdownList — MethodMan
– MethodMan, Commented Dec 3, 2014 at 21:07
well if there happens to be more I would still move it also look at this MDSN Binding Data to DropDownList — MethodMan
– MethodMan, Commented Dec 3, 2014 at 21:08
Since you're expecting only a single record back, you should remove the while - that makes it look like you could potentially have more than one record, which gets confusing to look at. If you know for sure you will get one record, just do read.Read();. If you're not sure, you can do if (read.Read()). Either way makes it clear that you're only filling in the controls once. — Joe Enos
– Joe Enos, Commented Dec 3, 2014 at 21:30

Joe Enos · Accepted Answer · 2014-12-03 21:32:11Z

Assuming your dropdown is already populated with the list of years, you need to set its value rather than setting its DataTextField - that property is meant for defining the column or property name of the data source for text, not setting the selected value itself.

DropDownList1.SelectedValue = read["Year"].ToString();

If you don't have the dropdown even populated yet, then you have to populate it in advance, using a data source, which is probably a list of years. For example, suppose you want all years between 2000 and 2050, something like this might do the trick:

var dataSource = Enumerable.Range(2000, 51)
   .Select(x => new { TheYear = x })
   .ToArray();

DropDownList1.DataSource = dataSource;
DropDownList1.DataValueField = "TheYear";
DropDownList1.DataTextField = "TheYear";
DropDownList1.DataBind();

Note that the DataTextField and DataValue field represent the property of the object in the data source.

For something simple like numbers, you can populate the dropdown one at a time as well, rather than using a data source - it's the same result in the end.

Community · Accepted Answer · 2017-05-23 12:27:18Z

0

SqlDataReader reads your data line by line. If you want to use a DataReader you'll have to add a new list item to your DDL per iteration

Try it like this:

while (read.Read())
{ 
    /*code omitted*/
    var item = new ListItem(); 
    item.Text = read["Year"].ToString();
    item.Value = read["Year"].ToString();
    DropDownList1.Items.Add(item);
}

Further reading and alternative solutions:

What is the right way to populate a DropDownList from a database?

edited May 23, 2017 at 12:27

CommunityBot

11 silver badge

answered Dec 3, 2014 at 21:07

Marco

24.1k13 gold badges81 silver badges132 bronze badges

4 Comments

Zai Po Over a year ago

I get this error DataBinding: 'System.Data.Common.DataRecordInternal' does not contain a property with the name '2013' @Serv

Zai Po Over a year ago

DropDownList1.DataBind();

Marco Over a year ago

try setting DropDownList1.DataValueField= "Year" DropDownList1.DataTextField = "Year"; I am not sure of the syntax right now.

Zai Po Over a year ago

thank you, it works now! had to add DropDownList1.Items.Clear(); to clear it from adding it multiple times

Collectives™ on Stack Overflow

DropDownList not working with SqlDataReader

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related