Relational Data, I'm Stuck!

[weismana81]

I've been working with vb.net for a little while now, and I've got an ok grasp on things, but I cannot seem to figure out how to manage relational data between tables in an ms access database. I'm sure this is too much to cover in one post, but does anyone know of an article, or an example somewhere that would help? I'd really like to be able to use the visual tools in vb.net, and I've seen familiar things, but I can't figure out how to get things working.

If it helps, all I'm trying to do (for now) is establish a "category-like" setup. For example, "items" is the main table and contains a field for "category". The categories have their own table so the user can set his/her own. Like in access, while the category field in the items table is an ID, at runtime it should display the appropriate category. Idealy the end result will, among other things, let the user filter by category. My problem is that I end up with a number (catID) instead of the category itself. But if I use the category field from the categories table, then the data bound objects from the items table do not respond. While there may be work-arounds for this, what about when the user is adding a new item. I bind the combo box to the catID field in the items table and get a number. But then if I bind it to the category field in the categories table, it doesn't save to the items table.

So that's my problem and like I said, I'm hitting a wall!!! Any direction from anyone is VERY MUCH APPRECIATED!!

 

[jmcilhinney]

Retrieve your Items table and your Categories table from the database. Assuming that your Categories table has an ID column and a Name column, set the DataSource of the Categories ComboBox to the Categories DataTable and the DataMember to the Name field. This means that each Item of the ComboBox is a row in the table and the list displays the Name field. In the SelectedIndexChanged event handler of the Category ComboBox, populate your Item Controls according to the ID field of the SelectedItem, which can be accessed like this:

categoryCombo.SelectedItem("ID")

When bound to a table, the Items property of a ComboBox returns a DataView, which behaves similarly to a DataRowCollection. The Item property, or the SelectedItem property, returns a DataRowView, which behaves similarly to a DataRow. Each individual field of a DataRowView can be accessed by index or name, just like a DataRow.

 

[weismana81]

Ok, perhaps a silly question here, but this is what I'm trying...

TextBox6.Text = ComboBox1.SelectedIndex("catID")

TextBox6 bound to the "title" field of the "items" table, ComboBox1 bound to the "CategoryName" field of the "Categories" table, and "catID" is the ID field of the Categories table. Although "catID" is located in both the "items" and "Categories" tables (created the relation in access).

I'm getting an error, and I'm sure you know why it's wrong, but my question is, am I even close?
Here is the error...
'Public Overrides Property SelectedIndex() As Integer' has no parameters and its return type cannot be indexed.
Am I not giving the SelectedIndex the parameter of the "catID" field?


Thank you so much for the reply. I'm really stressin here, and I really appreciate your help!

 

[jmcilhinney]

ComboBox1.SelectedIndex returns an Integer that represents the index of the selected item in the drop down list. You want to use ComboBox1.SelectedItem, which returns the row from the table as a DataRowView. ComboBox1.SelectedItem("catID") will get the ID from the selected row.

To display the CategoryName in the Item record rather than the catID, I think you'll have to assign the catID directly to the relevant field in the DataRow, then assign the CategoryName selected in the ComboBox to TextBox6, which I'm assuming is meant to display the category of the Item.

 

[jmcilhinney]

I re-read my previous post and thought it might be a bit confusing, so let me be explicit.

Assumptions:

• The database contains a Category table, which has a CategoryID field as primary key and a CategoryName field.
• The database contains an Item table, which has an ItemID field as primary key, an ItemName field and a CategoryID field which is a foreign key from the Category table.
• The ItemName field is bound to a TextBox called itemNameText.
• The CategoryName field is bound to a TextBox called categoryNameText.
• The Category table is bound to a ComboBox called categoryCombo.
• The item record is stored in a DataRow called itemRow.

You would retrieve a single Item record with the following SQL:

SELECT Item.*, Category.CategoryName FROM Item INNER JOIN Category ON Item.CategoryID = Category.CategoryID WHERE Item.ItemID = <item ID>

The SelectedIndexChanged event handler for categoryCombo will look something like this:

[color=Blue]Private Sub[/color] categoryCombo_SelectedIndexChanged(...) [color=Blue]Handles[/color] categoryCombo.SelectedIndexChanged
	[color=Green]'Update the category ID of the item.[/color]
  	itemRow.BeginEdit()
	itemRow("CategoryID") = Me.categoryCombo.SelectedItem("CategoryID")
	itemRow.EndEdit()

 	[color=Green]'Display the new category name of the item.[/color]
	categoryNameText.Text = Me.categoryCombo.SelectedItem("CategoryName")[color=Blue]
End Sub[/color]

You would update the Item record with the following parameterised SQL:

UPDATE Item SET ItemName = ?, CategoryID = ? WHERE ItemID = ?

 

[weismana81]

Now we're getting somewhere!!! I'm think I'm getting it now.

Thanks so much for time. I really really really appreciate it!!!!