Show more than one value from the C# dropdown column

Question:

How could it show more than one column value in a dropdown?

I tried the following method:

private void BindDropDownList()
    {
        DataTable dt = new DataTable();
        string localidade = string.Empty;
        string distrito = string.Empty;
        string newName = string.Empty;

        SqlConnection connection;
        string connectionString = WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
        connection = new SqlConnection(connectionString);
        try
        {
            connection.Open();
            string sqlStatement = "SELECT * FROM [Moradas] WHERE ([IDUser] = @IDUser)";
            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++)
                {
                    localidade = dt.Rows[i]["Localidade"].ToString();
                    distrito = dt.Rows[i]["Distrito"].ToString();
                    newName = localidade + " ---- " + distrito;
                    ddlMoradaSecd.Items.Add(new ListItem(newName, localidade));
                }
            }
        }
        catch (System.Data.SqlClient.SqlException ex)
        {
            string msg = "Fetch Error:";
            msg += ex.Message;
            throw new Exception(msg);
        }
        finally
        {
            connection.Close();
        }
    }

Without the WHERE works fine, but I need the WHERE , to just show that user's addresses, I need a WHERE with sessionparameter and sessionfield , as I'm using in sqldatasource :

<asp:SqlDataSource ID="SqlDataSourceMoradaSecd" runat="server" 
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT * FROM [Moradas] WHERE ([IDUser] = @IDUser) ORDER BY [Morada]">
    <SelectParameters>
        <asp:SessionParameter Name="IDUser" SessionField="IDUtilizador" Type="String" />
    </SelectParameters>
</asp:SqlDataSource>

Answer:

My suggestion is that when you instantiate each dropDownListItem in new ListItem(newName, localidade) you pass to the item value field not only the location, but also the user ID, concatenated with the location, separated by a blank space. More or less like this:

for (int i = 0; i < dt.Rows.Count; i++)
{
    localidade = dt.Rows[i]["Localidade"].ToString();
    distrito = dt.Rows[i]["Distrito"].ToString();
    newName = localidade + " ---- " + distrito;

    // Obtém o ID do usuário.
    string idUsuario = dt.Rows[i]["IDUser"].ToString();

    // Coloca no campo valor de cada item a localidade e o id do usuário, 
    // separados por um espaço em branco.
    ddlMoradaSecd.Items.Add(new ListItem(newName, string.Concat(localidade, " ", idUsuario)));
}

Having done that, the next step is to change the SELECT command in the *.aspx page to extract the user id that is stored in the item's value field. As the dropDownList value is now the concatenation of localidade + " " + idDoUsuário , so the userid is the value after the white space, and it can be obtained as follows:

SUBSTRING (@ValorDoDropDownListItem, CHARINDEX ( ' ', @ValorDoDropDownListItem ), LEN(@ValorDoDropDownListItem))

With this change the SqlDtaSource will look something like this:

<asp:SqlDataSource ID="SqlDataSourceMoradaSecd" runat="server" 
ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT * FROM [Moradas] WHERE ([IDUser] = 
SUBSTRING (@ValorDoDropDownListItem, CHARINDEX ( ' ', @ValorDoDropDownListItem ), LEN(@ValorDoDropDownListItem))) 
ORDER BY [Morada]">
<SelectParameters>
    <asp:SessionParameter Name="ValorDoDropDownListItem" SessionField="ValorDoDropDownListItem" Type="String" />
</SelectParameters>

Of course, you also need to change the DropDownList in *.aspx to modify the parameter name so that it matches the @ValorDoDropDownListItem I'm suggesting in this answer.

Another important point to note is that if you use this solution, you must do yet another treatment when using the value of the selected dropDownListItem to extract the location value. When you are going to use this value, for example in some code-behind postback, you should do the following to get the locale:

// Lembrando que o valor dos itens é localidade + " " + idUsuário,
// a localidade corresponde então ao texto que está antes do espaço
// em branco no valor de cada item.
var localidade = valorDoItemSelecionado.Split(' ')[0];
Post Views: 1
Scroll to Top