Update a datagridview having combobox with binding

Question

I am using Vb.Net to load data from sql server database. I have the car datatable as follows:

I load car details and distinct model values as follows:

sql = " select * from car"
daCar = New SqlDataAdapter(sql, sqlConn)
daCar.Fill(dsDataset, "car")

sql = " select distinct model from car"
daCar = New SqlDataAdapter(sql, sqlConn)
daCar.Fill(dsDataset, "model")

Now when I load the form I have two bindingsources and have one combobox to display the distinct values of the model so user car modify data easily

bsCar = New BindingSource(dsDataset, "car")
bsModel = New BindingSource(dsDataset, "model")
dgvCars.DataSource = bsCar

Dim col2 As New DataGridViewComboBoxColumn
col2.HeaderText = "Model"
col2.Name = "model"

col2.DataSource = bsModel
col2.DisplayMember = "model"
col2.ValueMember = "model"
col2.DataPropertyName = "model"

col2.FlatStyle = FlatStyle.Flat
dgvCars.Columns.Insert(2, col2)
dgvCars.Columns(2).Width = 150

And also to be able to modify the contents of the combobx I have the following event:

Private Sub dgvCars_EditingControlShowing(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs) _
        Handles dgvCars.EditingControlShowing
        If dgvCars.CurrentCell.ColumnIndex = 2
            Dim comboBox As ComboBox = TryCast(e.Control, ComboBox)
            comboBox.DropDownStyle = ComboBoxStyle.DropDown
        End If
    End Sub

Now every thing works perfect Except one problem: Whenever I try to make changes to the model combobox in the dgvCar, changes will return back to the old ones after losing focus! Also I cant add new records to the datagridview! How can I solve this problem?

Answer 1

From the looks of it the problem is, you are not letting the database know the data has changed.

So when you change or add something to the combo box it only adds to the combo box (CB) and when you refresh the CB (when it loses focus) and get the data from the database, the entered data is not in the database and is therefore not displayed (lost)

To solve this add an SQL query to Update or Insert into the database/table when you add or change data in the combo box

Also to add rows to a data grid view, investigate the following code:

dgvCars.Rows.Add()