Populate Multiple Web Controls in ASP.Net using SqlDataReader

Sunday, December 28, 2003

I’ve seen a couple people ask how to do this in newsgroups and forums. The answer is yes it is quite possible, you just need to use the NextResult method on the SqlDataReader and DataBind each control separately. The following example assumes you have 3 DropDownList controls.

 

private void Page_Load(object sender, System.EventArgs e)
{
  if(!IsPostBack)
  {
    SqlConnection sqlConnection = new SqlConnection();
    sqlConnection.ConnectionString = "your connection here";
    sqlConnection.Open();

    SqlCommand sqlCommand = new SqlCommand();
    sqlCommand.Connection = sqlConnection;
    sqlCommand.CommandText = 
          "SELECT ProductName FROM Products;" +
          "SELECT CompanyName FROM Shippers;" +
          "SELECT LastName FROM Employees";     

      SqlDataReader sqlReader;
      sqlReader = sqlCommand.ExecuteReader();

      DropDownList1.DataSource = sqlReader;
      DropDownList1.DataTextField = "ProductName";
      DropDownList1.DataBind();

      sqlReader.NextResult();

      DropDownList2.DataSource = sqlReader;
      DropDownList2.DataTextField = "CompanyName";
      DropDownList2.DataBind();

      sqlReader.NextResult();

      DropDownList3.DataSource = sqlReader;
      DropDownList3.DataTextField = "LastName";
      DropDownList3.DataBind();

      sqlReader.Close();
      sqlConnection.Close();
   }
}
by K. Scott Allen K.Scott Allen
My Pluralsight Courses
The Podcast!