Hello All, We are going to start new batch from next week. message/call or mail us for more details.

15 February 2013

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.



No comments:

Post a Comment