Update DataSource when item added to combobox

[keb1965]

I have a combo box that has been filled with a static table from the database, what I would like to do is allow the user to edit the value in the combobox, (i.e. add something that isn't already in the list) and have that value saved to the table.

When the application loads or a new query is performed, it binds the combobox to the dataset field holding the individual item the user selected for that record:

With cbArtist
    .DataBindings.Clear()
    .DataBindings.Add(New Binding("text", bindData, "Artist"))
End With

I also set the list for the user to select from

Try
    Dim ds As DataSet = GetStaticData("Artists" ,"ID")
    With .cbArtist
        .DataSource = ds.Tables("Artists")
        .DisplayMember = "Artist"
        .SelectedIndex = 0
    End With
Catch ex As Exception
     MsgBox("Cannot load default artists list", MsgBoxStyle.Information)
End Try

Now if the artist isn't in the default list, the user can add their own. What I would like to do is put that new item that didn't previously exist into the artists table.

Should I create a new dataset and add the new value to it and update the table, or is there a way I can automatically update the datasource when the combo box list changes?

 

[keb1965]

The data source will have at maximum 200 items.

I'm not sure I understand everything you said, so let me tell you what the data looks like so you can tell me if your solution will work. I have about 100 rows (expected to grow to about 200 at peak) with 3 columns, the first column is the autonumber seed so I don't need that, the second column is the item (artist) that will be displayed in the combobox, the third column is "genre". The idea is that when the user selects a "genre" in one combobox, the associated "artists" that fit into that genre are filtered into the combobox.

I am beginning to think I need to do multiple queries and create seperate datasets for each genre and when the genre is changed, the dataset for that genre of artists is added to the combobox to replace the previous dataset. Would that be correct or can I filter the data in one column, by the data in a second column using the filter you show above.

I appreciate you taking the time to lend a hand .. perhaps when I become more familiar with data handling, I'll be able to help someone else.

 

[cjard]

The data source will have at maximum 200 items.

I'm not sure I understand everything you said, so let me tell you what the data looks like so you can tell me if your solution will work. I have about 100 rows (expected to grow to about 200 at peak) with 3 columns, the first column is the autonumber seed so I don't need that, the second column is the item (artist) that will be displayed in the combobox, the third column is "genre". The idea is that when the user selects a "genre" in one combobox, the associated "artists" that fit into that genre are filtered into the combobox.

Er.. You want to give me a list of 10 genres, and when I choose one, you'll add 20 artists from that genre into the same combo, under the heading of the genre I just picked?

Do you know how annoying that is going to be? What if i'm a user who uses keyboard to navigate a combo, and I down-arrow scroll through the whole thing (not having the dropdown popped open)
Your code will end up adding artists for every genre and leaving me scrolling 200 items.

I'd uninstall a program like this

I think youre flying in the face of conventional HCI too much; the accepted norm would be to have one combo for genre, and then chosen, another combo fills with artists of that genre or to have no combos at all and use a tree instead

 

[keb1965]

you misunderstand ... the comboboxes (or textboxes with autocomplete) will work as filters. There are 3 of these controls. For now it is unimportant what type of control it will end up being, it is only important how they operate ... so that they prevent the exact scenario you just mentioned. Lets examine just a single level filter then (using 2 distinct controls)

Lets say we have 200 items in the database, but you don't need all 200 at any one time. These items are broken down into several categories. The top category "genre" has about 6 items, each of those 6 items has 30 +/- items "artists" that fit into that category. When the user selects a single item (or fills in the box) with a "genre", the adjacent control defaults to a filtered list that contains only those "artists" whose music falls within that category. This way, you aren't searching through hundreds of artists whose music you have no desire to find.

Now we can add a third control (again type is unimportant), that will list only the music associated with the "artist" selected in the second control.

So the flow is:
genre has 6 items, artists has 200 items, music has 10,000 items.
User selects 1 item in genre control, artists control is pared down to 30 +/- artists (based on value of genre), music control is pared down to 2000 +/- songs (based on value of genre).

User selects 1 item in artist control, genre control remains unchanged, music control is pared down to 60 +/- songs.

Finally the user selects a song from the list.

Think of it as finding a word in the dictionary. The word starts with M, so you find all the words that start with M, the second letter is O, so you find all the words that start with MO, the third letter is N, so you find all the words that start with MON, finally you have a small enough list that it is simple to find the word MONDAY. The concept is exactly the same.

 

[cjard]

Cascading comboboxes is what you're looking for. Many examples exist here, but I'd typically advocate using the database to do the searching. Each combo has a bindingsource, and a datatable. When the genre positionchanged fires on the bindingsource, retrieve the current value, and runa query filling the artists in that genre from the db. Note that because artistscombo -> artistsbindingsource -> artistsdatatable, you simply need to tell your tableadapter to FillArtistsByGenre("Rock", artistsdatatable)

See the DW2 link in my sign, "Creating a Form to Search Data"

 

[keb1965]

hmmm cascading comboboxes ... I hadn't heard that term before, but it makes perfect sense ... I'll see what I can dig up and take your advice.

Thanks again ...

 

[keb1965]

Ok, all is right with the world, although I had to change the DataSource to a DataView on my comboboxes ..

In the SelectedIndexChanged event for each of the combo boxes affected, I placed the following code in place (adjusted for each combobox of course)

Dim dv As DataView = CType(cbGenre.DataSource, DataView)
dv.RowFilter = "Genre = " & "'" & cbGenre.Text & "'"

Who would have thought it would be so simple ... and here I was thinking I would have to rebuild the dataset and perform queries on the fly.

 

[cjard]

It's even simpler than that if you use DataRelations