[RESOLVED] DataGridView ComboBox - SelectedIndex to FindStringExact

[Fedaykin]

The following code will work on a Form ComboBox:

ComboBox1.SelectedIndex = ComboBox1.FindStringExact("YourStringyouknow")

I'm trying to implement the same method on a DataGridView Combobox cell, but i'm not sure how to reference the cell:

For Each row As DataGridViewRow In DataGridView1.Rows
    DGVCombobox.SelectedIndex = DGVCombobox.FindStringExact("YourStringyouknow") 'obviously this doesn't work
Next

I tried:

        For Each row As DataGridViewRow In DataGridView1.Rows
            Dim DGVCmbBx As DataGridViewComboBoxCell = TryCast(row.Cells("POItems"), DataGridViewComboBoxCell)
            DGVCmbBx.Selected = DGVCmbBx.Items("YourStringyouknow") 'but this is expecting an integer, not a string
        Next

[.paul.]

Try this...
The currentcell address i used was just for testing purposes as was the string "two"

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    DataGridView1.CurrentCell = DataGridView1.Rows(0).Cells(3)
    DataGridView1.BeginEdit(True)
    ' Attempt to cast the EditingControl to a ComboBox.
    'this will only work if CurrentCell is a combobox column
    Dim cb As ComboBox = TryCast(DataGridView1.EditingControl, ComboBox)
    If Not cb Is Nothing Then
         cb.SelectedIndex = cb.FindStringExact("two")
    End If
End Sub

[Fedaykin]

I modified what you wrote to fit in my loop, but when it hits the next row in the DataGridView the previous row reverts back to the first index in the DataGridViewComboBox. The same behavior occurs if I manually change the selection in the DataGridViewComboBox, it reverts to some random selection if I hit the enter key or click out of the cell (and without firing the editingcontrol.SelectedIndexChanged):

For Each row As DataGridViewRow In Me.DataGridView1.Rows
                        DataGridView1.CurrentCell = row.Cells(2)
                        DataGridView1.BeginEdit(True)
                        Dim cb As ComboBox = TryCast(DataGridView1.EditingControl, ComboBox)
                        If Not cb Is Nothing Then
                            cb.SelectedIndex = cb.FindStringExact(CmbBxItem)
                        End If
                        DataGridView1.EndEdit(True)
