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?

 

[Tom]

Your post & question are a bit unclear but no you do not have to create a new dataset every time you want to add a new record...

 

[keb1965]

Ok, lets see if I can clear it up then ...

I have two tables, one contains the list of artists (I simply added them to the table manually), the other contains the value selected by a user and stored as an individual record.

The artist table is not associated in any way with the response table. The only thing the artist table does currently is fill the combobox with its records so the user can select one of them. What we are finding out, is that people are entering values that are not in the artist table and subsequently not in the combobox as an item that can be selected. This means I can either a)add the new artist to the artist table manually, or b) somehow get the value the user enters in the combo box and add it to the artist table to be available for the next user.

I have set the datasource of the combobox to the artist table (this fills the items in the combobox) and I have bound the "text" value to the response table, this means that whatever is showing in the combobox, is what is added to the record in the response table.

Now what I would like to do is add the value in the combobox (if it is unique in the list) to the artist table.

That is my dilemna.

I hope this explains it better.

 

[Tom]

No still not very clear... perhaps what I'm not understanding is your question, do you not know how to add a record to your datatable?

Dim rowNew as DataRow
rowNew = myDataset.Tables("Artists").NewRow
rowNew("ArtistName") = "Elvis"
myDataSet.Tables("Artists").Rows.Add(newRow)

This will add the record to your data table in memory.

 

[keb1965]

it can't be that simple can it?

So all I need to do is add the text that the user enters as a new item in the dataset used to fill the combobox, then update that dataset to the database?

Interesting ... I'll be back in a bit

 

[keb1965]

Ok, here is the updated code for my purposes ...

As you can see, I also verify if the value is not already in the combobox before adding it to the dataset.

Private Sub cbArtist_TextChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles cbArtist.TextChanged
    Dim cbValue As String = cbArtist.Text
    If IsInList(cbValue, cbArtist) = True Then
        Exit Sub
    Else
        Dim rowNew As DataRow
        rowNew = cbArtist.DataSource.NewRow
        rowNew("Artist") = cbValue
        cbArtist.DataSource.Rows.Add(rowNew)
    End If
End Sub

Private Function IsInList(ByVal strString As String, ByVal ComboBoxControl As ComboBox) As Boolean
    Dim X As Integer
    Dim InList As Boolean = False
    If strString <> "" Then
        For X = 0 To ComboBoxControl.Items.Count - 1
            Dim drv As DataRowView
            drv = ComboBoxControl.Items.Item(X)
            If UCase(strString) = UCase(drv.Item(1).ToString()) Then
                InList = True
                Exit For
            End If
        Next
    End If
    Return InList
End Function

I have several comboboxes I want to add this to, so I will likely edit the event handler to work for all comboboxes.... less code is easier to manage

 

[Tom]

You dont have to loop thru the entire table to see if a record exists in it. The datatable.Select method will return an array of rows that meet your criteria. In this case you dont need to work with the rows just check to see if any were returned (meaning the record does already exist)

Private Function IsInList (strArtist as string, myTable as DataTable) as Boolean

    Dim rowsFound() as DataRow
    rowsFound = myTable.Select(String.Format("Artist = '{0}'", strArtist))
    If rowsFound.Length > 0 Then Return True

End Function

less code is easier to manage

 

[Tom]

Just looking at your coding too and see a mistake. cbArtist is the name of your combo box right? You dont want to add the new record to the combobox, you want to add it to your dataset/datatable which is bound to the combobox.

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

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

 

[keb1965]

Thanks once again ... in my infinite stupidity I was just about to find out that I had to update the source, not the combobox itself.

Anyway, I am now working 100% ... I had to slightly change the code you provided to allow for multiple comboboxes to be verified ... Since each combobox will have a differing number of items and it will change regularly, it was simpler to have a separate table for each combobox. (unless you have another idea) Also, column 0 is the seed column and column 1 is where the data is stored. I simply check the column name for the table passed and see if it is in there... now I can pass any table and any value using just one function.

Thanks again!

Private Function IsInListA(ByVal strValue As String, ByVal myTable As DataTable) As Boolean
    Dim rowsFound() As DataRow
    rowsFound = myTable.Select(String.Format(myTable.Columns(1).Caption & " = '{0}'", strValue))
    If rowsFound.Length > 0 Then Return True
End Function

Oh and how right you are that less code is easier to manage

 

[keb1965]

Incidently, I found a caveat with updating the dataset from a combobox text value using the TextChanged event.

When a DataSource is added to a combo box to fill it in, if the combo box is not sorted, then the combo box text is changed immediately upon setting the datasource, and since there is no column associated yet, it puts the value "System.Data.DataRowView" in as the text. This causes the TextChanged event to fire. To resolve this, I simply set the combo box Sorted property to True, however, if you don't want the contents sorted, you can just as easily filter for that value in the TextChanged event or change the value after binding the datasource.

 

[Tom]

Its not a good idea to hard code to a specific column specially considering that you want to use it for different tables and different columns names. Instead pass the whole string inlcuding column name when you call the function.

If IsInListA("Artist = 'Elvis'") Then

Private Function IsInListA(ByVal strValue As String, ByVal myTable As DataTable) As Boolean
    Dim rowsFound() As DataRow
    rowsFound = myTable.Select(strValue)
    If rowsFound.Length > 0 Then Return True
End Function

 

[Tom]

I dont know what your interface looks like to suggest which event to use to add a new row and update the db but i'd definitely not use any of the combobox events for this.

Didnt you say they had a textbox to enter a search name? Isnt this where you added the code to add the row to the table & combobox? If so, I'd call the update right after the row was added and everything is outside of the combobox events.

 

[keb1965]

unfortunately, there isn't a textbox where the name is entered. The combo box has an editable text control, to properly have the values update, I have to trap the TextChanged event and exit the function if the control has focus. If it doesn't have focus it changes. If it loses focus after the text has been changed, I raise the TextChanged event.

Either way, it is behaving as it should.

Incidently the columns are created programmatically and as such will always be incremented as expected.

 

[JohnH]

Just looking at your coding too and see a mistake. cbArtist is the name of your combo box right? You dont want to add the new record to the combobox, you want to add it to your dataset/datatable which is bound to the combobox.

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

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

If the combo is bound to ds.Tables("Artists") then these two codes is identical. It would be implied they are.

 

[Tom]

Originally Posted by Tom
Just looking at your coding too and see a mistake. cbArtist is the name of your combo box right? You dont want to add the new record to the combobox, you want to add it to your dataset/datatable which is bound to the combobox.

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

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

If the combo is bound to ds.Tables("Artists") then these two codes is identical. It would be implied they are.

This is incorrect, they are not identical and the former is not even a valid statement.