Get two database fields, combine, and populate dropdown with the resulting string

Question

I'm populating a dropdown with this method:

private DataTable PopulateDropdown(string connectionString, DataTable datatable, string query, DropDownList myDropDownList, string DataTextField, string DataValueField)
    {
        DataTable dt = new DataTable();
        using (SqlConnection con = new SqlConnection(connectionString))
        {
            try
            {
                SqlDataAdapter adapter = new SqlDataAdapter(query, con);
                adapter.Fill(dt);
                myDropDownList.DataSource = dt;
                myDropDownList.DataTextField = DataTextField;
                myDropDownList.DataValueField = DataValueField;
                myDropDownList.DataBind();
            }
            catch (Exception ex) { }
        }
        return dt;
    }

This is great as it allows me to get any database field simply by changing the query. Example:

string query = "select firstName, id from table";
    PopulateDropdown(constring, dt, query, aspdropdown, "FirstName", "id");

However I need to be able to get two fields from the database and join them together, before populating the dropdownlist. I tried using:

string query = "select firstName, lastName, id from table";
PopulateDropdown(constring, dt, query, aspdropdown, "FirstName", "id", JoinedString: "lastName");

string JoinedString was added as another parameter and included in the method like this:

myDropDownList.DataTextField = DataTextField + JoinedString;

Unfortunately this just gave me an empty dropdown list. I don't really know how else to approach this. Can anyone show me how can I get it working?

I'm trying to get things working properly before looking at how to properly build exception handling! As a new developer, it's extremely hard to figure out the little things let alone develop good practices across the board. Apologies if the code offended you. — Syntax Error, Dec 09 '16 at 15:21
Maybe that is only a temporary catch until he is ready to put in the full exception handling @TimSchmelter — vipersassassin, Dec 09 '16 at 15:22
http://stackoverflow.com/questions/256832/c-sharp-fill-a-combo-box-with-a-datatable — vipersassassin, Dec 09 '16 at 15:23

mybirthname · Accepted Answer · 2016-12-09T15:26:26.417

1

Do this in sql query:

string query = "select firstName + ', ' + lastName AS FullName, id from table";

You will call your method like this:

PopulateDropdown(constring, dt, query, aspdropdown, "FullName", "id");

After that:

using(SqlDataAdapter adapter = new SqlDataAdapter(query, con))
{
    adapter.Fill(dt);
}
myDropDownList.DataSource = dt;
myDropDownList.DataTextField = dataTextField;//FullName
myDropDownList.DataValueField = dataValueField;//ID
myDropDownList.DataBind();

P.S Dispose your SqlDataAdapter

edited Dec 09 '16 at 15:26

answered Dec 09 '16 at 15:21

mybirthname

17,949
3
31
55

1

Perfect, thank you. I didn't even think of just approaching it at the SQL side – Syntax Error Dec 09 '16 at 15:24
@JayF1 you can mark the answer as correct if helped you. – mybirthname Dec 09 '16 at 15:32
1

Yep, I was just waiting for the timer to allow me to do so! – Syntax Error Dec 09 '16 at 15:33

score 0 · Answer 2 · answered Dec 09 '16 at 15:24

Unfortunately binding two values directly is not possible. You need to add a new column to your datatable, either in your sql query or later codebehind.

"select firstName, lastName, id, firstname + lastname AS JoinedString from table";

PopulateDropdown(constring, dt, query, aspdropdown, "FirstName", "JoinedString");

Get two database fields, combine, and populate dropdown with the resulting string

2 Answers2