[RESOLVED] DataGridViewComboBox reverts back to random selection when leaving the cell
I'm dying here, I've been working on this for days. I've tried multiple binding methods thinking that was the issue, but it appears to be some kind of endedit or commit issue, but for the life of me I cannot isolate it. I had attempted to solicit help in another thread for this issue, but it was off topic and didn't relate to my original post, so i'm trying again:
Issue: I have a DataGridViewComboBox that reverts back to the first or a random selection when leaving the cell. I left in some of my commented out attempts to show my work:
vb.net Code:
Private Sub PopComboBox(JobID As String)
'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(CBdt)
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.DataGridView1.Columns("POItems").ReadOnly = False 'Now make only this column editable
Dim col As DataGridViewColumn = Me.DataGridView1.Columns("POItems")
col.Width = 265
End Sub
'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)
If DataGridView1.CurrentCell.ColumnIndex = 2 Then
Dim CmbBx As ComboBox = TryCast(e.Control, ComboBox)
If (CmbBx IsNot Nothing) Then
' Remove an existing event-handler, if present, to avoid adding multiple handlers when the editing control is reused.
RemoveHandler CmbBx.SelectionChangeCommitted, New EventHandler(AddressOf editingControl_SelectedIndexChanged)
AddHandler CmbBx.SelectionChangeCommitted, New EventHandler(AddressOf editingControl_SelectedIndexChanged)
End If
End If
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
Private Sub editingControl_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) 'Handles editingControl.SelectedIndexChanged
Dim CmbBx As ComboBox = TryCast(sender, ComboBox)
'Dim CmbBx As ComboBox = editingControl
Dim DGV As DataGridView = Me.DataGridView1
Dim row As DataGridViewRow = DGV.CurrentRow
Dim ItemID As String = CmbBx.SelectedValue 'Shows the ValueMember value
'Dim DisplayMem As String = CmbBx.DisplayMember
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(QCGMAIN)
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
As soon as I click out of the cell it pops right back to the first index or a random index (but it does not update the other values in the grid). If I have done something stupid, please tell me. I welcome your wrath just to solve the issue!
Last edited by Fedaykin; Jul 13th, 2018 at 05:24 PM.
Re: DataGridViewComboBox reverts back to random selection when leaving the cell
You haven't actually explained what you're trying to achieve. We shouldn't really have to work it out from code that doesn't do it.
I would also suggest that such a large block of code is hard to read without syntax highlighting so it is preferable to use this:
[highlight=vb.net]your code here[/highlight]
rather than this:
[code]your code here[/code]
Finally, you have posted a lot of code that is incomplete, i.e. we cannot run it as it is. I've pasted it into VS but I can't see anything obvious. This seems like the sort of issue (as most are) that requires debugging the code, not simply reading it, but we would have to do quite a bit of work to be able to get that code to run. What you should be doing is creating a simple, self-contained example that demonstrates the issue so that we can copy and paste it and run it without having to spend potentially large amounts of time just getting the code to run.
Re: DataGridViewComboBox reverts back to random selection when leaving the cell
Originally Posted by jmcilhinney
What you should be doing is creating a simple, self-contained example that demonstrates the issue so that we can copy and paste it and run it without having to spend potentially large amounts of time just getting the code to run.
As a demonstration of this, check out this thread. I was able to copy the code and run it immediately to start debugging to find the problem. In this case, even controls were added in code. Personally, I wouldn't mind if I had to add controls in the designer to get code to work but when I would have to add code that I need to guess at and the code provided does things like query a database that is not provided then it just becomes too onerous a task for someone who tends to answer questions here in breaks at work for the most part.
Re: DataGridViewComboBox reverts back to random selection when leaving the cell
I've created a stand alone project and included a small SQL database.
If you select an item from the combobox and then click out of the cell onto the next row you should see it revert to something other than what was selected.
Last edited by Fedaykin; Jul 14th, 2018 at 05:18 PM.
Re: DataGridViewComboBox reverts back to random selection when leaving the cell
Maybe it's the database but, judging by the size of your attachment, you have not deleted the 'bin' and 'obj' folders from your project, which you must do because they contain the compiled binaries.
Re: DataGridViewComboBox reverts back to random selection when leaving the cell
No matter how many ways I create, bind or manipulate handlers the behavior of the combobox in the DataGridView is the same. It will not stay on the selection when you click out of the cell. Does anyone have a small working example of a DataGridViewComboBoxColumn that actually works?
Re: DataGridViewComboBox reverts back to random selection when leaving the cell
Originally Posted by Fedaykin
No matter how many ways I create, bind or manipulate handlers the behavior of the combobox in the DataGridView is the same. It will not stay on the selection when you click out of the cell. Does anyone have a small working example of a DataGridViewComboBoxColumn that actually works?
I don't work with databases, so can't give you any real help. However, I did notice what I assume would be a problem in the code where you bind to your ComboBoxColumn (named POItems):
Code:
Private Sub PopComboBox(JobID As String)
'Dim dt As New DataTable
Dim conStr As String = "Server=WIN7-PC\SQLEXPRESS;Database=DEVELOP2;Trusted_Connection=True;"
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(CBdt)
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 = CBdt
Notice that there are multiple rows that have the same value in the ItemID column (which you have assigned to the ComboBoxColumn's ValueMember Property).
What's happening is when you select the ComboBox item that corresponds to say the 4th item in the table, what is ultimately being displayed (after you click away from the ComboBox cell) is the first item that has the the same ItemID value: i.e. the very first item.
So you expect to see 'PCB Assembly ,(ITEM 01) ECN add resistors $10' from the fourth item, but what you actually get is 'PCB Assembly ,(ITEM 01) Golden Girls $12.5' from the first item, because they both have the same ItemID value.
Re: DataGridViewComboBox reverts back to random selection when leaving the cell
Oh wow, I think you nailed it! I need to come up with a unique identifier. Thank you!!! I will rewrite today and let you know how it goes. I've been struggling with this for days, thank you.
Re: DataGridViewComboBox reverts back to random selection when leaving the cell
Thank you Inferrd!! I was losing all hope, I can't believe I didn't notice that I didn't have a unique identifier. I was so focused on what was displayed I never even thought about. Thanks also to jmc for the insight on code highlighting and uploading. I was just hitting a brick wall and I could not get this done without your help.
By combining the ItemID with the SageSO in the SQL statement I create a simple Unique identifier for this datatable. I'm sure you could get fancy and add a primary key to the datatable, but this serves my purpose. I also used SelctionChangeCommitted instead of SelectedIndex because it seems to behave a little better. PopComboBox is called when the form is loaded:
Here is my working code:
vb.net Code:
Private Sub PopComboBox(JobID As String)
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, ItemID + ' ' + SageSo As UniqueID, LineDesc + ' ' + REPLACE(Amount, '-', '$') As DisplayMem, SageSO FROM SageJobs WHERE JobID = '" & JobID & "'", sqCon)
Adapter.Fill(dt)
sqCon.Close()
Me.POItems.ValueMember = "UniqueID" '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
Private Sub DataGridView1_EditingControlShowing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs) Handles DataGridView1.EditingControlShowing
CmbBx = TryCast(e.Control, ComboBox)
If (CmbBx IsNot Nothing) Then
' Remove an existing event-handler, if present
RemoveHandler CmbBx.SelectionChangeCommitted, New EventHandler(AddressOf ComboBox_SelectedIndexChanged)
AddHandler CmbBx.SelectionChangeCommitted, New EventHandler(AddressOf ComboBox_SelectedIndexChanged)
End If
End Sub
Private Sub DataGridView1_CurrentCellDirtyStateChanged(ByVal sender As Object, ByVal e As EventArgs) Handles DataGridView1.CurrentCellDirtyStateChanged
'End editing the cell as soon as a combobox item is selected
If IsNothing(DataGridView1.CurrentCell) Then Exit Sub
If DataGridView1.IsCurrentCellDirty And TypeOf DataGridView1.CurrentCell Is DataGridViewComboBoxCell Then