Solved

DataGridview in vb.net 2010

Posted on 2013-01-26
14
582 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
ID: 38824892
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
ID: 38826469
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
ID: 38826487
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
ID: 38827682
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
ID: 38827703
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
ID: 38827768
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
ID: 38827782
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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 18

Expert Comment

by:John (Yiannis) Toutountzoglou
ID: 38827856
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
ID: 38829837
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
ID: 38830727
(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
ID: 38831032
Set in your NameSpace "Imports System.Linq"
Did finally my solution worked also?
0
 

Author Comment

by:venkataramanaiahsr
ID: 38831528
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
ID: 38831656
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
ID: 38834922
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Setting runtime form location 4 31
Unable  to create new object 9 41
More on Time zones in vb 2010 12 37
Help with WinAPI Signatures in VB.NET for SetWindowSubclass 2 35
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…
1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…

895 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

15 Experts available now in Live!

Get 1:1 Help Now