Solved

DataGridview in vb.net 2010

Posted on 2013-01-26
14
578 Views
Last Modified: 2013-01-31
I have datagrid which is bound to order item data source. This has two foreign keys
 categoryid and  subcategoryid   subcategory is child table of  category.

The code is as mentoned below

  Dim categorycol As New DataGridViewComboBoxColumn
        Dim SubCatcol As New DataGridViewComboBoxColumn
        Dim DLoccol As New DataGridViewComboBoxColumn
        Dim DPricol As New DataGridViewComboBoxColumn

        Dim bindingsource1 As New BindingSource
        Dim bindingsource2 As New BindingSource
        Dim bindingsource3 As New BindingSource
        Dim bindingsource4 As New BindingSource
        Dim bindingsource5 As New BindingSource
        Try



            objUC_Oitem = New UC_OrderItem

            ds = objUC_Oitem.Load_OrderItem


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

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

                With categorycol
                    .HeaderText = "Category"
                    .Name = "OCategory"

                End With
                categorycol.DataSource = BindingSource2
                categorycol.ValueMember = ds.Tables("OrderCategory").Columns("OCcode").ColumnName
                categorycol.DisplayMember = ds.Tables("OrderCategory").Columns("OCName").ColumnName
                categorycol.DataPropertyName = ds.Tables("OrderItem").Columns("Occode").ColumnName

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

         
         
            If dgvList.Columns.Contains("SubCatCol") = False Then

                bindingsource5.DataSource = ds
                bindingsource5.DataMember = ds.Tables("orderSubcategory").TableName

                With SubCatcol
                    .HeaderText = "SubCategory"
                    .Name = "SubCatCol"
                End With
                SubCatcol.DataSource = bindingsource5
                SubCatcol.ValueMember = ds.Tables("OrderSubCategory").Columns("osccode").ColumnName
                SubCatcol.DisplayMember = ds.Tables("OrderSubCategory").Columns("oscname").ColumnName
                SubCatcol.DataPropertyName = ds.Tables("Orderitem").Columns("osccode").ColumnName

                dgvList.Columns.Insert(dgvList.Columns("osccode").Index + 1, SubCatcol)
            End If




            dgvList.AutoResizeColumns(DataGridViewAutoSizeColumnsMode.AllCells)

Both  category combo and  subcategory combo is correctly getting populated and is linked to the respective ids in orderitem table.

My requirement is i want to populate only related  subcategory for the  category selected in the category combo.(currenlty all the order subcategory is getting populated in subcategorycombo)

First i want to know whether this  is possible and if so how to do it.

Pls respond as early as possible

Thanks in advance
0
Comment
Question by:venkataramanaiahsr
  • 7
  • 6
14 Comments
 
LVL 18

Accepted Solution

by:
John (Yiannis) Toutountzoglou earned 500 total points
Comment Utility
Hi .. venkataramanaiahsr..
Yes it possible to achieve this ...
You have to use 3 different events..
Yours DatagridView Editing Control Showing Event
YourCombobox DropDown event and the DropDownClosed event as follow
 
Private Mycombo1 As ComboBox
 
Private sub dgvList_EditingControlShowing(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewEditingControlShowingEventArgs) Handles dgvList.EditingControlShowing
Try
If (Mycombo1 IsNot Nothing) Then


                RemoveHandler Mycombo1.DropDownClosed, New EventHandler(AddressOf MyComboBox_DropDownClosed)
                AddHandler Mycombo1.DropDownClosed, New EventHandler(AddressOf MYComboBox_DropDownClosed)


                RemoveHandler Mycombo1.DropDown, New EventHandler(AddressOf MyComboBox_DropDown)
                AddHandler Mycombo1.DropDown, New EventHandler(AddressOf MyComboBox_DropDown)


            End If

Catch ex As Exception

     MessageBox.Show(ex.Message, "ErrorOccured", MessageBoxButtons.OK, MessageBoxIcon.Error)

 End Try

End Sub

Private Sub MyComboBox_DropDownClosed(ByVal sender As Object, ByVal e As EventArgs)
       
        Try

            If Me.dgvList.CurrentRow.Cells("OrderCategory").IsInEditMode Then
                
                Dim CustCategory As Integer = Mycombo1.SelectedValue
                ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                Dim bs As New BindingSource
                bs.DataSource = BindingSource2
                bs.DataMember = "OrderCategory"
                bs.Filter = ("Your KeyOrderCategory =" & CustCategory)

                Dim col As New DataGridViewComboBoxCell
                col = Me.dgvList.CurrentRow.Cells("orderSubcategory")

                col.DataSource = bs
                col.DisplayMember = "OrderCategory"
                col.ValueMember = "Your KeyOrderSubCategory"
                '''''''''''''''''''''''''''''''''''''''''''''''''''''''''
            
            End If
        
        Catch ex As Exception

           MessageBox.Show(ex.Message, "ErrorOccured", MessageBoxButtons.OK, MessageBoxIcon.Error)

        End Try

    End Sub
 Private Sub MyComboBox_DropDown(ByVal sender As Object, ByVal e As EventArgs)

     
        Try

            Dim CustCategory As Integer = Me.dgvList.CurrentRow.Cells("OrderCategory").Value
            


                ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
                Dim bs As New BindingSource
                bs.DataSource = BindingSource2
                bs.DataMember = "OrderCategory"
                bs.Filter = ("Your KeyOrderCategory =" & CustCategory)

                Dim col As New DataGridViewComboBoxCell
                col = Me.dgvList.CurrentRow.Cells("orderSubcategory")

                col.DataSource = bs
                col.DisplayMember = "OrderCategory"
                col.ValueMember = "Your KeyOrderSubCategory"
                '''''''''''''''''''''''''''''''''''''''''''''''''''''''''

            End If

       

        Catch ex As Exception

            MessageBox.Show(ex.Message, "ErrorOccured", MessageBoxButtons.OK, MessageBoxIcon.Error)

        End Try

    End Sub 

Open in new window

The "Key" of all these is the  "bs.Filter = ("Your KeyOrderCategory =" & CustCategory)"
Hope that helps...(Adjust your tables name and the keys as value members accordingly)..
0
 

Author Comment

by:venkataramanaiahsr
Comment Utility
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?
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
Comment Utility
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

Open in new window

and tell me if the filtered ComboBox is empty or not
0
 

Author Comment

by:venkataramanaiahsr
Comment Utility
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.
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
Comment Utility
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...
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
Comment Utility
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...
0
 

Author Comment

by:venkataramanaiahsr
Comment Utility
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.
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
Comment Utility
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 ...
0
 
LVL 27

Expert Comment

by:Ark
Comment Utility
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

Open in new window

0
 

Author Comment

by:venkataramanaiahsr
Comment Utility
(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
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
Comment Utility
Set in your NameSpace "Imports System.Linq"
Did finally my solution worked also?
0
 

Author Comment

by:venkataramanaiahsr
Comment Utility
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
0
 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
Comment Utility
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

Open in new window

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

Author Comment

by:venkataramanaiahsr
Comment Utility
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.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction When many people think of the WebBrowser (http://msdn.microsoft.com/en-us/library/2te2y1x6%28v=VS.85%29.aspx) control, they immediately think of a control which allows the viewing and navigation of web pages. While this is true, it's a…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now