adding items to a list box using a dataset

[steve_rm]

Hello,

I have a 1 table with a list of departments, and another table with a list of employees who work in those departments.

What I want to do is select a department from a combo box, and display all the employees who work in that department in a list box.

I am using a dataset and created the data relationship between the 2 tables. My code is as follows:

OleDbCommand cmd = cnn.CreateCommand();
cmd.CommandType = CommandType.Text;
cmd.CommandText = "SELECT * FROM Employee";
da.SelectCommand = cmd;
da.FillSchema(ds,SchemaType.Source,"Employee");
da.Fill(ds,"Employee");

cmd.CommandText = "SELECT * FROM Department";
da.SelectCommand = cmd;
da.FillSchema(ds,SchemaType.Source,"Department");
da.Fill(ds,"Department");

//Create the relationship for the employee and department tables
DataColumn parentColumn = ds.Tables["Department"].Columns["DepartmentCode"];
DataColumn childColumn = ds.Tables["Employee"].Columns["DepartmentID"];

ds.Relations.Clear();
DataRelation drEmployees = new DataRelation("EmployeeDetails",parentColumn,childColumn);
ds.Relations.Add(drEmployees);

//Use a for loop to add all the employees into a list box - have problem with this part
foreach employee in departments
Add to the list box

Many thanks in advance,

Steve

[mar_zim]

why not use left outer join?

'in you form load
Dataset ds=new dataset();
ds.tables.add(new datatable("dt"));
'cn is a connection
'assuming the department code are bind to your combobox
'put this code to your combobox selected index changed
oledbdataadapter da = new oledbdataadapter("select * from employee e left outer join departments d on e.departmentid='" & combobox1.text & "'",cn);
da.fill(ds.tables[0]);
foreach(datarow dr in ds.tables[0].rows){
listbox1.items.add(dr[0].tostring());<---it fills the first column data to your listbox
}

[jmcilhinney]

If you want the Employees in the second list to be updated when the selection in the Department list is changed you should bind your Employee control to a DataView of the Employee table. You then use the RowFilter of the DataView to display only the Employee rows that match the selected Department. To make this easier you could use one of two options.

1. Use a join to include the Department name in each Employee row. Your RowFilter property could then use the SelectedItem of the Department ComboBox.
2. Bind the Department ComboBox to the Department table with a DisplayMember of the Department name and a ValueMember of the Department ID. Your RowFilter property could then use the SelectedValue of the Department ComboBox.

[steve_rm]

Hello,

Thanks for your reply.

I was hopping to do all this by using the data relations. As l am learning how to use them.

Steve

[jmcilhinney]

I would think that changing a single property to update your dependent list would be preferable to using a loop any day. DataRelations have other uses but they don't really help you here.

[steve_rm]

Hello,

Thanks for all your replys. I have finished the coding and now the program works the way l want it to work.

Please tell me if there is something that you can add to this, maybe there is a better and more efficient way to do it.

thanks in advance,

Steve

//Find the selected department to search for employees in
			DataRow[] SelectDepartment = ds.Tables["Department"].Select("DepartmentName =  '" + cboEmployeeDepartments.Text + "'");			
			//Obtain all the employees working in this department
			DataRow[] EmployeesRows = SelectDepartment[0].GetChildRows("EmployeeDetails");
			//Loop through all the employees and add them to the list book
			for ( int i = 0; i < EmployeesRows.Length; i++ )
			{
				lstEmployeeInDepartments.Items.Add(EmployeesRows[i]["FirstName"]);
			}