Show more than one dropdown column value C#

Question

How could it show more than the value of a column of 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 correctly, but I need the WHERE, to just show the addresses of that user, I need a WHERE with sessionparameter and sessionfield, as I am 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 1

My suggestion is that by instantiating each dropDownListItem in new ListItem(newName, localidade) you pass to the item’s value field not only the locale, but also the user ID, concatenated with the locale, separated by a blank space. More or less as follows:

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 this, the next step is to change on the *.aspx page the command SELECT to extract the id of the user that is stored in the item’s value field. As the dropdownlist value is now the concatenation of localidade + " " + idDoUsuário, then the user ID is the value after the blank, and can be obtained as follows:

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

With this change Sqldtasource will then 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>

Naturally, the DropDownList in *.aspx to change the name of the parameter so that it matches with the @ValorDoDropDownListItem that I am suggesting in this answer.

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

// 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];