I have 3 tables:
1) House:
| ID_house | House_names | num_region |
---------------------------------------
| int | names | int |
num_region - a foreign key, which equals to a primary key(Region.ID_region) in Region table.
2) Region:
| ID_region | Nameofregions | num_arearegion |
---------------------------------------------
| int | names | int |
num_arearegion - a foreign key, equals to primary key(Areas_InRegion.ID_areas) in Areas_InRegion table.
3) Areas_InRegion:
| ID_areas | Area_names |
------------------------
| int | names |
In the form I have 3 comboboxes:
1) cmbHouse - for showing names of houses from House table.
2) cmbRegion - for showing names of regions from Region table.
3) cmbArea - for showing names of areas in regions from Areas_InRegion table.
I populate comboboxes like this:
//cmbHouse
string cmbHouse_query = "SELECT * FROM House";
OleDbDataAdapter dahouse = new OleDbDataAdapter(cmbHouse_query, connection);
DataTable tablehouse = new DataTable();
dahouse.Fill(tablehouse);
cmbHouse.DataSource = tablehouse;
cmbHouse.DisplayMember = "House_names";
cmbHouse.ValueMember = "House.num_region";
cmbHouse.SelectedIndex = -1;
//cmbRegion
string cmbRegion_query = "SELECT * FROM Region";
OleDbDataAdapter daregion = new OleDbDataAdapter(cmbRegion_query, connection);
DataTable tableregion = new DataTable();
daregion.Fill(tableregion);
cmbRegion.DataSource = tableregion;
cmbRegion.DisplayMember = "Nameofregions";
cmbRegion.ValueMember = "Region.ID_region";
cmbRegion.SelectedIndex = -1;
//cmbArea
string cmbArea_query = "SELECT * FROM Areas_InRegion";
OleDbDataAdapter daArea = new OleDbDataAdapter(cmbArea_query, connection);
DataTable tablearea = new DataTable();
daArea.Fill(tablearea);
cmbArea.DataSource = tablearea;
cmbArea.DisplayMember = "Names_OfAreas";
cmbArea.ValueMember = "Areas_InRegion.ID_areas";
cmbArea.SelectedIndex = -1;
Combobox cmbRegion has valuemember as the primary key.
I can filter cmbHouse combobox by the primary key as valuemeber in cmbRegion combo, but I can't filter cmbArea combobox.
private void cmbHouse_SelectionChangeCommitted(object sender, EventArgs e)
{
if (cmbHouse.SelectedIndex > -1)
{
//DataRow selectedDataRow = ((DataRowView)cmbHouse.SelectedItem).Row;
int num_region = Convert.ToInt32(cmbHouse.SelectedValue);
OleDbCommand com = new OleDbCommand();
com.CommandText = "SELECT * FROM Region WHERE Region.ID_region=" + num_region.ToString() + "";
//com.Parameters.AddWithValue("House.num_region", typeof(int));
OleDbDataAdapter danum_region = new OleDbDataAdapter(com.CommandText, connection);
DataTable tablenum_region = new DataTable();
danum_region.Fill(tablenum_region);
cmbRegion.DataSource = tablenum_region;
cmbRegion.DisplayMember = "Nameofregions";
cmbRegion.ValueMember = "Region.num_arearegion";
//cmbRegion.SelectedIndex = -1;
if(cmbRegion.SelectedIndex > -1)
{
int num_area = Convert.ToInt32(cmbRegion.SelectedValue);
OleDbCommand com2 = new OleDbCommand();
com2.CommandText = "SELECT * FROM Areas_InRegion WHERE Areas_InRegion.ID_areas=" + num_area.ToString(); // + num_area.ToString() + " ; WHERE Region.num_arearegion=@Areas_InRegion.ID_area
//com2.Parameters.AddWithValue("@Areas_InRegion.ID_area", num_area);
OleDbDataAdapter danum_area = new OleDbDataAdapter(com2.CommandText, connection);
DataTable tablenum_area = new DataTable();
//tablenum_area.DefaultView.RowFilter = "Areas_InRegion.ID_areas=" + num_area.ToString();
danum_area.Fill(tablenum_area);
cmbArea.DataSource = tablenum_area;
cmbArea.DisplayMember = "Names_OfAreas";
cmbArea.ValueMember = "Areas_InRegion.ID_areas";
//cmbArea.SelectedIndex = -1;
}
}
}
For this, it is necessary that there was another valuemeber as a foreign key [Region.num_areas].
Is it possible to have multiple valuemembers in every single combobox?
When I click on any of the comboboxes, other comboboxes should be filtered.
Please help me.
Thank you.
An house is located in a region (1-1 relationship), a Region can have one or more Areas (1-n relationship), an Area belongs to one and only one Region with these relationships you have your database schema wrong. You need something like this (because obviously an house could be located in only one Area)
1) House:
| ID_house | House_names | ID_region | ID_areas |
--------------------------------------------------
| int | names | int | int |
2) Region:
| ID_region | Nameofregions |
-----------------------------
| int | names |
3) Areas:
| ID_areas | Area_names | ID_region |
-------------------------------------
| int | names | int |
Now when you get the id for the house you have also the id for the region and the area.
private void cmbHouse_SelectionChangeCommitted(object sender, EventArgs e)
{
if (cmbHouse.SelectedIndex > -1)
{
// Each item in a combobox binded to a datatable is a DataRowView
// If we get this object we can access all the columns from that row
DataRowView rv = cmbHouse.SelectedItem as DataRowView;
// Extract the fk for the region and the area
int id_region = Convert.ToInt32(rv["ID_region"]);
int id_area = Convert.ToInt32(rv["ID_area"]);
// at startup you have already filled the combo with the regions,
// so there is no need to look again in the database for the region
// Just set the current selected value to the region
cmbRegion.SelectedValue = num_region;
// the same happens for the Areas combo. It is already filled with the
// areas, but here we should really have only the areas that belongs to
// the selected region not all the area to avoid problems
// (so remove the initial filling and do it only when the user choose an House)
// Query for all areas belonging to the selected region
string areaSql = @"SELECT * FROM Areas
WHERE ID_Region=@reg";
OleDbCommand com2 = new OleDbCommand(areaSql, connection);
com2.Parameters.AddWithValue("@reg", num_region);
OleDbDataAdapter danum_area = new OleDbDataAdapter(com2);
DataTable tablenum_area = new DataTable();
danum_area.Fill(tablenum_area);
// Always put the Datasource after the setting for DisplayMember and ValueMember
// to avoid performance drops and problems if there is a SelectedIndexChanged event
cmbArea.DisplayMember = "Names_OfAreas";
cmbArea.ValueMember = "ID_areas";
cmbArea.DataSource = tablenum_area;
// last step is setting the SelectedValue on the cmbArea to the house's area
cmbArea.SelectedValue = id_area;
}
}