[RESOLVED] Combobox Refuses to Populate

Hi all :wave:

Ok, I've been working on this code for several days now, and it's making me nuts. In this windows forms app, I have a form with six comboboxes. Two are parents, one with a single child and the other with three children.

All of these work beautifully except for one of the children. The code for each one is identical, and I've verified the query against the database a dozen times. I even have another of the children that is in the very same block of code. One works beautifully, the other one refuses to populate any data. Below is the code for the one that doesn't work.

I would tell you on what line the error occurs, but it doesn't throw any errors. Try catches show nothing, and I have VS 2010 set to throw all errors, but I get nothing, just an empty combo box where there should be a list of names. Any help would be greatly appreciated.

Code:

---

Public Class errorTracking_submit
    Dim ConnectionString = "Data Source=dvmx6030\MS2005_DEV1, 2755; user id =CRCUser;password=TWC5uck5;database=CRCTools;Connection Timeout=5"
    Dim connection As SqlConnection = New SqlConnection(ConnectionString)
    Dim UserID As String = Form1.adid
    'Set up binding sources
    Dim BindingSourceCP As New BindingSource
    Dim BindingSourceDiv As New BindingSource
    Dim BindingSourceEType As New BindingSource
    Dim BindingSourceECat As New BindingSource
    Dim BindingSourceProv As New BindingSource
    Dim BindingSource_Prov As New BindingSource
    Dim BindingSourceCRR As New BindingSource
    Dim BindingSource_CRR As New BindingSource

    Sub start() Handles MyBase.Load
        Me.cb_CRR.Visible = False
        Me.cb_Prov.Visible = False
        Me.Prov_Label.Visible = False
        Me.CRR_Label.Visible = False
        Call PopComboBoxes()
    End Sub

    Private Sub PopComboBoxes()

        'Get the data.  Each DataSet must contain a Parent table,
        'a Child table and a ParentChild relation between them.
        'There are six combo boxes, two parents and four children
        'cb_CP has one child, cb_EType has three
        'All children use identical code to obtain their data
        'Only cb_CRR is not working


        Dim ds_CP As DataSet = Me.GetDataSet_CP()
        Dim ds_EType As DataSet = Me.GetDataSet_EType()
        Dim ds_Prov As DataSet = Me.GetDataSet_Prov()
        Dim ds_CRR As DataSet = Me.GetDataSet_CRR()

        'Bind the parent sources to the parent tables.

        BindingSourceCP.DataSource = ds_CP
        BindingSourceCP.DataMember = "Parent_CP"

        BindingSourceEType.DataSource = ds_EType
        BindingSourceEType.DataMember = "Parent_EType"

        BindingSourceProv.DataSource = ds_Prov
        BindingSourceProv.DataMember = "Parent_EType"

        BindingSourceCRR.DataSource = ds_CRR
        BindingSourceCRR.DataMember = "Parent_EType"


        'Bind the child sources to the relationships.

        BindingSourceDiv.DataSource = BindingSourceCP
        BindingSourceDiv.DataMember = "ParentChild"

        BindingSourceECat.DataSource = BindingSourceEType
        BindingSourceECat.DataMember = "ParentChild"

        BindingSource_Prov.DataSource = BindingSourceProv
        BindingSource_Prov.DataMember = "ParentChild"

        BindingSource_CRR.DataSource = BindingSourceCRR
        BindingSource_CRR.DataMember = "ParentChild"


        'Bind the parent controls to the parent sources.
        With cb_ErrorType
            .DisplayMember = "Name"
            .ValueMember = "ETypeID"
            .DataSource = BindingSourceEType
            .SelectedIndex = -1
        End With

        With cb_CablePartner
            .DisplayMember = "CPName"
            .ValueMember = "ID"
            .DataSource = BindingSourceCP
            .SelectedIndex = -1
        End With

        'Bind the child controls to the child sources.
        With cb_ECat
            .DisplayMember = "ErrorDiscription"
            .ValueMember = "ErrorID"
            .DataSource = BindingSourceECat
            .SelectedIndex = -1
        End With

        With cb_Market
            .DisplayMember = "DivName"
            .ValueMember = "DivID"
            .DataSource = BindingSourceDiv
            .SelectedIndex = -1
        End With
    End Sub

    Private Function GetDataSet_CRR()

        If connection.State = ConnectionState.Closed Then
            connection.Open()
        End If
            Dim ds As New DataSet
            Dim parent_etype As DataTable = Me.GetParentTable_EType()
            Dim child_crr As DataTable = Me.GetChildTable_CRR()
            ds.Tables.Add(parent_etype)
            ds.Tables.Add(child_crr)

            'Add a relationship between the ID of the parent
            'table and the ParentID of the child table.

            ds.Relations.Add("ParentChild", _
                                parent_etype.Columns("EtypeID"), _
                                child_crr.Columns("GroupID"))
            Return ds
            connection.Close()
    End Function

    Private Function GetChildTable_CRR() As DataTable
        Dim strSQL As String = "SELECT ID, [Last Name] + ', ' + [First Name] AS Name, GroupID FROM TBLadminEMPLIST WHERE JobTitle = 1"
        Dim da As New SqlDataAdapter(strSQL, connection)
        Dim table As New DataTable("Child_CRR")
        da.Fill(table)
        Return table
    End Function

    Private Sub cb_ErrorType_SelectionChangeCommitted(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cb_ErrorType.SelectionChangeCommitted
        'Shows/hides and populates the remaining child combo boxes based on the selected value of cb_Etype

        Select Case cb_ErrorType.SelectedValue
            Case Is = 5
                Me.CRR_Label.Visible = True
                Me.CRR_Label.Enabled = True
                With cb_CRR
                    .Visible = True
                    .Enabled = True
                    .DisplayMember = "Name"
                    .ValueMember = "ID"
                    .DataSource = BindingSource_CRR
                    .SelectedIndex = -1
                End With
                With cb_Prov
                    .Visible = False
                    .Enabled = False
                End With
                With Prov_Label
                    .Visible = False
                    .Enabled = False
                End With
            Case Is = 3
                Me.Prov_Label.Visible = True
                Me.Prov_Label.Enabled = True
                With cb_Prov
                    .Visible = True
                    .Enabled = True
                    .DisplayMember = "ProvName"
                    .ValueMember = "ProvID"
                    .DataSource = BindingSource_Prov
                    .SelectedIndex = -1
                End With
                With cb_CRR
                    .Visible = False
                    .Enabled = False
                End With
                With CRR_Label
                    .Visible = False
                    .Enabled = False
                End With
            Case Is = 4
                With cb_Prov
                    .Visible = False
                    .Enabled = False
                End With
                With Prov_Label
                    .Visible = False
                    .Enabled = False
                End With
                With cb_CRR
                    .Visible = False
                    .Enabled = False
                End With
                With CRR_Label
                    .Visible = False
                    .Enabled = False
                End With
        End Select
    End Sub

---

Thanks in advance!
Zoe

Running thru the code in notepad the following seems like the path to the ComboBox not loading. With that said how many rows are being returned via Me.GetParentTable_EType() and Me.GetChildTable_CRR() ? What happens in Me.GetChildTable_CRR as the code is not shown?

Code:

---

Dim ds_CRR As DataSet = Me.GetDataSet_CRR()

---

Code:

---

    Private Function GetDataSet_CRR()

        If connection.State = ConnectionState.Closed Then
            connection.Open()
        End If
            Dim ds As New DataSet
            Dim parent_etype As DataTable = Me.GetParentTable_EType()
            Dim child_crr As DataTable = Me.GetChildTable_CRR()
            ds.Tables.Add(parent_etype)
            ds.Tables.Add(child_crr)

            'Add a relationship between the ID of the parent
            'table and the ParentID of the child table.

            ds.Relations.Add("ParentChild", _
                                parent_etype.Columns("EtypeID"), _
                                child_crr.Columns("GroupID"))
            Return ds
            connection.Close()
    End Function

---

I've snipped out the code from the initial post for you. Hacking through it in notepad is messy to say the least.

Here's the data set function:

Quote:

---

Originally Posted by ZoeWashburn

Code:

---

    Private Function GetDataSet_CRR()

        If connection.State = ConnectionState.Closed Then
            connection.Open()
        End If
            Dim ds As New DataSet
            Dim parent_etype As DataTable = Me.GetParentTable_EType()
            Dim child_crr As DataTable = Me.GetChildTable_CRR()
            ds.Tables.Add(parent_etype)
            ds.Tables.Add(child_crr)

            'Add a relationship between the ID of the parent
            'table and the ParentID of the child table.

            ds.Relations.Add("ParentChild", _
                                parent_etype.Columns("EtypeID"), _
                                child_crr.Columns("GroupID"))
            Return ds
            connection.Close()
    End Function

---

---

And here's the function for Me.GetChildTable_CRR:

Quote:

---

Code:

---

Private Function GetChildTable_CRR() As DataTable
        Dim strSQL As String = "SELECT ID, [Last Name] + ', ' + [First Name] AS Name, GroupID FROM TBLadminEMPLIST WHERE JobTitle = 1"
        Dim da As New SqlDataAdapter(strSQL, connection)
        Dim table As New DataTable("Child_CRR")
        da.Fill(table)
        Return table
    End Function

---

---

When all is said and done, the query should return 52 rows to the data table, which I've confirmed it does via breakpoints and whatnot. So something is getting lost between the data set and the combo box itself.

Thank you!! I've been pounding my head on this brick wall for so long, it's good to finally get some constructive feedback on it.

Zoe

The only thought would be (since both tables have rows) that there is an issue with the relationship model between the two tables or how the underlying data is joined

Code:

---

            ds.Relations.Add("ParentChild", _
                                parent_etype.Columns("EtypeID"), _
                                child_crr.Columns("GroupID"))

---

I see no problems here

Code:

---

                With cb_CRR
                    .Visible = True
                    .Enabled = True
                    .DisplayMember = "Name"
                    .ValueMember = "ID"
                    .DataSource = BindingSource_CRR
                    .SelectedIndex = -1
                End With

---

Although the following has no baring on your issue at hand might I suggest renaming your BindingSource variables from

Code:

---

        Dim BindingSourceCP As New BindingSource
        Dim BindingSourceDiv As New BindingSource
        Dim BindingSourceEType As New BindingSource
        Dim BindingSourceECat As New BindingSource
        Dim BindingSourceProv As New BindingSource
        Dim BindingSource_Prov As New BindingSource
        Dim BindingSourceCRR As New BindingSource
        Dim BindingSource_CRR As New BindingSource

---

To something like this

Code:

---

        Dim bsCP As New BindingSource
        Dim bsDiv As New BindingSource
        Dim bsEType As New BindingSource
        Dim bsECat As New BindingSource
        Dim bsProv As New BindingSource
        Dim bs_Prov As New BindingSource
        Dim bsCRR As New BindingSource
        Dim bs_CRR As New BindingSource

---

Bottom line (at least for me) is the human eye can have issues focusing on a bunch of variables all beginning with BindingSource and is easier on the eyes (especially when attempting to figure out the current issue) using the shorten version


Any ways nothing is sticking out with your code so I would be looking at the parent child relation. One option is to create a temp form with two DataGridView controls and assign the data source as master detail to see if the data does show properly or not. Sorry that is all I can think about at this moment.

What's the Count for each binding source?

Kevin,

Thanks for the input! I'll tinker with it some more tomorrow and see where I end up.

Stanav,

As in the rowcount pulled by each query?

Zoe

Quote:

---

Originally Posted by ZoeWashburn

Kevin,

Thanks for the input! I'll tinker with it some more tomorrow and see where I end up.

Stanav,

As in the rowcount pulled by each query?

Zoe

---

No, the row count in a datatable isn't the same as the count in the binding source that it binds to. For example, if you set a filter on the binding source, the bindingsource.count will be different than the datatable.rows.count. Since you have a relationship in your dataset, that could create a constraint that results in no rows. The bindingsource.Count property will give you the actual count of the records that will be shown.

stanav,

You were right. The count of the binding source is 0. So... the question becomes, how do I troubleshoot that? I'm guessing it's something in the parent/child relationship, but it doesn't make a whole lot of sense. The parent bindingsource has a count of three (as it should) and I know the values are 3, 4, and 5. The Select Case in the cb_ErrorType_SelectionChangeCommitted fires based on these values. cb_Prov fires on option 3 and it loads beautifully. cb_CRR fires on 5, and that table has a GroupID field that is set to 5 for all rows, so there's no reason I can think of why the relationship would return no rows. Any ideas?

Hey, at least we're making progress. We have identified the problem. ;-)

Thanks,
Zoe

Quote:

---

Originally Posted by ZoeWashburn

stanav,

You were right. The count of the binding source is 0. So... the question becomes, how do I troubleshoot that? I'm guessing it's something in the parent/child relationship, but it doesn't make a whole lot of sense. The parent bindingsource has a count of three (as it should) and I know the values are 3, 4, and 5. The Select Case in the cb_ErrorType_SelectionChangeCommitted fires based on these values. cb_Prov fires on option 3 and it loads beautifully. cb_CRR fires on 5, and that table has a GroupID field that is set to 5 for all rows, so there's no reason I can think of why the relationship would return no rows. Any ideas?

Hey, at least we're making progress. We have identified the problem. ;-)

Thanks,
Zoe

---

It actually makes sense that the problem is with the relationship and the underlying data. Best to replicate the SQL statements outside of the current environment so there is no confusion with everything else going on. As mentioned before you could create a temp form with a master and detail datagridview and run the SQL, see what happens. Actually before doing this try running the SQL in an SQL editor which can be from the editor inside Visual Studio to MS-Access on linked database tables.

Quote:

---

Originally Posted by kevininstructor

It actually makes sense that the problem is with the relationship and the underlying data. Best to replicate the SQL statements outside of the current environment so there is no confusion with everything else going on. As mentioned before you could create a temp form with a master and detail datagridview and run the SQL, see what happens. Actually before doing this try running the SQL in an SQL editor which can be from the editor inside Visual Studio to MS-Access on linked database tables.

---

I've verified the query in the Query Editor multiple times, it's fine. Will try the master/detail data grid and see what happens.

Quote:

---

Originally Posted by ZoeWashburn

I've verified the query in the Query Editor multiple times, it's fine. Will try the master/detail data grid and see what happens.

---

It should not take a whole lot to get a master-detail to display as you already have the BindingSource and DataSets already setup. My guess is the master will display data and the details will not.

Well, Kevin, looks like you shouldn't have taken that bet. The master/detail datagridview did show the detail...all 52 records entact. Literally copied and pasted the code from one form to the other and changed from comboboxes to datagrids.

Have I stumped the geniuses yet? :-P

Quote:

---

Originally Posted by ZoeWashburn

Well, Kevin, looks like you shouldn't have taken that bet. The master/detail datagridview did show the detail...all 52 records entact. Literally copied and pasted the code from one form to the other and changed from comboboxes to datagrids.

Have I stumped the geniuses yet? :-P

---

It was only a guess, not a bet :)

