dcsimg
Results 1 to 12 of 12
  1. #1

    Thread Starter
    Lively Member
    Join Date
    Jul 2013
    Posts
    126

    Resolved [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:
    1. Private Sub PopComboBox(JobID As String)
    2.  
    3.         'Dim dt As New DataTable
    4.         Dim conStr As String = QCGMAIN
    5.         Dim sqCon As New SqlConnection(conStr)
    6.         sqCon.Open()
    7.         Dim Adapter As New SqlDataAdapter("SELECT ItemID, LineDesc + '  ' + REPLACE(Amount, '-', '$') As DisplayMem, SageSO FROM SageJobs WHERE JobID = '" & JobID & "'", sqCon)
    8.         Adapter.Fill(CBdt)
    9.         sqCon.Close()
    10.  
    11.         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)
    12.         Me.POItems.DisplayMember = "DisplayMem"
    13.         Me.POItems.DataSource = CBdt
    14.         'DirectCast(Me.DataGridView1.Columns("POItems"), DataGridViewComboBoxColumn).ValueMember = "ItemID"
    15.         'DirectCast(Me.DataGridView1.Columns("POItems"), DataGridViewComboBoxColumn).DisplayMember = "DisplayMem"
    16.         'DirectCast(Me.DataGridView1.Columns("POItems"), DataGridViewComboBoxColumn).DataSource = dt
    17.  
    18.         Me.DataGridView1.Columns("POItems").ReadOnly = False    'Now make only this column editable
    19.         Dim col As DataGridViewColumn = Me.DataGridView1.Columns("POItems")
    20.         col.Width = 265
    21.  
    22.     End Sub
    23.  
    24.  'Private WithEvents editingControl As ComboBox
    25.     'Sub 1 of 4 DGV Combobox SelectedIndexChanged
    26.     Private Sub DataGridView1_EditingControlShowing(sender As Object, e As DataGridViewEditingControlShowingEventArgs) Handles DataGridView1.EditingControlShowing
    27.         'editingControl = TryCast(e.Control, ComboBox)
    28.  
    29.         If DataGridView1.CurrentCell.ColumnIndex = 2 Then
    30.             Dim CmbBx As ComboBox = TryCast(e.Control, ComboBox)
    31.             If (CmbBx IsNot Nothing) Then
    32.                 ' Remove an existing event-handler, if present, to avoid adding multiple handlers when the editing control is reused.
    33.                 RemoveHandler CmbBx.SelectionChangeCommitted, New EventHandler(AddressOf editingControl_SelectedIndexChanged)
    34.                 AddHandler CmbBx.SelectionChangeCommitted, New EventHandler(AddressOf editingControl_SelectedIndexChanged)
    35.             End If
    36.         End If
    37.  
    38.     End Sub
    39.     'Sub 2 of 4 DGV Combobox
    40.     Private Sub DataGridView1_CellEndEdit(sender As Object, e As DataGridViewCellEventArgs) Handles DataGridView1.CellEndEdit
    41.         'editingControl = Nothing
    42.     End Sub
    43.     'Sub 3 of 4 DGV Combobox
    44.     Private Sub DataGridView1_CurrentCellDirtyStateChanged(ByVal sender As Object, ByVal e As EventArgs) Handles DataGridView1.CurrentCellDirtyStateChanged
    45.  
    46.         If IsNothing(DataGridView1.CurrentCell) Then Exit Sub
    47.         If DataGridView1.IsCurrentCellDirty And TypeOf DataGridView1.CurrentCell Is DataGridViewComboBoxCell Then
    48.             DataGridView1.CommitEdit(DataGridViewDataErrorContexts.Commit)
    49.         End If
    50.         If DataGridView1.IsCurrentCellDirty And TypeOf DataGridView1.CurrentCell Is DataGridViewCheckBoxCell Then
    51.             DataGridView1.CommitEdit(DataGridViewDataErrorContexts.Commit)
    52.         End If
    53.     End Sub
    54.  
    55.     'Sub 4 of 4 DGV Combobox
    56.     Private Sub editingControl_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) 'Handles editingControl.SelectedIndexChanged
    57.         Dim CmbBx As ComboBox = TryCast(sender, ComboBox)
    58.         'Dim CmbBx As ComboBox = editingControl
    59.         Dim DGV As DataGridView = Me.DataGridView1
    60.         Dim row As DataGridViewRow = DGV.CurrentRow
    61.  
    62.         Dim ItemID As String = CmbBx.SelectedValue 'Shows the ValueMember value
    63.         'Dim DisplayMem As String = CmbBx.DisplayMember
    64.         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)
    65.         Dim JobID As String = Me.QuoteNoTxtBx.Text & Me.SufxTxtBx.Text
    66.         Dim CustPO As String = Nothing
    67.         Dim POQTY As String = Nothing
    68.         Dim POUnitEa As String = Nothing
    69.         Dim POExt As String = Nothing
    70.  
    71.         Dim sqCon As New SqlClient.SqlConnection(QCGMAIN)
    72.         Dim sqCmd As New SqlClient.SqlCommand
    73.  
    74.         'Read database
    75.         sqCmd.Connection = sqCon            'create the DB connection
    76.         sqCon.Open()                        'open the connection
    77.         sqCmd.CommandText = "SELECT QTY, UnitPrice, Amount FROM SageJobs WHERE SageSO = '" & SageSO & "' AND JobID = '" & JobID & "' AND ItemID = '" & ItemID & "'"
    78.         Dim sqReader As SqlDataReader = sqCmd.ExecuteReader()        'execute the SQL command
    79.         If sqReader.HasRows Then
    80.             While sqReader.Read()
    81.                 If Not IsDBNull(sqReader.Item("QTY")) Then
    82.                     POQTY = sqReader.Item("QTY").ToString
    83.                 End If
    84.                 If Not IsDBNull(sqReader.Item("UnitPrice")) Then
    85.                     POUnitEa = sqReader.Item("UnitPrice").ToString
    86.                 End If
    87.                 If Not IsDBNull(sqReader.Item("Amount")) Then
    88.                     POExt = sqReader.Item("Amount").ToString
    89.                 End If
    90.             End While
    91.         End If
    92.         sqReader.Close()       'always close the reader when you're done with it.
    93.         sqCon.Close()
    94.  
    95.         Try
    96.             sqCmd = sqCon.CreateCommand()
    97.             sqCmd.CommandText = "SELECT CustPO FROM dbo.SageJobs WHERE SageSO = '" & SageSO & "' And CustPO != ''"
    98.             sqCon.Open()
    99.             Dim RetVal As Object = sqCmd.ExecuteScalar()
    100.             If Not IsNothing(RetVal) And Not IsDBNull(RetVal) Then
    101.                 CustPO = RetVal 'for boolean or RetVal.ToString for string
    102.             End If
    103.             sqCon.Close()
    104.         Catch ex As Exception
    105.             MessageBox.Show(ex.ToString)
    106.         End Try
    107.  
    108.         'Populate Sage line item info into Grid row:
    109.         row.Cells("ItemID").Value = ItemID
    110.         row.Cells("CustPO").Value = CustPO
    111.         row.Cells("SageSO").Value = SageSO
    112.         row.Cells("POQTY").Value = POQTY
    113.         row.Cells("POUnitEa").Value = POUnitEa
    114.         row.Cells("POExt").Value = POExt
    115.     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.

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    99,144

    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  3. #3
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    99,144

    Re: DataGridViewComboBox reverts back to random selection when leaving the cell

    Quote Originally Posted by jmcilhinney View Post
    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  4. #4

    Thread Starter
    Lively Member
    Join Date
    Jul 2013
    Posts
    126

    Re: DataGridViewComboBox reverts back to random selection when leaving the cell

    Okay, I will work on an example, thank you.

  5. #5

    Thread Starter
    Lively Member
    Join Date
    Jul 2013
    Posts
    126

    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.

  6. #6
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    99,144

    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.
    Why is my data not saved to my database? | MSDN Data Walkthroughs
    VBForums Database Development FAQ
    My CodeBank Submissions: VB | C#
    My Blog: Data Among Multiple Forms (3 parts)
    Beginner Tutorials: VB | C# | SQL

  7. #7

    Thread Starter
    Lively Member
    Join Date
    Jul 2013
    Posts
    126

    Re: DataGridViewComboBox reverts back to random selection when leaving the cell

    Updated project to remove bin and obj folders and reattached.
    Attached Files Attached Files

  8. #8

    Thread Starter
    Lively Member
    Join Date
    Jul 2013
    Posts
    126

    Re: DataGridViewComboBox reverts back to random selection when leaving the cell

    Any luck?

  9. #9

    Thread Starter
    Lively Member
    Join Date
    Jul 2013
    Posts
    126

    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?

  10. #10
    Frenzied Member
    Join Date
    Jul 2011
    Location
    UK
    Posts
    1,147

    Re: DataGridViewComboBox reverts back to random selection when leaving the cell

    Quote Originally Posted by Fedaykin View Post
    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
    The contents of the datatable look like this:
    Code:
    ItemID  |                   DisplayMem                    | SageSO
    --------------------------------------------------------------------
       1    | PCB Assembly ,(ITEM 01) Golden Girls  $12.5     | SM13557
       2    | NRE  $2.5                                       | SM13557
       1    | PCB Assembly ,(ITEM 01)  ECN wire mod  $5       | SM13695
       1    | PCB Assembly ,(ITEM 01) ECN add resistors  $10  | SM13696
       2    | PCB Assembly ,(ITEM 02) NRE Stencil  $20        | SM13696
    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.

  11. #11

    Thread Starter
    Lively Member
    Join Date
    Jul 2013
    Posts
    126

    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.

  12. #12

    Thread Starter
    Lively Member
    Join Date
    Jul 2013
    Posts
    126

    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:
    1. Private Sub PopComboBox(JobID As String)
    2.  
    3.         Dim dt As New DataTable
    4.         Dim conStr As String = QCGMAIN
    5.         Dim sqCon As New SqlConnection(conStr)
    6.         sqCon.Open()
    7.         Dim Adapter As New SqlDataAdapter("SELECT ItemID, ItemID + ' ' + SageSo As UniqueID, LineDesc + '  ' + REPLACE(Amount, '-', '$') As DisplayMem, SageSO FROM SageJobs WHERE JobID = '" & JobID & "'", sqCon)
    8.         Adapter.Fill(dt)
    9.         sqCon.Close()
    10.  
    11.         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)
    12.         Me.POItems.DisplayMember = "DisplayMem"
    13.         Me.POItems.DataSource = dt
    14.  
    15.         Me.DataGridView1.Columns("POItems").ReadOnly = False    'Now make only this column editable
    16.         Dim col As DataGridViewColumn = Me.DataGridView1.Columns("POItems")
    17.         col.Width = 265
    18.  
    19.     End Sub
    20.  
    21.     Private Sub DataGridView1_EditingControlShowing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs) Handles DataGridView1.EditingControlShowing
    22.         CmbBx = TryCast(e.Control, ComboBox)
    23.         If (CmbBx IsNot Nothing) Then
    24.             ' Remove an existing event-handler, if present
    25.             RemoveHandler CmbBx.SelectionChangeCommitted, New EventHandler(AddressOf ComboBox_SelectedIndexChanged)
    26.             AddHandler CmbBx.SelectionChangeCommitted, New EventHandler(AddressOf ComboBox_SelectedIndexChanged)
    27.         End If
    28.     End Sub
    29.  
    30.     Private Sub DataGridView1_CurrentCellDirtyStateChanged(ByVal sender As Object, ByVal e As EventArgs) Handles DataGridView1.CurrentCellDirtyStateChanged
    31.  
    32.         'End editing the cell as soon as a combobox item is selected
    33.         If IsNothing(DataGridView1.CurrentCell) Then Exit Sub
    34.         If DataGridView1.IsCurrentCellDirty And TypeOf DataGridView1.CurrentCell Is DataGridViewComboBoxCell Then
    35.             DataGridView1.CommitEdit(DataGridViewDataErrorContexts.Commit)
    36.         End If
    37.  
    38.     End Sub
    39.  
    40.     Private Sub ComboBox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs)
    41.  
    42.         Dim DGV As DataGridView = CType(sender.EditingControlDataGridView, DataGridView)
    43.         Dim row As DataGridViewRow = DGV.CurrentRow
    44.  
    45.         'Pulls the column (numbered 0-3) in the datarow inside the Combobox (See PopComboBox Sub)
    46.         Dim ItemID As String = (CType(CmbBx.SelectedItem, DataRowView)).Row.ItemArray(0).ToString()
    47.         Dim UniqID As String = (CType(CmbBx.SelectedItem, DataRowView)).Row.ItemArray(1).ToString()
    48.         Dim SageSO As String = (CType(CmbBx.SelectedItem, DataRowView)).Row.ItemArray(3).ToString()
    49.        
    50.         'do stuff
    51.  
    52.     End Sub
    Last edited by Fedaykin; Today at 03:46 PM.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width