CODEWITHSUNDEEP
c#asp.net
Zai Po
Zai Po

Reputation: 1

DropDownList not working with SqlDataReader

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

Upvotes: 0

Views: 1785

Answers (2)

Joe Enos
Joe Enos

Reputation: 40383

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.

Upvotes: 1

Marco
Marco

Reputation: 23927

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:

Upvotes: 0

Related Questions