过滤datagridview时，我在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.

推荐答案

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

这篇关于过滤datagridview时，我在comboboxcolumn中有重复的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！