c# - 如何根据 1 个组合框中的选择过滤 2 个组合框?
问题描述
我有 3 张桌子:
1)房子:
| ID_house | House_names | num_region |
---------------------------------------
| int | names | int |
num_region - 外键,相当于 Region 表中的主键(Region.ID_region)。
2) 地区:
| ID_region | Nameofregions | num_arearegion |
---------------------------------------------
| int | names | int |
num_arearegion - 外键,等于 Areas_InRegion 表中的主键(Areas_InRegion.ID_areas)。
3)Areas_InRegion:
| ID_areas | Area_names |
------------------------
| int | names |
在表格中,我有 3 个组合框:
1) cmbHouse - 用于显示 House 表中的房屋名称。
2) cmbRegion - 用于显示 Region 表中的区域名称。
3) cmbArea - 用于显示 Areas_InRegion 表中区域的名称。
我填充这样的组合框:
//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;
组合框 cmbRegion 具有 valuemember 作为主键。
我可以通过主键将 cmbHouse 组合框过滤为 cmbRegion 组合中的 valuemeber,但我无法过滤 cmbArea 组合框。
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;
}
}
}
为此,有必要有另一个 valuemeber 作为外键 [Region.num_areas]。
是否可以在每个组合框中有多个值成员?
当我单击任何组合框时,应过滤其他组合框。
请帮我。
谢谢你。
解决方案
房子位于一个区域(1-1 关系),一个区域可以有一个或多个区域(1-n 关系),一个区域属于一个且只有一个区域,这些关系是您的数据库架构错误。你需要这样的东西(因为显然一所房子只能位于一个区域)
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 |
现在,当您获得房屋的 id 时,您还获得了地区和区域的 id。
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;
}
}
推荐阅读
- selenium - AM io.appium.java_client.remote.AppiumCommandExecutor$1 lambda$0
- python - 熊猫:关于如何在熊猫数据框中加载数据的任何想法
- python - 可能是 PySide2、QCamera、QAbstractVideoSurface 上的错误
- python - 如何在 Jupyter 实验室中使用 Mac 命令键
- c# - 无法在 VS 2019 C# 中显示包含 Windows Media Player 对象的表单的设计器视图
- python - 以 10 分钟间隔聚合 python Spark DataFrame 的行
- tensorflow - 如何在 tensorflow 1 的循环内加速 keras 模型预测?
- javascript - React FullCalendar - 无法在日期选择上返回自定义 JSX
- arrays - 这是我在函数中掷骰子的想法,好吗?
- c - 将复合文字传递给 _Generic 不起作用