DataGridview in vb.net 2010

Thanks a lot. it worked. Just one more thing. is it possible to link subcategory col to order item subcategoryid so that moment i select the subcategory name in the combo, id gets updated in orderitem subcategoryid.

Currently this subcategory col is independent. to update the subcategoryid in the oderitem dataset which is bound to the dgvlist, i am looping thr the datset in submit button and updating the orderitem subcategoryid.

 col (DataGridViewComboBoxCell) does not have a dataproperty  wherin i can link  to orderitem subcategoryid.

if  i bind the subcat col(DataGridViewComboBoxcolumn)  dataproperty , when i select the subcategory in the combo,  data error is thrown and the grid becomes blank.

is there any way i can solve this?

Initialy set these 2 line in your Code ...(nothing else)

Private Sub dgvList_DataError(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewDataErrorEventArgs) Handles dgvList.DataError
        
       
    End Sub

Select allOpen in new window

and tell me if the filtered ComboBox is empty or not

It is amazing! it is working perfectly fine now. i,e i intitally bound to order subcategory table at the col level with dataproperty set to order item subcategory id. then at cell level, in ordersubcategory combo dropdown event i bound filtered set of subcategory table .

Same thing i was getting error earlier.  (what is the logic behind it?)

Everyting working to my requirement except for few minor issues. If you can resolove it , it is absolutely great.  

1.  after selecting the value in the order subcategory combobox , the orderitem subcatid col does not get updated immediately. only it gets updated after i cllick anywhare outside this cell.
2. the order subcategory col drop down does not happen in single click. i have to click twice for the dropdown to happen.

For the second one you have to set the EditMode of the dgvList datagridview "EditOnEnter" and it will be fine..
For the first one you need also to select something from the second Combobox..It will updated automatically only if you have one and only value...

what is the logic behind it? ....the Empty DataError?
I really don't Know..I know how the DataError works but I noticed also my self when i used the code i sent you in a previous application of mine...

Thanks . 2nd problem solved. first one i did not understand your soln.

I have two combobox order category and order subcategory.  at the intial grid load , all the order category  is loaded with current order category id in order item  selcted in the order categor cell. when i click on drop down button of order subcategory col, depending on the order category  displayed , order subcategory is loaded with current  order subcat in the orderitem getting selected and displayed. Upto this it is fine. when i want to change the order subcategory and select a different order subcategory the corresponding id of order item does not get immediately updated. it will be still showing the old id only. only when i click outside this cell, the order item subcategoryid gets updated.

I have the same code in one of my applications...when i select something form the first Combobox the other one goes to "Empty" and i am then ready to  select something from the second ComboBox .The item appears directly to the second Combo After selection ...I can not understand why you have this problem ...

