Click here to Skip to main content
15,662,782 members
Please Sign up or sign in to vote.
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:
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:
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:
i need to filter it displaying only one name i think there is a mistake in the filtering,does anybody have an idea.
Updated 14-Jun-16 12:45pm

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;
    cam.camIp, cam.cabCode, cab.cabName, cam.streeetId, cam.camType, cam.camCode
    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;
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
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
Share this answer
ramy nemer 13-Jun-16 18:58pm    
the last query how should i put it in code
an0ther1 13-Jun-16 19:41pm    
Create a new Datatable, new SQLCommand & new CommandText & query the DB in the cellbeginediting event.
This is not an efficient method of solving your problem as everytime the cellbeginedit event occurs you will re-query the DB.

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

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900