Ok, fine. Split hairs why don'tcha? ;-) j/k

But anyway... If I could figure out how to switch my form to a datagridview and make it look right I would, but I'm thinking that would be really cluttered with everything I've got going on here. Any ideas for how to get the combobox to do what the datagrid did??

Ok, this is officially making me insane. The datatable loads fine, the dataset loads fine, but for some screwed up reason the bindingsource refuses to acknowledge that the child table rows are there.

Anyone have an idea here??

I do not have a solution but in the VS2008 project (will work in VS2010 but not VS2005 without tweaks) attached load it up, build and run it. There is a master/detail on customers and orders. There are three customers where two have no details (disjointed per say). So two rows will show data and one will not. Now in the form load event uncomment the line

Code:

---

'WorkingBindSourceWithDataSet()

---

And now rebuild and run the project. You will get no rows back on any of the details as in the first run. Go back to the IDE and comment out

Code:

---

dsTables.Tables.Add(dtOrders)

---

Then uncomment

Code:

---

'dsTables.Tables.Add(dtOrders.Copy)

---

Rebuild and run, you will now get the same results as the first run.

My point here is sometimes your code can look fine but is not. I am guessing (not betting) you have a similar issue with your code logic that is not easily detected such as the demo I provided.

Note that I included some method extensions that might assist in debugging.

Hey Kevin,

Thanks! I ended up switching to static databinding instead of the dynamic code I was using and all the combo boxes are working now. But I'd still like to go back to dynamic binding eventually, so this will be very helpful on a future release.

Thanks again!
Zoe