Actually, you don't need handle combobox open and close events to filter it's source. When cell is in edit mode all other cells validation is suspended, so you can add filter in cell_begin edit event and restore filter in cell_endedit. The only problem - there is no datagridview event when you choose item from combobox - Validated(ing), EndEdit, Formatting,Parsing and other events occuring when you leave editing cell. If you want subitem's cell reflecting Item's cell changes - you need combo events.

 'Dim ds As DataSet
    Private Sub dgv_CellBeginEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellCancelEventArgs) Handles dgvMain.CellBeginEdit
        Dim dgv As DataGridView = CType(sender, DataGridView)
        If e.ColumnIndex = -1 Then Return
        If dgv.Columns(e.ColumnIndex).DataPropertyName = "SubCategoryID" Then
            Dim r As DataGridViewRow = dgv.Rows(e.RowIndex)

           'change combo datasource when it's in edit mode
            Dim dv As New DataView(ds.Tables("SubCatTable"))
            dv.RowFilter = "CategoryID=" & r.Cells("CategoryID").Value
            Dim cboCol As DataGridViewComboBoxCell = CType(dgv(e.ColumnIndex, e.RowIndex), DataGridViewComboBoxCell)
            With cboCol
                .DisplayMember = "SubCategoryName"
                .ValueMember = "ID"
                .DataSource = dv
            End With
        End If
    End Sub

    Private Sub dgvMain_CellEndEdit(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvMain.CellEndEdit
        Dim dgv As DataGridView = CType(sender, DataGridView)
        If e.ColumnIndex = -1 Then Return
        If dgv.Columns(e.ColumnIndex).DataPropertyName = "SubCategoryID" Then
            Dim cboCol As DataGridViewComboBoxCell = CType(dgv(e.ColumnIndex, e.RowIndex), DataGridViewComboBoxCell)
           'restore combo datasource
            cboCol.DataSource = ds.Tables("SubCatTable")
            'or 
            'cboCol.DataSource = your_initial_bindingsource
        End If
    End Sub

'==========Just to reflect combobox changes==========
    Private Sub dgvMain_EditingControlShowing(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs) Handles dgvMain.EditingControlShowing
        If TypeOf e.Control Is System.Windows.Forms.ComboBox Then
            Dim dgv As DataGridView = CType(sender, DataGridView)
            Dim cell As DataGridViewCell = dgv.CurrentCell
            If cell Is Nothing Then Return
            If dgv.Columns(cell.ColumnIndex).DataPropertyName = "CategoryID" Then
                With DirectCast(e.Control, ComboBox)
                    RemoveHandler .DropDownClosed, AddressOf comboClosed
                    AddHandler .DropDownClosed, AddressOf comboClosed
                End With
            End If
        End If
    End Sub

    Private Sub comboClosed(ByVal sender As Object, ByVal e As EventArgs)
        Dim cell As DataGridViewCell = dgvMain.CurrentCell
        If cell Is Nothing Then Return
        If dgvMain.Columns(cell.ColumnIndex).DataPropertyName = "CategoryID" Then
            Dim r As DataGridViewRow = dgvMain.CurrentRow
            Dim catID As Integer = DirectCast(sender, ComboBox).SelectedValue
            If r.Cells("CategoryID").Value <> catID Then
                r.Cells("SubCategoryID").Value = (From p In ds.Tables("SubCatTable"). _
                                                  Select("CategoryID=" & catID) Select p("ID")).First
            End If
        End If
    End Sub

Select allOpen in new window

(From p In ds.Tables("SubCatTable"). _
                                                  Select("CategoryID=" & catID) Select p("ID")).First
            End If
can you explain this.

it is saying  "expression of type one dimensional array of system.data.datarow is not queryable.  make sure you are missing an assembly reference. and or name space import of for the linq provider

Set in your NameSpace "Imports System.Linq"
Did finally my solution worked also?

Thanks  a lot jtoutou.  with your valuable inputs and bit of more google search, finally i could achieve what i itended.  this is what i did.

In load button event of the grid

BindingSource1.DataSource = ds.Tables("OrderItem")
dgvList.DataSource = BindingSource1

 If dgvList.Columns.Contains("OCategory") = False Then
                BindingSource2.DataSource = ds
                BindingSource2.DataMember = ds.Tables("OrderCategory").TableName

                col = New DataGridViewComboBoxColumn

                With col
                    .HeaderText = "OCategory"
                    .Name = "OCategory"

                End With

                col.DataSource = bindingsource2
                col.ValueMember = ds.Tables("OrderCategory").Columns("OCcode").ColumnName
                col.DisplayMember = ds.Tables("OrderCategory").Columns("OCName").ColumnName
                col.DataPropertyName = ds.Tables("OrderItem").Columns("Occode").ColumnName

                dgvList.Columns.Insert(dgvList.Columns("Occode").Index + 1, col)
            End If


            If dgvList.Columns.Contains("OSubCategory") = False Then
                bindingsource3.DataSource = ds
                bindingsource3.DataMember = "OrderSubcategory"

                col = New DataGridViewComboBoxColumn
                With col
                    .HeaderText = "OSubCategory"
                    .Name = "OSubCategory"
                End With

                col.DataSource = bindingsource3
                col.ValueMember = ds.Tables("OrderSubCategory").Columns("OSCcode").ColumnName
                col.DisplayMember = ds.Tables("OrderSubCategory").Columns("OsCName").ColumnName
                col.DataPropertyName = ds.Tables("OrderItem").Columns("Osccode").ColumnName
                dgvList.Columns.Insert(dgvList.Columns("OCategory").Index + 1, col)

            End If

______________


Private Sub dgvlist_EditingControlShowing(ByVal sender As System.Object, ByVal e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs) Handles dgvList.EditingControlShowing

        mycombo1 = TryCast(e.Control, ComboBox)



        If (mycombo1 IsNot Nothing) Then

           
            RemoveHandler mycombo1.DropDown, New EventHandler(AddressOf mycombobox_dropdown)
            AddHandler mycombo1.DropDown, New EventHandler(AddressOf mycombobox_dropdown)
           
            RemoveHandler mycombo1.SelectedIndexChanged, New EventHandler(AddressOf mycombobox_SelectedIndexChanged)
            AddHandler mycombo1.SelectedIndexChanged, New EventHandler(AddressOf mycombobox_SelectedIndexChanged)

        End If
       
    End Sub

    Private Sub mycombobox_dropdown(ByVal sender As Object, ByVal e As EventArgs)


        If dgvList.CurrentRow.Cells("OSubCategory").IsInEditMode Then

            Dim s As Integer = dgvList.CurrentRow.Cells("OCategory").Value
            If s = 0 Then Exit Sub

            Dim bs As New BindingSource
            bs.DataSource = ds
            bs.DataMember = "OrderSubcategory"

            bs.Filter = "Occode = " & s
            Dim dgvcell As New DataGridViewComboBoxCell
            dgvcell = Me.dgvList.CurrentRow.Cells("OSubCategory")
            dgvcell.DataSource = bs
            dgvcell.DisplayMember = "oscname"
            dgvcell.ValueMember = "osccode"




        End If






    End Sub


    Private Sub mycombobox_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)

        If dgvList.CurrentRow.Cells("OCategory").IsInEditMode Then

           
            Dim dgvcell As New DataGridViewComboBoxCell
            dgvcell = Me.dgvList.CurrentRow.Cells("OSubCategory")
            dgvcell.DataSource = Nothing
            dgvcell.Items.Clear()
         

        End If

       
    End Sub

    Private Sub dgvList_DataError(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewDataErrorEventArgs) Handles dgvList.DataError


    End Sub


 Private Sub dgvList_RowValidating(ByVal sender As Object, ByVal data As DataGridViewCellCancelEventArgs) Handles dgvList.RowValidating
        If blnload = False Then Exit Sub

        Dim row As DataGridViewRow = dgvList.Rows(data.RowIndex)
        Dim trackOSCcell As DataGridViewCell = row.Cells(dgvList.Columns("Osubcategory").Index)
        data.Cancel = Not (IsTrackGood(trackOSCcell))

    End Sub

    Private Function IsTrackGood(ByRef cell As DataGridViewCell) As Boolean

        If cell.FormattedValue = "" Then
            cell.ErrorText = "Please select a OSC"
            dgvList.Rows(cell.RowIndex).ErrorText = _
                "Please select a OSC"
            Return False

        End If
        Return True
       
    End Function


    Private Sub dgvList_RowValidated(sender As Object, e As System.Windows.Forms.DataGridViewCellEventArgs) Handles dgvList.RowValidated
        If blnload = False Then Exit Sub
        dgvList.Rows(e.RowIndex).ErrorText = ""
        dgvList.Rows(e.RowIndex).Cells(dgvList.Columns("oSubcategory").Index).ErrorText = ""
    End Sub


