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?

 

[JohnH]

This is incorrect, they are not identical

Then why would you modify a datatable that is not bound to the combo?

and the former is not even a valid statement.

It is using late binding code, but otherwise as valid as type casting it to whatever data source this combo has. If the combo is bound to a DataTable then that is what DataSource returns, if it's bound to a DataSet you would of course need to get the correct table first, ie combo.DataSource.Tables(combo.DisplayMember).Rows.Add

 

[Tom]

Nope still is invalid

 

[JohnH]

Nope still is invalid

What do you mean and how so?

 

[Tom]

 cbArtist.DataSource.Rows.Add(rowNew)

I mean it will not work, there is no such statement; you can not add anything to a combobox in this manner....

 

[JohnH]

 cbArtist.DataSource.Rows.Add(rowNew)

I mean it will not work, there is no such statement; you can not add anything to a combobox in this manner....

Why not?? If DataSource is a DataTable then this statement is 100% identical to aDataTable.Rows.Add. How can you claim otherwise? Is it late-binding code that confuses you? This is default setting for VB with Option Strict off, with Strict on it would only require a CType/DirectCast from DataSource to DataTable type to do the same.
Late-binding code is like writing ds.Tables("Artist") instead of StrongDataSet.ArtistTable if this is easier for you to relate to.

 

[Tom]

Its nothing thats confusing me, I know it wont work. There is no way that statement will add anything to a combobox. If ya think otherwise then compile a working example.

Second if ya need to turn Option Strict Off in order to force anything to work, that in itself should be an indication of a problem.

 

[JohnH]

Second if ya need to turn Option Strict Off in order to force anything to work, that in itself should be an indication of a problem.

You're misunderstanding the purpose of Option Strict, it is something you can turn ON to let the compiler give you better help in writing type safe code, it is not something that will make erroneous code right.

Dim o As Object = New DataTable
Dim dt As DataTable = o

Would you claim that o variable here does not contain a DataTable object just because option strict won't allow it to be assigned the dt variable?

Dim o as Object = New DataTable
Dim dt As DataTable = CType(o, DataTable)

These two examples has same valid code, they are also completely analogous to the DataSource property we're discussing. The DataSource is type Object and it is assigned the source of data that can be for example a DataSet/DataTable. Since these are reference type objects you can retrieve the same object back from the property any time.

 

[Tom]

I understand what Option Strict does just fine; just like I understand that the statement wont add anything to a combobox.

 

[JohnH]

If ya think otherwise then compile a working example.

intialize a combo:

Dim dt As New DataTable
dt.Columns.Add("test")
dt.Rows.Add("item 1")
Me.ComboBox1.DataSource = dt
Me.ComboBox1.DisplayMember = "test"

in a button click do this:

CType(Me.ComboBox1.DataSource, DataTable).Rows.Add("item 2")

As I have explained this last code line is the same as this:

Me.ComboBox1.DataSource.Rows.Add("item 2")

So you can choose either one of them depending on your Strict setting, they both do exactly the same, that is adding an item to the data source, which will display in combo right away.

I have to admit this I posted earlier "combo.DataSource.Tables(combo.DisplayMember).Rows.Add" is not 100% accurate, I was thinking about the data grids, the DisplayMember here if source is DataSet will of course include the column name, but you should see how it relate: combo.DataSource.Tables("tablename").Rows.Add

 

[Tom]

This is not a working example and you can not add tables or rows into a combobox's datasource property.

 

[JohnH]

It is a working sample, will you quit your nonsense!?

 

[cjard]

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?

For this, I probably WOULDNT have used a combo at att, but instead used a TextBox with its AutoComplete options set. The data source of the autocomplete would of course be list of artists.

You can run into serious headaches when using combos in some of these situations where you basically just want it as a suggester rather than a limiting list of options to pick from

Change this
cbArtist.DataSource.Rows.Add(rowNew)

To this
ds.Tables("Artists").Rows.Add(rowNew)

Tom (if you are now reading this) - there would be no need to change the code in the way you have advocated here.

cbArtist.DataSource and ds.Tables("Artists") are object reference pointers that point to the same object in memory, namely the datatable supplying the list of artists, ergo any operations that call for addition of rows to that datatable will reflect in the combo regardless of how the reference to the datatable was gotten.

Option Strict On would have prevented the use of cbArtist.DataSource (which we all know is a datatable) as though it were a datatable. If Option Strict were On, then the code would have to be:
DirectCast(cbArtist.DataSource, DataTable).Rows.Add....

But it has no influence on the behaviour of the datatable when adding rows

This is not a working example and you can not add tables or rows into a combobox's datasource property.

You can if the datasource is pointing to a dataset/datatable respectively.

As an alternate perspective on what John is saying, try this:

Dim c as Control = New TextBox
c.Text = "Hello world" 'works with Option Strict On or Off
c.MaxLength = 6 'works with Option Strict Off only
DirectCast(c, TextBox).MaxLength = 6 'works with Option Strict On or Off

MaxLength is specific to textbox, so either the object must be explicitly cast to a textbox, or Option Strict must be deactivated, so that VB will "perform the cast for us implicitly" - whatever voodoo it performs to do this, I don't know, but with it off it is possible without casting.

ALl John is trying to put across is that it is possible to use the datasource of the combo as if it were a datatable, because in this context IT IS a datatable. Option Strict is merely controlling whether casting is needed..

 

[cjard]

The plain and simple truth is the original statement will not work PERIOD.