Next

     Private WithEvents editingControl As ComboBox
    'Sub 1 of 4 DGV Combobox SelectedIndexChanged
    Private Sub DataGridView1_EditingControlShowing(sender As Object, e As DataGridViewEditingControlShowingEventArgs) Handles DataGridView1.EditingControlShowing
        editingControl = TryCast(e.Control, ComboBox)
    End Sub
    'Sub 2 of 4 DGV Combobox
    Private Sub DataGridView1_CellEndEdit(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellEndEdit
        editingControl = Nothing
    End Sub
    'Sub 3 of 4 DGV Combobox
    Private Sub DataGridView1_CurrentCellDirtyStateChanged(ByVal sender As Object, ByVal e As EventArgs) Handles DataGridView1.CurrentCellDirtyStateChanged
        If IsNothing(DataGridView1.CurrentCell) Then Exit Sub
        If DataGridView1.IsCurrentCellDirty And TypeOf DataGridView1.CurrentCell Is DataGridViewComboBoxCell Then
            DataGridView1.CommitEdit(DataGridViewDataErrorContexts.Commit)
        End If
        If DataGridView1.IsCurrentCellDirty And TypeOf DataGridView1.CurrentCell Is DataGridViewCheckBoxCell Then
            DataGridView1.CommitEdit(DataGridViewDataErrorContexts.Commit)
        End If
    End Sub
    'Sub 4 of 4 DGV Combobox
    Private Sub editingControl_SelectedIndexChanged(sender As Object, e As EventArgs) Handles editingControl.SelectedIndexChanged
        Dim CmbBx As ComboBox = TryCast(sender, ComboBox)
        Dim DGV As DataGridView = CType(sender.EditingControlDataGridView, DataGridView)
        Dim row As DataGridViewRow = DGV.CurrentRow

        Dim ItemID As String = CmbBx.SelectedValue 'Shows the ValueMember value
        Dim SageSO As String = (CType(CmbBx.SelectedItem, DataRowView)).Row.ItemArray(2).ToString() 'Pulls the 3rd column (numbered 0-2) in the datarow inside the Combobox (See PopComboBox Sub)
        Dim JobID As String = Me.QuoteNoTxtBx.Text & Me.SufxTxtBx.Text
        Dim CustPO As String = Nothing
        Dim POQTY As String = Nothing
        Dim POUnitEa As String = Nothing
        Dim POExt As String = Nothing

        Dim sqCon As New SqlClient.SqlConnection(SqlConnStr)
        Dim sqCmd As New SqlClient.SqlCommand

        'Read database
        sqCmd.Connection = sqCon            'create the DB connection 
        sqCon.Open()                        'open the connection
        sqCmd.CommandText = "SELECT QTY, UnitPrice, Amount FROM SageJobs WHERE SageSO = '" & SageSO & "' AND JobID = '" & JobID & "' AND ItemID = '" & ItemID & "'"
        Dim sqReader As SqlDataReader = sqCmd.ExecuteReader()        'execute the SQL command
        If sqReader.HasRows Then
            While sqReader.Read()
                If Not IsDBNull(sqReader.Item("QTY")) Then
                    POQTY = sqReader.Item("QTY").ToString
                End If
                If Not IsDBNull(sqReader.Item("UnitPrice")) Then
                    POUnitEa = sqReader.Item("UnitPrice").ToString
                End If
                If Not IsDBNull(sqReader.Item("Amount")) Then
                    POExt = sqReader.Item("Amount").ToString
                End If
            End While
        End If
        sqReader.Close()       'always close the reader when you're done with it.
        sqCon.Close()

        Try
            sqCmd = sqCon.CreateCommand()
            sqCmd.CommandText = "SELECT CustPO FROM dbo.SageJobs WHERE SageSO = '" & SageSO & "' And CustPO != ''"
            sqCon.Open()
            Dim RetVal As Object = sqCmd.ExecuteScalar()
            If Not IsNothing(RetVal) And Not IsDBNull(RetVal) Then
                CustPO = RetVal 'for boolean or RetVal.ToString for string
            End If
            sqCon.Close()
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try

        'Populate Sage line item info into Grid row:
        row.Cells("ItemID").Value = ItemID
        row.Cells("CustPO").Value = CustPO
        row.Cells("SageSO").Value = SageSO
        row.Cells("POQTY").Value = POQTY
        row.Cells("POUnitEa").Value = POUnitEa
        row.Cells("POExt").Value = POExt

    End Sub

[.paul.]

That happens with comboboxes when you bind them to the same datasource. The solution is to clone the datasource so each combobox has a unique datasource...

[.paul.]

If you set the datasource for the column it should allow the comboboxes to work independently

[Fedaykin]

The datasource is already set for the column:

    Private Sub PopComboBox(JobID As String)

        Dim dt As New DataTable
        Dim conStr As String = MyConStr
        Dim sqCon As New SqlConnection(conStr)
        sqCon.Open()
        Dim Adapter As New SqlDataAdapter("SELECT ItemID, LineDesc + '  ' + REPLACE(Amount, '-', '$') As DisplayMem, SageSO FROM SageJobs WHERE JobID = '" & JobID & "'", sqCon)
        Adapter.Fill(dt)
        sqCon.Close()

        Me.POItems.ValueMember = "ItemID" 'You can just reference the name of the Combobox directly in the grid. (Me.POItems is the name of the column in the DGV)
        Me.POItems.DisplayMember = "DisplayMem"
        Me.POItems.DataSource = dt

        Me.DataGridView1.Columns("POItems").ReadOnly = False    'Now make only this column editable
        Dim col As DataGridViewColumn = Me.DataGridView1.Columns("POItems")
        col.Width = 265

    End Sub

[.paul.]

It's definitely a binding related issue that you're experiencing. The code for changing an individual datagridviewcomboboxcell's combobox.selectedindex is working as it should. The problem you have occurs whether the selectedindex is set manually or programmatically, so it's some sort of binding issue...

[Fedaykin]

I agree with you about the binding source, however I've tried everything method I know to bind. I put a BindingSource object in the graphical and assigned it to the DataSource of the column and then filled it with:

    Private Sub PopComboBox(JobID As String)

        Dim dt As New DataTable
        Dim conStr As String = MyConStr
        Dim sqCon As New SqlConnection(conStr)
        sqCon.Open()
        Dim Adapter As New SqlDataAdapter("SELECT ItemID, LineDesc + '  ' + REPLACE(Amount, '-', '$') As DisplayMem, SageSO FROM SageJobs WHERE JobID = '" & JobID & "'", sqCon)
        Adapter.Fill(dt)
        sqCon.Close()

        Me.BindingSource1.DataSource = dt
        Me.POItems.ValueMember = "ItemID" 'You can just reference the name of the Combobox directly in the grid. (Me.POItems is the name of the column in the DGV)
        Me.POItems.DisplayMember = "DisplayMem"
        'Me.POItems.DataSource = dt

        Me.DataGridView1.Columns("POItems").ReadOnly = False    'Now make only this column editable
        Dim col As DataGridViewColumn = Me.DataGridView1.Columns("POItems")
        col.Width = 265

    End Sub

I tried removing the BindingSource and just filling the column with the datatable direct:

    Private Sub PopComboBox(JobID As String)

        Dim dt As New DataTable
        Dim conStr As String = MyConStr
        Dim sqCon As New SqlConnection(conStr)
        sqCon.Open()
        Dim Adapter As New SqlDataAdapter("SELECT ItemID, LineDesc + '  ' + REPLACE(Amount, '-', '$') As DisplayMem, SageSO FROM SageJobs WHERE JobID = '" & JobID & "'", sqCon)
        Adapter.Fill(dt)
        sqCon.Close()

        Me.POItems.ValueMember = "ItemID" 'You can just reference the name of the Combobox directly in the grid. (Me.POItems is the name of the column in the DGV)
        Me.POItems.DisplayMember = "DisplayMem"
        Me.POItems.DataSource = dt

        Me.DataGridView1.Columns("POItems").ReadOnly = False    'Now make only this column editable
        Dim col As DataGridViewColumn = Me.DataGridView1.Columns("POItems")
        col.Width = 265

    End Sub

Seems no matter how I fill it, the behavior is the same. This DataGridViewComboBoxColumn does not seem to be very stable and the fact that it doesn't have a native SelectedIndex is just plain silly.

I also just tried this, but the behavior is exactly the same, it reverts to some random item in the pulldown when I click out of the combobox cell:

    Private Sub PopComboBox(JobID As String)

        For Each row As DataGridViewRow In Me.DataGridView1.Rows
            Dim dt As New DataTable
            Dim conStr As String = QCGMAIN
            Dim sqCon As New SqlConnection(conStr)
            sqCon.Open()
            Dim Adapter As New SqlDataAdapter("SELECT ItemID, LineDesc + '  ' + REPLACE(Amount, '-', '$') As DisplayMem, SageSO FROM SageJobs WHERE JobID = '" & JobID & "'", sqCon)
            Adapter.Fill(dt)
            sqCon.Close()

            Dim cb As DataGridViewComboBoxCell = TryCast(row.Cells("POItems"), DataGridViewComboBoxCell)

            cb.ValueMember = "ItemID"
            cb.DisplayMember = "DisplayMem"
            cb.DataSource = dt

            'Me.POItems.ValueMember = "ItemID" 'You can just reference the name of the Combobox directly in the grid. (Me.POItems is the name of the column in the DGV)
            'Me.POItems.DisplayMember = "DisplayMem"
            'Me.POItems.DataSource = dt
        Next

    End Sub

I'm starting to think this is more of an EndEdit type of thing...

[.paul.]

I'm not sure. I could get your project working if I had it here to debug it, but based on the code you've posted, I can't give you an answer...

[Fedaykin]

It's a huge project and there are several database tables to pull the data from, so not really an option. I appreciate you sticking with it this far.

I've set breakpoints on every method and the only thing that fires after clicking out of the cell is the CellEndEdit. When it fires that the Combobox jumps back to some other value than what was selected:

    Private WithEvents editingControl As ComboBox

    Private Sub DataGridView1_CellEndEdit(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellEndEdit
        editingControl = Nothing
    End Sub

[Fedaykin]

Maybe because I'm binding two different sets of data to the grid?

1. I manually added columns in the graphical display and they are unbound.
2. I bind data to the grid which adds columns to the right.
3. I bind data to the 'POItems' DataGridViewComboBox column based on user selection on the form itself.
4. User makes a selection from the DataGridViewComboBox that populates the other columns in the row. <-- clicking out of this cell reverts back to the original or random selection on the ComboBox.

Is it not legal to have two different sets of data on the grid? It's that what is freaking it out?