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?
 
Mike EghtebasConnect With a Mentor Database 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, ...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.