datagridview row with a combobox column not inserting in database

Question

Hi!

I added a DataGridView to my windows form. The grid is data bound and one of it's columns is of type DataGridViewComboBoxColumn and the column is also data bound. This datagrid is connected to another datagrid in parent-child relationship. Following is the code used for creating the grid and updating data to DB:

Public Class frmCampaigns
    Dim conSMS As New DBConnection
    Dim Schema As String = frmMain.DB
    Dim WithEvents BMB As BindingManagerBase
    Dim WithEvents adpCamp As New SqlDataAdapter("select * from in_campaign", conSMS.sqlConn)
    Dim WithEvents adpCB As New SqlDataAdapter("select * from in_cn_brand", conSMS.sqlConn)
	Dim dsCamp As New DataSet
	Dim adpBrands As New SqlDataAdapter("select distinct it_brand cb_name from in_item", conSMS.sqlConn)
    Dim dsBrands As New DataSet
	Dim cmdSave As New SqlCommand("", conSMS.sqlConn)

	Private Sub frmCampaign_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
	        BindFields()
	        Fill_Combo()
	        Fill_CBGrid()
	End Sub

	Private Sub BindFields()
        If conSMS.ConnectSQLDB(My.Settings.SERVER, Schema, My.Settings.ISEC) = True Then
            Try
                dsCamp.Clear()
                dsCamp.Relations.Clear()
                adpCamp.Fill(dsCamp, "CAM")
                adpCB.Fill(dsCamp, "CB")
                
                dsCamp.Relations.Add("drCB", dsCamp.Tables(0).Columns(0), dsCamp.Tables(1).Columns(1), False)
                
                dsCamp.Tables(0).PrimaryKey = New DataColumn() {dsCamp.Tables(0).Columns(0)}
                dsCamp.Tables(0).Columns(0).AutoIncrement = True
                dsCamp.Tables(1).PrimaryKey = New DataColumn() {dsCamp.Tables(1).Columns(0)}
                dsCamp.Tables(1).Columns(0).AutoIncrement = True
                
                txtCID.DataBindings.Clear()
                txtCID.DataBindings.Add("Text", dsCamp, "CAM.cn_id", True, DataSourceUpdateMode.OnPropertyChanged)
                txtCName.DataBindings.Clear()
                txtCName.DataBindings.Add("Text", dsCamp, "CAM.cn_Name", True, DataSourceUpdateMode.OnPropertyChanged)
                dtpSDate.DataBindings.Clear()
                dtpSDate.DataBindings.Add("Text", dsCamp, "CAM.cn_start", True, DataSourceUpdateMode.OnPropertyChanged)
                dtpEDate.DataBindings.Clear()
                dtpEDate.DataBindings.Add("Text", dsCamp, "CAM.cn_end", True, DataSourceUpdateMode.OnPropertyChanged)
                
                BMB = BindingContext(dsCamp, "CAM")
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")
                Me.Close()
            End Try
        End If
    End Sub

	Private Sub Fill_Combo()
        dsBrands.Clear()
        adpBrands.Fill(dsBrands, "CB")
    End Sub


	Private Sub Fill_CBGrid()
		Dim colCB As New DataGridViewComboBoxColumn
        colCB.FlatStyle = FlatStyle.Flat
        colCB.DisplayStyle = DataGridViewComboBoxDisplayStyle.Nothing
        colCB.AutoSizeMode = DataGridViewAutoSizeColumnMode.Fill
        colCB.HeaderText = "Brands"
        If conSMS.ConnectSQLDB(My.Settings.SERVER, Schema, My.Settings.ISEC) = True Then
            Try
                dgCBrand.DataSource = dsCamp
                dgCBrand.DataMember = "CAM.drCB"
                dgCBrand.Columns.RemoveAt(2)
                dgCBrand.Columns.Insert(2, colCB)
                colCB.DataSource = dsBrands
                colCB.Name = "cb_name"
                colCB.DataPropertyName = "cb_name"
                colCB.DisplayMember = "CB.cb_name"
                
                dgCBrand.Columns(0).Visible = False
                dgCBrand.Columns(1).Visible = False
            Catch ex As Exception
                MsgBox(ex.Message, MsgBoxStyle.Critical, "Error")
                Me.Close()
            End Try
        End If
	End Sub

	Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click
        If conSMS.ConnectSQLDB(My.Settings.SERVER, Schema, My.Settings.ISEC) = True Then
            Try
                BMB.EndCurrentEdit()
                
                adpCampaign.Update(dsCamp.Tables(0))
                adpCB.Update(dsCamp.Tables(1))
                
                MsgBox("Campaign details saved successfully!", MsgBoxStyle.Information, "Campaign Details")
                
            Catch ex As Exception
                MsgBox("An error occurred while saving Campaign Details. Error is: " & ex.Message, MsgBoxStyle.Critical, "Campaign Details")
            End Try
        End If
    End Sub
End Class

Now, if I select a value from drop down list of the combo box column, press enter key and then click on save button then my data is successfully updated to the database but if I just select the value and leave the datagrid and click on save button, no data is inserted in the DB.

I used datagrid.EndEdit() on datagrid_leave event but the problem is still there.

Answer 1

Try executing the CommitEdit method in the CurrentCellDirtyStateChanged event.

Private Sub dgCBrand_CurrentCellDirtyStateChanged(sender As Object, e As EventArgs) _
        Handles dgCBrand.CurrentCellDirtyStateChanged
    Dim dgv As DataGridView = DirectCast(sender, DataGridView)
    If dgv.IsCurrentCellDirty Then
        If TypeOf dgv.CurrentCell Is DataGridViewComboBoxCell Then
            dgv.CommitEdit(DataGridViewDataErrorContexts.Commit)
        End If
    End If
End Sub

Answer 2

Dim adpBrands As New SqlDataAdapter("select distinct it_brand cb_name from in_item",

Are "it_brand" and "cb_name" two different columns in the table?

Then there is a comma missing between them.