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

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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