[RESOLVED] Combobox and Parent columns

[Mike54]

I am using the following code which populates a combobox with Location Codes from a table called tb_StoreLocations.

Dim SQLStr As String
Dim dtTableData As New DataTable
Dim dbDataAdapter As SqlDataAdapter
SQLStr = ("Select Locationcode from tb_StoreLocations where StoreCode = '" & storekey & "'")
dbDataAdapter = New SqlDataAdapter(SQLStr, PubConnString)
dbDataAdapter.Fill(dtTableData)
ComboBox4.DataSource = dtTableData

What I would like to do is instead of displaying location Codes use another table called tb_locations to display the location names.

tb_locations consists of

ID
LocationCode
LocationName

I have created a relationship between these 2 tables with Locations being the parent but I don't know how to fill the combobox with the parent locationname rather than the locationcode

[jmcilhinney]

Query the database and populate a DataTable with whatever columns are appropriate, which probably means ID and LocationName, but maybe LocationCode too. Bind your ComboBox to the data the same way but, before setting the DataSource, set the DisplayMember to the name of the column you want to display and set the ValueMember to the name of the column you want to retrieve values from using the SelectedValue. That means that the user can select a name and then you can get the corresponding ID or code.

[Mike54]

I don't think this is answering the query I have or I am not fully understanding the reply. The latter is quite likely. Doing what I think has been suggested I get a full list of Location names rather than the required subset.

Perhaps I didn't explain in enough detail my requirement.

There are 3 tables
tb_Stores which contains a list of store names.
tb_locations which contains a list of locations.
tb_StoreLocations which contains a list of what stores are at which locations.

tb_Stores has 3 fields ID, StoreCode, StoreName
tb_Locations has 3 fields ID, LocationCode, LocationName
tb_StoreLocations has 3 fields StoreLocationCode, StoreCode, LocationCode


The user selects a StoreName from a combobox which lists all storenames stored in tb_Stores. I then use the selected StoreName to retreive all LocationCodes from tb_StoreLocations that have the corresponding StoreCode. i.e. I now have a list of LocationCodes where that store is located.
What I want to do is display that list in another combobox but instead of displaying the LocationCodes which mean nothing to the user I want to display the LocationNames as listed in tb_Locations.

I could just add the LocationName to the tb_StoreLocations but that would be defeating the object of trying to learn how to access data from other tables.

[jmcilhinney]

Yeah, you didn't really explain it properly the first time around. First things first, your database design appears to be less than optimal. Presumably the ID is the primary key for the Stores and Locations tables. In that case, the StoreLocations table should contain those two IDs, NOT the codes. It might also have it's own ID column but that would be unnecessary; the two IDs from the other tables would form a composite primary key.

[Mike54]

OK. The composite primary key will be very useful and I was unaware of that, the problem of learning as I go along, so I will amend my tables accordingly. That aside, I still need to know how to retreive the location names to my combobox.

[jmcilhinney]

You can just populate two DataTables in one DataSet: one with the Stores data and one with the results of another query that joins the Locations and the StoreLocations. Once you've got the two DataTables, follow the CodeBank link in my signature and check out my thread on Parent/Child Data-binding.

[Mike54]

I have now optimized my tables by removing the ID columns and the StoreLocationCode, so I have the following:
There are 3 tables
tb_Stores which contains a list of store names.
tb_locations which contains a list of locations.
tb_StoreLocations which contains a list of what stores are at which locations.

tb_Stores has 2 fields StoreCode, StoreName (StoreCode is the Primary Key)
tb_Locations has 2 fields LocationCode, LocationName (LocationCode is the Primary Key)
tb_StoreLocations has 2 fields StoreCode, LocationCode (StoreCode & LocationCode form a Composite Primary Key)

A parent/child relationship between tb_Locations & tb_StoreLocations has been set up already on LocationCode.

The code sample given by jmcilhinney seems to show the second combox being a child of the first, which is not what I need. I can select all the correct records into my combobox2 by using the code I have shown, all I need to do is change the LocationCode to a LocationName.

Can some one please explain in simple terms (preferably code) how I can acheive this otherwise I shall have to admit defeat and add the LocationName into the tb_StoreLocations table. I am obviously wrong but I would have thought with the relationship set up you would just be able to reference the parent field directly.

[jmcilhinney]

The code sample given by jmcilhinney seems to show the second combox being a child of the first, which is not what I need.

Yes it is

Can some one please explain in simple terms (preferably code) how I can acheive this

I already have. Populate two DataTables in a DataSet using two queries. The first query gets just the contents of the Stores table while the second joins StoreLocations and Locations. You then add the DataRelation between the StoreCode columns in each DataTable. Everything else is done just as I describe in the CodeBank.

[Mike54]

I have checked your code again and it sets up a table with Parent ID and Child name. That is what I need but my table has Parent ID and Child ID (ie Store code, Location code). To acheive the same I would need to add the Locationname into the storelocation table which is what I was trying to avoid doing.

[jmcilhinney]

How many times can I say the same thing? For the third time, your child DataTable is populated by a query that joins the StoreLocations and the Locations tables, therefore it has columns from both those tables, i.e. it contains the StoreCode (ParentID) column, the LocationCode (ChildID) column and the LocationName (ChildName) column. If you don't know how to write a query that joins two tables then don't just continually ignore the fact that I keep saying that but actually tell me that you don't know how to do it. If you do know how to do it then just go ahead and do it.

[Mike54]

Sorry, I was trying to understand what you were doing by trying to understand the code. You're right I dont know how to write a query that joins two tables so I would appreciate it if you showed me.

[jmcilhinney]

E.g.

SELECT Table1.Column1, Table2.Column2
FROM Table1 INNER JOIN Table2
ON Table1.PrimaryKey = Table2.ForeignKey

[Mike54]

That solved the problem. Thank you.

Found the following link which explained this command very clearly.

http://www.w3schools.com/sql/sql_join_inner.asp