Binding combobox with distinct and concatenated value

Question

Hi all,

I'm trying to bind a combobox with the following. I have 2 issues. First, I cannot get the combobox to populate with values. Right now it shows 'System.System.Data.DataRowView' upon execution. Second, I will need to afterwards refer to the individual fields themselves to query the database, so I hardly know what to put as 'ValueMember'. Your help is greatly appreciated. Thanks so much.

try
               {
                   string query = "SELECT DISTINCT direction+ ' ' + street + ' ' + city + ', ' + state + ' ' + zip FROM table";

                   SqlCommand cmd = new SqlCommand(query,conn);
                   SqlDataAdapter da = new SqlDataAdapter(query, conn);
                   conn.Open();
                   DataTable dt = new DataTable();
                   da.Fill(dt);
                   cboAddress.DataSource = dt;
                   cboAddress.ValueMember = ;   //  What to put here?

                   dt.Columns.Add("Address", typeof(string), "direction + ' ' + city + ' ' + state + ' ' + zip");
                   cboAddress.DisplayMember = "Address";

                   conn.Close();

               }
               catch (Exception)
               {

                   MessageBox.Show("Error loading addresses");
               }

Answer 1

Hello,

The best way to handle this IMHO is to use a list with ToString override. Here I read data from multiple tables (but can also be just one table) using 10 records and in this case the tables have no duplicates but they did then I would had used DISTINCT.

Full source with database script in the following GitHub repository.

SELECT TOP (10) E.EmployeeID,   
                E.LastName,   
                E.FirstName,   
                E.Address,   
                E.City,   
                E.PostalCode,   
                C.Name AS CountryName,   
                E.ContactTypeIdentifier,   
                C.CountryIdentifier  
FROM Employees AS E  
     INNER JOIN Countries AS C ON E.CountryIdentifier = C.CountryIdentifier;  

Class/Container

This class is used to return data back to the form.

public class Employee  
{  
    public int EmployeeID { get; set; }  
    public string LastName { get; set; }  
    public string FirstName { get; set; }  
    public string Address { get; set; }  
    public string City { get; set; }  
    public string PostalCode { get; set; }  
    public string CountryName { get; set; }  
    public int ContactTypeIdentifier { get; set; }  
    public int CountryIdentifier { get; set; }  
  
    public override string ToString() => $"{Address} {City} {PostalCode} {CountryName}";  
  
}  

Data class

The SelectStatement is broken out to clean up the read method

    public class Operations  
    {  
        public static string ConnectionString =   
            "Data Source=.\\SQLEXPRESS;Initial Catalog=NorthWind2020;Integrated Security=True";  
  
        public static List<Employee> ReadEmployees()  
        {  
            var list = new List<Employee>();  
            using (var cn = new SqlConnection() { ConnectionString = ConnectionString })  
            {  
                using (var cmd = new SqlCommand() { Connection = cn, CommandText = SelectStatement })  
                {  
                    cn.Open();  
                    var reader = cmd.ExecuteReader();  
                    while (reader.Read())  
                    {  
                        list.Add(new Employee()  
                        {  
                            EmployeeID = reader.GetInt32(0),  
                            LastName = reader.GetString(1),  
                            FirstName = reader.GetString(2),  
                            Address = reader.GetString(3),  
                            City = reader.GetString(4),  
                            PostalCode = reader.GetString(5),  
                            CountryName = reader.GetString(6),  
                            ContactTypeIdentifier = reader.GetInt32(7),  
                            CountryIdentifier = reader.GetInt32(8)  
                        });  
                    }  
  
                }  
  
                return list;  
            }  
        }  
  
        private static string SelectStatement => @"  
SELECT TOP (10) E.EmployeeID,   
                E.LastName,   
                E.FirstName,   
                E.Address,   
                E.City,   
                E.PostalCode,   
                C.Name AS CountryName,   
                E.ContactTypeIdentifier,   
                C.CountryIdentifier  
FROM Employees AS E  
     INNER JOIN Countries AS C ON E.CountryIdentifier = C.CountryIdentifier;  
";  
    }  

Form code, one ComboBox, one Button.

The BindingSource is optional but makes life easier.

public partial class Form1 : Form  
{  
    private readonly BindingSource _employeeBindingSource = new BindingSource();  
    public Form1()  
    {  
        InitializeComponent();  
        Shown += OnShown;  
    }  
  
    private void OnShown(object sender, EventArgs e)  
    {  
        _employeeBindingSource.DataSource = Operations.ReadEmployees();  
        EmployeeComboBox.DataSource = _employeeBindingSource;  
    }  
  
    private void CurrentEmployeeButton_Click(object sender, EventArgs e)  
    {  
        if (_employeeBindingSource.Current == null) return;  
          
        var emp = (Employee) _employeeBindingSource.Current;  
        MessageBox.Show(  
            $"ID: {emp.EmployeeID}\n"+   
            $"Contact Id: {emp.ContactTypeIdentifier}\nName {emp.FirstName} {emp.LastName}");  
    }  
}