Show more than one column value from dropdown C #

Question

Show more than one column value from dropdown C #

Navigation

#1 by (1 votes)

3

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

sql c# dropdown

asked by anonymous 12.07.2015 / 00:38

1 answer

Text overwriting the header How to click on a ListView item and open an Activity referring to the item I clicked on?

score 1 · Answer 1

My suggestion is that when instantiating every dropDownListItem in new ListItem(newName, localidade) you go to the value field of the item not only the location, but also the user ID, concatenated with the locale, separated by a 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 in the * .aspx page the SELECT command to extract the id of the user that is stored in the item's value field. Since the value of the dropDownList is now the concatenation of localidade + " " + idDoUsuário , then the user ID is the value after white space, and 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 must also change the DropDownList in * .aspx to modify the parameter name so that it matches the @ValorDoDropDownListItem I am suggesting in that response.

Another important point to note is that if you use this solution, you should still do another treatment when you use the value of% selected% to extract the value of the locale. When you use this value, for example in some postback in the code-behind, 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];