过滤datagridview时，我在comboboxcolumn中有重复的值 [英] When filtering a datagridview I have a duplicate values in comboboxcolumn
问题描述
我有一个列表，当dgv1被填充时，streetId和streetName被填充，在dgv2中我有一个用于streetName的组合框，所以我填充了它的街道，但我有一个cabName cmbxcolumn我需要根据第一列过滤它选择（streetName）.i有一个桌面摄像头：
i have a list that have streetId and streetName filled when dgv1 is filled,in dgv2 i have a comboboxcolumn for streetName so i populate the street in it ,but i have a cabName cmbxcolumn i need to filter it based to the 1st column selection(streetName).i have a table camera:
camIp cabCode streetId camType camCode
11.5 34 9 camtype1 PTZ
11.7 34 9 camtype2 C
12.1 19 10 camtype2 A
12.2 19 10 camtype3 B
12.3 19 10 camtype4 PTZ
和一个表cabCode：
and a table cabCode:
cabCode cabName
19 cabName1
34 cabName2
35 cabName3
我的尝试：
这里我尝试过：
我为相机创建了一个数据表：
DataTable CamerastblUsage = new DataTable（）;
SqlCommand cmd5 = cnn.CreateCommand（）;
cmd5.CommandText =SELECT cam.camIp，cam.cabCode，c.cabName，cam .streetId，cam.camType，cam.camCode来自camera cam，cab c where cam.cabCode = c.cabCode;
BindingSource unfilteredCamerasUsageBS = new BindingSource（）;
DataView undv2 = new DataView（CamerastblUsage）;
unfilteredCamerasUsageBS.DataSource = undv2;
Column23.DisplayMember =cabName;
Column23.ValueMember = camIp;
Column23.DataSource = unfilteredCamerasUsageBS;
//这个绑定源是我执行过滤后视图的地方
BindingSource filteredCamerasUsageBS = new BindingSource（）;
DataView dv6 = new DataView（CamerastblUsage）;
filteredCamerasUsageBS.DataSource = dv6;
然后我使用了cellbeginedit事件：
if（e.ColumnIndex == Column23.Index）
{
//过滤cabCode组合框基于在列索引0中选择的streetId
DataGridViewComboBoxCell dgcb =（DataGridViewComboBoxCell）dataGridView3 [e.ColumnIndex，e.RowIndex ];
dgcb.DataSource = filteredCamerasUsageBS;
this.filteredCamerasUsageBS.Filter =streetId =+
Convert.ToString（this.dataGridView3 [e.ColumnIndex - 1，e.RowIndex] .Value.ToString（））;
}
但是当选择streetName，示例为具有Id 10的streetName1时，过滤器在cabName组合框中给出三个相同的相同名称的相同的cabcode 19：
cabName3
cabName3
cabName3
i需要过滤它只显示一个名称我觉得过滤有错误，有没有人知道。
What I have tried:
Here what i've tried:
I created a datatable for camera :
DataTable CamerastblUsage = new DataTable();
SqlCommand cmd5 = cnn.CreateCommand();
cmd5.CommandText = "SELECT cam.camIp,cam.cabCode,c.cabName,cam.streetId ,cam.camType,cam.camCode from camera cam , cab c where cam.cabCode = c.cabCode";
BindingSource unfilteredCamerasUsageBS = new BindingSource();
DataView undv2 = new DataView(CamerastblUsage);
unfilteredCamerasUsageBS.DataSource = undv2;
Column23.DisplayMember = "cabName";
Column23.ValueMember = "camIp";
Column23.DataSource = unfilteredCamerasUsageBS;
// this binding source is where I perform my filtered view
BindingSource filteredCamerasUsageBS = new BindingSource();
DataView dv6 = new DataView(CamerastblUsage);
filteredCamerasUsageBS.DataSource = dv6;
then i used the cellbeginedit event:
if (e.ColumnIndex == Column23.Index )
{
//filter cabCode combobox based on streetId selected in column index 0
DataGridViewComboBoxCell dgcb = (DataGridViewComboBoxCell)dataGridView3[e.ColumnIndex, e.RowIndex];
dgcb.DataSource = filteredCamerasUsageBS;
this.filteredCamerasUsageBS.Filter = "streetId = " +
Convert.ToString(this.dataGridView3[e.ColumnIndex - 1, e.RowIndex].Value.ToString());
}
but when selecting a streetName,example streetName1 that have Id 10 ,the filter gives in the cabName comboboxcolumn three same names of same cabcode 19:
cabName3
cabName3
cabName3
i need to filter it displaying only one name i think there is a mistake in the filtering,does anybody have an idea.
推荐答案
嗨Ramy，
一个更好的方法 - 实际上每当你遇到这种情况时 - 你应该得到你的组合数据来自其来源的字段，然后过滤该数据。
假设您拥有您建议的结构，您将获得来自相机表的网格数据 - 因为你不会显示cabCode列，你通常也会包含文本数据cabName，查询应该是这样的;
Hi Ramy,
A better way of doing this - and indeed whenever you have this type of situation - you should get the data for your combo fields from their source and then filter that data.
Assume you have the structure you have advised, you would get your grid data from the camera table - since you would not display the cabCode column you would normally include the text data cabName as well, the query should look like;
SELECT
cam.camIp, cam.cabCode, cab.cabName, cam.streeetId, cam.camType, cam.camCode
FROM
camera cam
LEFT JOIN cabCode cab ON cam.cabCode = cab.cabCode
注意使用左连接 - 这可确保您将获得所有相机记录 - 包括那些没有cabCode设置的记录 - 如果您只想拥有cabCode，请使用内连接。
您还应该获得cabCode表的完整副本如下;
Note the use of a left join - this ensures that you will get all camera records - including those that do not have a cabCode set - use an inner join if you want only those that have a cabCode.
You should also get a complete copy of the cabCode table as follows;
SELECT cabCode, cabName FROM cabCode
当你想要过滤组合字段的cabCode数据，然后你可以通过cabCode字段过滤你的cabCode数据。
由于streetId和cabCode之间没有连接，你需要得到一个cabCode列表有效 - 通过streetId&过滤您的第一个数据表然后循环以获取适用的cabCode值，然后使用IN（id1，id2，id3）进行过滤
或者，您可以跳过获取cabCode表的完整副本，并在每次使用时重新查询数据库以下
When you want to filter your cabCode data for the combo field you would then filter your cabCode data by the cabCode field.
Since there is no connection between streetId and cabCode you need to get a list of the cabCode that are valid - either by filtering your first datatable by the streetId & then looping to get the applicable cabCode values, then filtering using IN(id1, id2, id3)
Alternatively you can skip getting a complete copy of the cabCode table and just re-query the db each time using the following
SELECT cabCode, cabName FROM cabCode WHERE cabCode IN
(SELECT cabCode FROM camera WHERE streetId = <selected street="" id="">)</selected>
还有其他方法可以执行此操作 - 例如，您可以使用GROUP BY获取唯一的cabCode列表， cabName& streetId值但如果您的相机表中没有记录或没有街道ID，这往往会失败。
亲切的问候
There are other methods of doing this - for instance you can use GROUP BY to get a list of unique cabCode, cabName & streetId values but this tends to fail if you have no records in your camera table or no street id.
Kind Regards
sry for the late但我改变了数据库中的几列，并且它工作正常，非常感谢
sry for the late but i changed a couple of columns in database and it worked correctly thanks a lot
这篇关于过滤datagridview时，我在comboboxcolumn中有重复的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