I genuinely disagree, for the same reason that I could write this into a VB IDE, Option Strict OFF, and it'd compile and work fine:

Dim dt as New DataTable
Dim o as Object

o = dt

MessageBox.Show(o.Rows.Count) 'shows 0

If Option Strict was On, i'd have to cast:

MessageBox.Show(DirectCast(o, DataTable).Rows.Count)

Now alter that, play semantics, keep changing the coding until it does, remove my posts, ban me; everything but actually put that un-altered code statement into a program and get it to actually work!

You know, I do occasionally find myself arguing with others (what me? no! who would have thought? ) but when it's as simple and provable as this I will quickly flip into VB, start a new project and make darn sure of my point before I hammer someone into the ground with it.. The fact that (when the debate's over) I usually wondered why I bothered..
..but back to the point; you're correct, but only in limited circumstances, so you shouldnt have a pop without robusting up your point by considering all the other possible contexts.

Now, I did point out to the admins that banning you for something like this is perhaps a little harsh, and that were I an admin, I'd have let it slide, possibly locking the topic because it's not really contributing to Keb's question now, but you have to remember that everyone is human and humans have bad days.
There are guys over on codeguru that I want to ban even though they've done nothing technically wrong against the AUP; they just suck the life out the place by asking inane questions, leading helpers on goose chases and never answering other people's questions.. So have a care to the human element of it; pick your debate partners carefully, and watch out for those who walk softly and carry big sticks.

Is this my punishment that I can’t waste any more of my time helping others here on your message board?

It's more that the admins of any particular community have certain ideas about how they would like that community to be, feel, appear etc and have chosen the people to ensure it remains thus carefully, such that those people can use their judgement. As noted, it's not a route I would have taken were it open to me; as the discussion was adding nothing further to the original topic I would have taken it to PM and if you'd really got up my nose then I'd have put you on ignore. It's rare that my patience can be tested thus; I've only ever put one person on ignore in any forum in my life.

So to the others that see this (before this post is removed too) you can see how you are not allowed to have your own opinions here on this board.....

Your own opinions are fine, I have my opinions and definitely support the notion that "Everyone is entitled to my opinions" but you have to be careful how you present them in terms of jarring with the ethos of the community; Neal and the others want to create a community that is more or less "One question, one topic, don't fight, definitely don't fight with moderators and don't engage in senseless debate that would put newbies off the place"

Shoot just take a look at there post counts of thousands compared to there rep points of around 10% and see that 90% of what they post isnt actually helpful to anyone else here.

Rep came along long after post count; personally, my rep to postcount ratio is quite low (1:10) yet I don't feel that 90% of my posts are (that) trash. I wouldn't get hung up on it though - many people don't even return to say thanks but that's just the way of things. You at least know that your intentions were good and your advice was the best you could give. I do think on this particular point John was making that there was some talking at crossed purposes, and that you couldn't see the point he was making. I'm not taking sides; just offering my perspective. He's very even metered, level headed and knowledgeable. If I ever disagree with him, I usually quietly double check my facts, cause he's more often right than wrong

 

[keb1965]

For this, I probably WOULDNT have used a combo at att, but instead used a TextBox with its AutoComplete options set. The data source of the autocomplete would of course be list of artists.

You can run into serious headaches when using combos in some of these situations where you basically just want it as a suggester rather than a limiting list of options to pick from

Thanks for the thought. I understand the issues with using combo boxes, but as with so many other things in life, seldom do we get to choose our poison. I'll have to argue this one though, it seems to make alot of sense.

This is the reason I wanted to make the datasource update when an item was added that wasn't in the list. But as with all things, there are other caveats that must be handled, even with something as simple as filling out a stinking form with a couple of names and checking a few checkboxes.

Which brings me to the problem that cropped up yesterday. On my form, there are 4 fields that must be filled in. As the fields are filled in, the options in the combobox (or textbox if I end up changing the combobox) need to be filtered by a second column in the datasource. I could query the database each time the fields update, but then for the most part, I think I would be having lots of data pass back and forth that isn't necessary. I guess something like a conditional fill from the dataset (which I suspect is not possible). Either way, it isn't a huge deal, it is just more code and more time in my hands. Is it possible to change the datasource dynamically? That might be a solution.

Incidently, I did try my original code without explicitly modifying the datasource and it works either way.

 

[cjard]

Is it possible to change the datasource dynamically?

If you have your combo bound through some kind of IBindingList (it's kinda hard not to) to its underlying data list then you can set the .Filter of the source to something appropriate.
Of course, too, you'll be following good HCI guidelines and not loading more than.. 40 items into a comboi (max 20 is recommended) so there is negligible expense in doing:

Public Sub whatever(...) Handles WhateverTextBox.TextChanged

  'if your combo binds to a bindingsource
  myBS.Filter = string.Format("[State] = '{0}' ", stateTextBox.Text)

  'if you bound the combo straight to the datatable (you didnt, but it looks like you did)
  myDt.DefaultView.Filter = string.Format("[State] = '{0}' ", stateTextBox.Text)

End Sub

If your combo has thousands of items (tut tut), you should do this in a timer, with its interval set to 100ms, and every time it fires it decrements a variable starting at 5, by 1. WHen the var hit 0, stop the timer and set the filter. Every key press in the textbox resets the variable to 5, so basically about half a second after user stops typing, the filter is set. THis ensures multiple filters are not done, hogging the ui thread