When filtering a datagridview I have a duplicate values in comboboxcolumn

Question

1.00/5 (1 vote)

See more:

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:

SQL

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

and a table cabCode:

SQL

cabCode  cabName
19      cabName1
34      cabName2
35      cabName3

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.

Posted 13-Jun-16 11:33am

ramy nemer

Updated 14-Jun-16 12:45pm

v2

Add a Solution

2 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

an0ther1 · Answer 1 · 2016-06-13T12:41:00

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;

SQL

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

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;

SQL

SELECT cabCode, cabName FROM 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

SQL

SELECT cabCode, cabName FROM cabCode WHERE cabCode IN
(SELECT cabCode FROM camera WHERE streetId = <selected street="" id="">)</selected>

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

ramy nemer · Answer 2 · 2016-06-14T12:45:00

Solution 2

sry for the late but i changed a couple of columns in database and it worked correctly thanks a lot

Posted 14-Jun-16 12:45pm

ramy nemer

When filtering a datagridview I have a duplicate values in comboboxcolumn

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

When filtering a datagridview I have a duplicate values in comboboxcolumn

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0

Existing Members

...or Join us