[2005] Data Grid Just Wont Work

Hello all. Here is my delema. I have two tables from an Access database where Bold indicates the primary key:

Categories (CatID, CatDesc), and ...

Items (ItemID, CatID, categories, name, description, date, cost).

I am trying to set up a Master-Detail relationship with these two tables. I want to design a listbox that will list the categories of the first table, and a datagrid that will display only the items related to the selection in the list box. For example, the list box CatDesc field contains: cars, tools, toys. When I click cars in the list box, I would like only the cars items to display in the datagrid.

I've been reading and trying to figure this out all night but I cant. Please, if anyone can help me out that would be great. So far, I know how to create a connection to my external Access database for this project and how to add it to the Data Source tab but thas all. So if you decide to help me, please keep in mind that I am very, very limited. So if you say to add a dataset, I am totally lost already.

Thanks all,
Scott

I just expalined how to do this earlier today with two DataGridViews. Check out this thread for an example. Now, because you are displaying your parent records in a ListBox instead of a DataGridView this part:

vb.net Code:

Me.BindingSource1.DataSource = data.Tables("Parent")
 
    Me.DataGridView1.DataSource = Me.BindingSource1
 
    Me.DataGridView2.DataMember = "ParentChild"
    Me.DataGridView2.DataSource = Me.BindingSource1

would become this:

vb.net Code:

Me.BindingSource1.DataSource = data.Tables("Parent")
 
    Me.ListBox1.ValueMember = "ID"
    Me.ListBox1.DisplayMember = "Name"
    Me.ListBox1.DataSource = Me.BindingSource1
 
    Me.DataGridView2.DataMember = "ParentChild"
    Me.DataGridView2.DataSource = Me.BindingSource1

Now, to set up the realtion and the binding using your table and column names:

vb.net Code:

Dim relation As New DataRelation("CategoriesItems", _
                                     parentTable.Columns("CatID"), _
                                     childTable.Columns("CatID"))
 
    data.Relations.Add(relation)
 
    Me.BindingSource1.DataSource = data.Tables("Categories")
 
    Me.ListBox1.ValueMember = "CatID"
    Me.ListBox1.DisplayMember = "CatDesc"
    Me.ListBox1.DataSource = Me.BindingSource1
 
    Me.DataGridView2.DataMember = "CategoriesItems"
    Me.DataGridView2.DataSource = Me.BindingSource1

If you have created a typed DataSet then all of this can be set up in the designer with no code at all. If that's the case then let me know and I'll give you the instructions.

Hey man, thanks for your help. I actually read that post but I was a lil confused. So I did some more reading and so far this is what I've done:

-Started a new Windows project

-Used Server Explorer and created a new data connection

-Used Data Sources, added the new connection, and added the two tables

-Used Data Sources to add Categories table as ListBox and Items table as DataGrid (Not DataGrid View)

- 5 Things were created below the form: dataset, tblcategoryAssetBindingSource, tblItemsAssetBindingSource, tblCategorytblAdapter, tblItemstblAdapter

...At this point now, I'm stuck. Any hints? BTW, instructions via design mode would be preferred.

Thanks,
Scott

I think you will find that the DataSource property of the tblItemsAssetBindingSource will be set to the DataSet and the DataMember will be set to the tblItemstbl. If you change the DataSource to tblcategoryAssetBindingSource and the DataMember to the relationship between the tables it should work.

Quote:

---

I think you will find that the DataSource property of the tblItemsAssetBindingSource will be set to the DataSet and the DataMember will be set to the tblItemstbl.

---

Ok you're totally correct and I made the first set of changes. I havent created a relationship yet though because I dont know how to configure the selected fields...this is what I figure:

-Parent Table (tblCategories)
-Child Table (tblItems)
-Key and FKey Colums (CatID)
-Relation Only?
-OK

Thanks,
Scott

You shouldn't have to create a DataRelation in your VB app. If the tables are related then you should have created a relationship in the database. The Data Source wizard will then create the DataRelation automatically, just as it creates the DataTables automatically.

I suggest that you go back to your database and create the relationship, then reconfigure your Data Source. You do that by selecting it in the Data Sources window and pressing the Configure button. You should find that a corresponding DataRelation has been added to your DataSet.

Quote:

---

You shouldn't have to create a DataRelation in your VB app. If the tables are related then you should have created a relationship in the database. The

---

That worked perfectly. Thanks! Everything is working now. That sad part is that I dont understand how we got it to work. The book I have is a microsoft book about ado.net but it does very poorly at explaining things. Well I guess I'll keep trying to read up. Unless you'd like to explain to me how this works :D

Thanks,
Scott

The DataSource property refers to the source of the data that will be displayed in a control. The DataMember is the member of that data source that will determine what data from the data source will be displayed. This part:

vb.net Code:

Me.BindingSource1.DataSource = data.Tables("Categories")

says that the source of the data exposed by BindingSource1 is the "Categories" table in the DataSet. There is no DataMember so all the data from that table will be exposed. That could also be written like this:

vb.net Code:

Me.BindingSource1.DataMember = "Categories"
Me.BindingSource1.DataSource = data

That says that the source of the data is the DataSet but only data from the "Categories" member will be exposed. The DataSet class interprets a member as meaning a DataTable. Now that's exactly what you have done in the designer: you've selected the DataSet as the DataSource and the Categories table as the DataMember for the first BindingSource. That now means that that BindingSource will expose that data to any controls it is bound to.

This part:

vb.net Code:

Me.ListBox1.ValueMember = "CatID"
Me.ListBox1.DisplayMember = "CatDesc"
Me.ListBox1.DataSource = Me.BindingSource1

binds that BindingSource to the ListBox, thus the ListBox will display the data from the DataTable that is exposed by the BindingSource. It also says that the ListBox should diaply the data from the "CatDesc" column and the ListBox's SelectedValue property should return the value from the "CatID" column that corresponds to the selected record.

In your case you also have a second BindingSource and your DataGridView is bound to that, thus it will display whatever data is exposed by that BindingSource. You have selected the first BindingSource as the DataSource for the second BindingSource and a DataRelation as the DataMember. When you do that the BindingSource performs a little bit of magic. The Current property of the first BindingSource refers to the same record as the user selects in the ListBox. The second BindingSource uses the DataRelation to get all the child rows for that parent row and exposes that data, thus that's what gets displayed in the grid bound to it.

The scenario outlined in this thread uses a ListBox and a DataGrid.

If I was to use two ListBox controls (second ListBox instead of DataGrid), is there anyway I can bind the second ListBox to a DataRelation so that it shows only the children of the item selected in the first ListBox ?

If, as I guess, the answer is no, what would be the best alternative to link the items in the two ListBox controls ?

Thank you.