Populate Master/Detail data in Combo Box

[rockronie]

Hello there,

I have a form with 3 combo boxes. The first combo box will populate the master records. if the user select any record from the first combo box the second combo box will populate the detail records.

Example Data:

Table: Building (Master)
Bldg_Code Bldg_Name

1----------Building Number 1
​

2 ---------Building Number 2

Table: Building_Floor (Detail)
Bldg_Code Bldg_FloorCode Bldg_FloorDesc
1---------------1---------Ground Floor
1---------------2---------First Floor
1---------------3---------Second Floor
2---------------1---------First Floor

Table: Building_FloorFlat (Detail)
Bldg_Code Bldg_FloorCode Bldg_FloorFlatCode Bldg_FloorFlatDesc
1---------------1----------------1----------Flat Number 1
1---------------1----------------2----------Flat Number 2
1---------------1----------------3----------Flat Number 3


I'm a beginner in vb.net, Hoping you can provide a source program solution.



Thanks,

Rock

 

[vis781]

Right, well i don't know about giving you a 'source program solution' but take these steps and get back to us if you get stuck.

Create the tables in whatever database you are using.
Create the connection string and test that it works.
Create a Dataset and put two datatables in it.
Fill your first table with the info from table buildings.
Set the datasource of combobox1 to datatable1 and set the value member of the combo to bldg_code
Make sure you can see the info in combobox1 when you run the app

Get that far and let us know if you have any problems and we'll be happy to help.

 

[rockronie]

vis781

thanks vis781 here is the code below for you to check:
-------------------------------------------------
Dim oConn As SqlConnection = DBConn()
Dim da_Bldg As New SqlDataAdapter("SELECT Bldg_Code,Bldg_Name FROM HSE_Building Where Shared_Bldg='Y' and RecStatus='A'", oConn)
Dim da_BldgFloor As New SqlDataAdapter("Select Bldg_Code,Bldg_Floor_No,Bldg_Floordesc From HSE_BldgFloor Where RecStatus='A' ", oConn)

If oConn.State = ConnectionState.Closed Then
oConn.Open()
End If
da_Bldg.Fill(ds_House, "Bldg")
da_BldgFloor.Fill(ds_House, "BldgFloor")

ds_House.Relations.Add(New DataRelation("RelationBldg_Floor", ds_House.Tables("Bldg").Columns("Bldg_Code"), ds_House.Tables("BldgFloor").Columns("Bldg_Code"), False))
CboBldg.DataSource = ds_House.Tables("Bldg")
CboFloor.DataSource = ds_House.Tables("BldgFloor")
CboBldg.DisplayMember = "Bldg_Name"
CboBldg.ValueMember = "Bldg_Code"
CboFloor.DisplayMember = "Bldg_FloorDesc"
CboFloor.ValueMember = "Bldg_Floor_No"

 

[vis781]

Does it work? What are you having trouble with?

 

[rockronie]

Is populating the data in the combo box. However, if I select any item in the combox box (cboBldg) it does not filter or populate the correct relation to the detail combo box (cboFloor)


Hoping somebody can provide information to correct the problem


Thanks

 

[jmcilhinney]

You should have all three DataTables in a DataSet and create DataRelations between them. You then assign the DataSet to the DataSource property of three BindingSource objects. You set the DataMember property of the first BindingSource to the name of the parent table; you set the DataMember property of the second BindingSource to the name of the DataRelation between the parent and child tables; and you set the DataMember property of the third BindingSource to the name of the DataRelation between the child and grandchild tables. Now you assign the BindingSources to the DataSource properties of the three ComboBoxes. That's it. Everything else is done for you. The relationships filter the data so only the correct child data is displayed in each ComboBox. Much of this can be done in the designer too, which makes it easier still.

 

[rockronie]

if you can provide a sample code will be greatly appreciated as I am new to VB.net.

 

[jmcilhinney]

I think that my instructions are fairly explicit, plus a great deal of the work should be done in the designer so I can't really show you the code for that. You might be best served by following a few tutorials to get the idea a bit at a time. There are several links in my signature.

 

[rockronie]

jmcilhinney

Thanks jmcilhinney


I followed your intructions and working fine now. I really appreciate so much
and more power to you