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:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mike EghtebasDatabase and Application DeveloperCommented:
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, ...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.