populate two drop down lists with one query

Question

User154499744 posted

I have 2 drop down lists. dlist_1 and dlist_2. I want to populate both with the result from a sql query. This is the code I have.

string ConnectionString = ConfigurationManager.ConnectionStrings["SomeDB"].ConnectionString;
using (SqlConnection con = new SqlConnection(ConnectionString))
{
	SqlCommand cmd = new SqlCommand("SELECT DISTINCT [SomeValue] FROM dbo.SomeTable", con);
	cmd.CommandType = CommandType.Text;
	con.Open();

	SqlDataReader MyList = cmd.ExecuteReader();

	dlist_1.DataSource = MyList;
	dlist_1.DataValueField = "SomeValue";
	dlist_1.DataTextField = "SomeValue";
	dlist_1.DataBind();

	dlist_2.DataSource = MyList;
	dlist_2.DataValueField = "SomeValue";
	dlist_2.DataTextField = "SomeValue";
	dlist_2.DataBind();
}

This seems to populate only dlist_1. If I put dlist_2 first then it populates dlist_2 only. Basically, it populates whatever dlist I assign first and that's it.

I suppose I could have done 2 separate using ( ... ) { ... } with the same query, but I tried doing it this way. Why doesn't this work?

Answer 1

User-359936451 posted

What version of .net?

Answer 2

User475983607 posted

The SqlDataReader is a forward-only stream.  You need to copy the reader records to a collection then bind the collection to each dropdown or use a data adapter.

These examples use the Adventure Works database.

        public void PopulateDdlAdapter()
        {
            string ConnectionString = ConfigurationManager.ConnectionStrings["AdventureWorks"].ToString();
            string cmd = @"SELECT TOP 10 [ProductID]
                                      ,[Name]
                                  FROM [AdventureWorks2008R2].[Production].[Product]";
            SqlDataAdapter adapter = new SqlDataAdapter(cmd, ConnectionString);

            DataSet MyList = new DataSet();
            adapter.Fill(MyList, "Customers");


            dlist_1.DataSource = MyList;
            dlist_1.DataValueField = "ProductID";
            dlist_1.DataTextField = "Name";
            dlist_1.DataBind();

            dlist_2.DataSource = MyList;
            dlist_2.DataValueField = "ProductID";
            dlist_2.DataTextField = "Name";
            dlist_2.DataBind();

        }

        public void PopulateDdlList()
        {
            string ConnectionString = ConfigurationManager.ConnectionStrings["AdventureWorks"].ToString();
            using (SqlConnection con = new SqlConnection(ConnectionString))
            {
                SqlCommand cmd = new SqlCommand(@"SELECT TOP 10 [ProductID]
                              ,[Name]
                          FROM [AdventureWorks2008R2].[Production].[Product]", con);
                cmd.CommandType = CommandType.Text;
                con.Open();

                SqlDataReader MyList = cmd.ExecuteReader();
                ListItemCollection items = new ListItemCollection();
                while (MyList.Read())
                {
                    items.Add(new ListItem() { Value = MyList["ProductID"].ToString(), Text = (string)MyList["Name"].ToString() });
                }

                dlist_1.DataSource = items;
                dlist_1.DataValueField = "Value";
                dlist_1.DataTextField = "Text";
                dlist_1.DataBind();

                dlist_2.DataSource = items;
                dlist_2.DataValueField = "Value";
                dlist_2.DataTextField = "Text";
                dlist_2.DataBind();
            }


        }

Answer 3

User61956409 posted

Hi NewKid1nTown,

Firstly, this link discussed a similar issue, you could refer to it.

http://stackoverflow.com/questions/12228059/can-i-bind-same-datareader-object-to-2-controls

Secondly, as mgebhard said, you could try to use SqlDataAdapter to fill DataTable or DataSet then populate DropDowns with DataTable.

using (SqlConnection con = new SqlConnection(ConnectionString))
{
    con.Open();

    SqlDataAdapter adp = new SqlDataAdapter("SELECT DISTINCT [SomeValue] FROM dbo.SomeTable", con);

    DataTable dt1 = new DataTable();
    adp.Fill(dt1);
    DataTable dt2 = new DataTable();
    adp.Fill(dt2);

    dlist_1.DataSource = dt1;
    dlist_1.DataValueField = "Field1";
    dlist_1.DataTextField = "Field2";
    dlist_1.DataBind();

    dlist_2.DataSource = dt2;
    dlist_2.DataValueField = "Field1";
    dlist_2.DataTextField = "Field2";
    dlist_2.DataBind();

    con.Close();
}

Best Regards,

Fei Han