Displaying two Column Fields in DropDownList Control

There are two basic ways on how to display two fields from database in the DropDownList. The first one is concatenating it in the sql query and the second one is concatening it programmatically in codes.

Manipulating the sql query to concatenate two fields. Here’s the code block below.

private
void BindDropDownList() {
  DataTable dt = new DataTable();
  SqlConnection connection = new SqlConnection(GetConnectionString());
  try {
    connection.Open();
    string sqlStatement =
        "SELECT CustomerID + ' ---- ' + ContactName AS Name, CustomerID FROM "
        "Customers";
    SqlCommand sqlCmd = new SqlCommand(sqlStatement, connection);
    SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);

    sqlDa.Fill(dt);
    if (dt.Rows.Count > 0) {
      DropDownList1.DataSource = dt;
      DropDownList1.DataTextField = "Name";
      DropDownList1.DataValueField = "CustomerID";
      DropDownList1.DataBind();
    }
  } catch (System.Data.SqlClient.SqlException ex) {
    string msg = "Fetch Error:";
    msg += ex.Message;
    throw new Exception(msg);
  } finally {
    connection.Close();
  }
}

As you can see above, we concatenate two fields  “CustomerID and ContactName” by separating it by “—-”  to make it as one field called “Name” in the sql query .

Programmatically concatenating two fields in the DropDownList. Here’s the code block below.

private
void BindDropDownList() {
  DataTable dt = new DataTable();
  string id = string.Empty;
  string name = string.Empty;
  string newName = string.Empty;

  SqlConnection connection = new SqlConnection(GetConnectionString());
  try {
    connection.Open();
    string sqlStatement = "SELECT * FROM Customers";
    SqlCommand sqlCmd = new SqlCommand(sqlStatement, connection);
    SqlDataAdapter sqlDa = new SqlDataAdapter(sqlCmd);

    sqlDa.Fill(dt);
    if (dt.Rows.Count > 0) {
      for (int i = 0; i < dt.Rows.Count; i++) {
        id = dt.Rows[i]["CustomerID"].ToString();
        name = dt.Rows[i]["ContactName"].ToString();
        newName = id + " ---- " + name;
        DropDownList1.Items.Add(new ListItem(newName, id));
      }
    }
  } catch (System.Data.SqlClient.SqlException ex) {
    string msg = "Fetch Error:";
    msg += ex.Message;
    throw new Exception(msg);
  } finally {
    connection.Close();
  }
}

As you can see above, we loop through the DataTable and passed the field values in the string variable called “id and name” and set the concatenated values in a string variable called “newName”.

To test, call the method BindDropDownList() at Page_Load event. The page output should look like below.

Technorati Tags: ADO.NET,ASP.NET,C#,TipsTricks