MVC - combining two field in a drop down from joint table

I have a table that has 2 foreign key. I would like to create in drop down that contain the value of these two foreign key. How
can I do that?

Here is my table definition:

Master Table
Id INT Prinary key
PhoneNumber VARCHAR(3)
IDLocation Foreign key to location table
IDDepartment Foreign Key to Department table

Master Table with data exammpe:
1|383-383-33838|1|1

Location Table
ID Primary key but this is related to the IDLocation Foreign key in Master table
Location Varchar

Location Table with example.
1|Chicago

Department Table
ID Primary key but this is relate to the ID Department foreng Key in Master table
Department varchar
1|Clothes

So in my drop down I would like something like this.

Please choose
Chicago - Clothes
or "Value" - "value"

yguyon28Asked: 2014-11-14

Who is Participating?

Solutions

LVL 34

Mike EghtebasConnect With a Mentor Database and Application DeveloperCommented: 2014-11-14

Set the following properties as shown below:

cmbBox.DataTextField = "Diaplay" // for "Chicago - Clothes"
cmbBox.DataValueField = "BothFKs" // for "1|1"

You could concatenate them in your Select statement:

Select 
   m.IDLocation + '|' + m.IDDepartment As BothFKs
   , l.Location + '-' + d.Department As Diaplay
   From [Master Table] m
   Inner Join [Location Table] l 
   on m.IDLocation = l.IDLocation
   Inner Join [Department Table] d 
   on m.IDDepartment = d.IDDepartment

Open in new window

This will produce:

1|1 and Chicago - Clothes
^-- value ^--display

Most likely, you need to use CAST() like, apply it if it errors:

Cast(m.IDLocation as varchar(5)) + '|' + Cast(m.IDDepartment as varchar(5)) As BothFKs

cmbBox.SelectedItem.Text --> "Chicago - Clothes"
cmbBox.SelectedItem.Value --> "1|1"
cmbBox.SelectedIndex --> 0 ,1, 2, ...