Hi guys.

I have a table in SQL Server which looks like this:

ID Code Name     Surname
1   MS  Mike     Smith 
2   JD  John     Doe
3   UP  Unknown  Person

and so on...

Now I want to bind the data from this table into the ComboBox in a way that in the ComboBox I have displayed value from the Code column.

I am doing the binding in this way:

SqlDataAdapter sqlAdapter = new SqlDataAdapter("SELECT * FROM dbo.Users ORDER BY Code", MainConnection);
sqlAdapter.Fill(dsUsers, "Users");
cbxUsers.DataSource = dsUsers.Tables["Users"];
cmUsers = (CurrencyManager)cbxUsers.BindingContext[dsUsers.Tables["Users"]];
cbxUsers.DisplayMember = "Code";

And this code seems to work. I can scroll through the list of Codes. Also I can start to write code by hand and ComboBox will autocomplete the code for me.

However, I wanted to put a label at the top of the combobox to display Name and Surname of the currently selected user code.

My line of though was like that: "So, I need to find an event which will fire up after the change of code in combobox and in that event I will get the current DataRow..."

I was browsing through the events of combobox, tried many of them but without a success.

For example:

private void cbxUsers_SelectionChangeCommitted(object sender, EventArgs e)
{
 if (cmUsers != null)
 {
  DataRowView drvCurrentRowView = (DataRowView)cmUsers.Current;
  DataRow drCurrentRow = drvCurrentRowView.Row;
  lblNameSurname.Text = Convert.ToString(drCurrentRow["Name"]) + " " + Convert.ToString(drCurrentRow["Surname"]);
 }
}

This give me a strange results. Firstly when I scroll via mouse scroll it doesn't return me the row wich I am expecting to obtain. For example on JD it shows me "Mike Smith", on MS it shows me "John Doe" and on UP it shows me "Mike Smith" again! The other problem is that when I start to type in ComboBox and press enter it doesn't trigger the event.

However, everything works as expected when I bind data to lblNameSurname.Text in this way:

lblNameSurname.DataBindings.Add("Text", dsusers.Tables["Users"], "Name");

The problem here is that I can bind only one column and I want to have two. I don't want to use two labels for it (one to display name and other to display surname).

So, what is the solution to my problem?

Also, I have one question related to the data selection in ComboBox. Now, when I type something in the combobox it allows me to type letters that are not existing in the list. For example, I start to type "J" and instead of finishing with "D" so I would have "JD", I type "Jsomerandomtexthere". Combobox will allow that but such item does not exists on the list. In other words, I want combobox to prevent user from typing code which is not on the list of codes.

Thanks in advance for your time.