In this only hitch is , when i change order category or order subcategory display value, the cell value does not get immed. updated. it gets updated , after i click out side the cell . But this does not create any problem as after changing the display item , when i click on submit, automatically the new values gets saved. .

Just for info. is there any way i can update  the cell value of the combobox cell immed. after changing the display value

Once again thanks a lot for your valued help

Some questions?

Private Sub mycombobox_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs)

        If dgvList.CurrentRow.Cells("OCategory").IsInEditMode Then

           
            Dim dgvcell As New DataGridViewComboBoxCell
            dgvcell = Me.dgvList.CurrentRow.Cells("OSubCategory")
            dgvcell.DataSource = Nothing
            dgvcell.Items.Clear()
          

        End If

Select allOpen in new window

Why you set the Datasource to Nothing ...
And Where is the DropDownClosed event...?

I wanted to clear the order subcategory when the selction in cateegory combo changes.

without setting datasource to nothing , it was not allowing me to clear the items. (it used to throw, cannot clear the items when it is bound)

otherwise, when the selection in ordercategory combo changes the last selection in order subcategory would still be highligted. only when i click on dropdown button of order sucategory , it would get cleared.

the dropdownclosed was not required in my case.

In selectindexed changed event , when ordercategory selecton changes , i am clearing ordersubcategory combo and in dropdown event, i am loading the filterd set of order subcategory for a selected ordercategory in os combo.