none
Items collection cannot be modified when the datasource property is set RRS feed

  • Question

  • Hi am having this little problem of Items collection cannot be modified when the datasource property is set and i don't know how to go about it, Please help me if possible.

    Here, i have populated names of Universities from my MSAccess DB in the University Combobox. But what i want to do is,

    On the SelectedChange Event of the University Combobox Then the respective Faculties will be populated in another Combobox called Faculty Combo box. I am performing this operation using one Class definition and Querying from different methods outside the class.

    So the error of "Items collection cannot be modified when the datasource property is set" is thrown when i try to select any University from the University Combo box. But i can figure it out, Everything seems OK while looking at it but i know something is wrong within the lines.

    Here is my code:

    Class Definition codes:

        ' Database connection
        Public DBcon As New OleDbConnection("Provider=Microsoft.JET.OLEDB.4.0;" & _
                                                    "Data Source=DATABASE.mdb")
        'Get DB Command ready
        Public DBCmd As OleDbCommand
    
        'Database data
        Public DBDA As OleDbDataAdapter
        Public DBDT As DataTable
    
        'Parameter Query
        Public Parameter As New List(Of OleDbParameter)
    
        'Query Stats
        Public Record As Integer
        Public Exception As String
    
        Public Sub ExecQuery(Query As String)
            'Reseting the Query Stats
            Record = 0
            Exception = ""
    
            Try
                'Open a Connetion
                DBCon.Open()
    
                'Creating Database command
                DBCmd = New OleDbCommand(Query, DBCon)
    
                'Loading Params into Database Command
                Parameter.ForEach(Sub(x) DBCmd.Parameters.Add(x))
    
                'Clear the List of Params
                Parameter.Clear()
    
                'Execute Command and Fill the DataTable
                DBDT = New DataTable
                DBDA = New OleDbDataAdapter(DBCmd)
                Record = DBDA.Fill(DBDT)
            Catch ex As Exception
                Exception = ex.Message
            End Try
    
            'Close Connection
            If DBCon.State = ConnectionState.Open Then DBCon.Close()

    Code for Populating the University Combo box:

    Try
                'Query string
                Dim myQuery As String
                myQuery = "SELECT * FROM Universities ORDER BY University_Name ASC"
    
                'Run query
                Access_DB.ExecQuery(myQuery)
    
                ' In case of Errors Report
                If NoErrors(True) = False Then Exit Sub
    
                'Fill the Combo box
    
                'Clear Combobox
                University_Choice.University_NameComboBox.Items.Clear()
    
                'Populate the combo box
                With University_Choice.University_NameComboBox
                    .DisplayMember = "University_Name"
                    .ValueMember = "University_ID"
                    .DataSource = Access_DB.DBDT
                    .AutoCompleteMode = AutoCompleteMode.SuggestAppend
                    .AutoCompleteSource = AutoCompleteSource.ListItems
                End With
    
                'Display the first item found
                If Access_DB.Record > 0 Then University_Choice.University_NameComboBox.SelectedIndex = 0
    
            Catch ex As Exception
                Access_DB.Exception = ex.Message
            End Try

    Code for Populating the Faculties Combo box:

     Try
                'Query string
                Dim QueryFaculty As String
                QueryFaculty = "SELECT * FROM University_Faculties " & _
                    "WHERE University_ID = @UniversityID"
    
                Access_DB.ParamAdd("@UniversityID", "%" & _
                                         University_Choice.University_NameComboBox.SelectedValue.ToString & "%")
    
                'Run query
                Access_DB.ExecQuery(QueryFaculty)
    
                'In case of errors report
                If NoErrors(True) = False Then Exit Sub
    
                'Clear
                University_Choice.cboFaculty.Items.Clear()
    
                'Populate the combo box
                With University_Choice.cboFaculty
                    .DisplayMember = "Faculty_Name"
                    .ValueMember = "University_Faculties_ID"
                    .DataSource = AccessDB.DBDT
                    .AutoCompleteMode = AutoCompleteMode.SuggestAppend
                    .AutoCompleteSource = AutoCompleteSource.ListItems
                End With
    
                'Display the first item found
                If Access_DB.Record > 0 Then University_Choice.cboFaculty.SelectedIndex = 0
            Catch ex As Exception
                Access_DB.Exception = ex.Message
            End Try

    Please if you need any further Info please let me Know. Thanks


    Friday, May 26, 2017 2:11 AM

All replies

  • Try removing ‘University_Choice.cboFaculty.Items.Clear()’, since you use “data binding” by assigning a data table to DataSource. In this case, instead of affecting the items of the combobox, you should change the data table or its rows. In your code Clear is not needed since you recreate the associated data table.

    If you want to clear the combobox and cancel data binding, you can also execute ‘University_Choice.cboFaculty.DataSource = Nothing’.



    Friday, May 26, 2017 4:39 AM
  • Am sorry for that long code, just noticed its kind of vague.

    When i checked through the lines, i found that the "Record" variable is always rested to 0 each time in the Class definition code i.e here 'Resetting the Query Stats Record = 0. But the Try...Catch block did not specify a particular line where the error is. All i discovered is when the method for populating the faculty is fired, it will still go through same class definition, thereby reset the "Record" variable to zero.

    I believe that's one of the problem but still don't know to fix that.

    Thanks for your idea Viorel

    Friday, May 26, 2017 7:27 AM
  • Am sorry for that long code, just noticed its kind of vague.

    When i checked through the lines, i found that the "Record" variable is always rested to 0 each time in the Class definition code i.e here 'Resetting the Query Stats Record = 0. But the Try...Catch block did not specify a particular line where the error is. All i discovered is when the method for populating the faculty is fired, it will still go through same class definition, thereby reset the "Record" variable to zero.

    I believe that's one of the problem but still don't know to fix that.

    Thanks for your idea Viorel

    Not quite sure what you're getting at here, but Viorel_ is correct.  Since you have a databound control you cannot modify its Items collection directly.  Instead you must modify the underlying datasource to which the control is bound.

    Also, for future reference, there is an "insert code" button on the post editor.  Please use it when pasting code. I will modify your original post to include the proper code blocks.


    Reed Kimble - "When you do things right, people won't be sure you've done anything at all"

    Friday, May 26, 2017 1:59 PM
    Moderator
  • I have seen my problems, i was actually clearing my Faculty combobox each time the method is fired. Thanks so much you guys are really great!!!

    But after removing that line causing the problem, the Faculty combobox still was not populated, i thought the Parameter.Clear() method is the problem. Next, when i got rid of the Parameter.Clear() method i got an error saying "The OleDbParameter is already contained by another OleDbParameter collection". Then i used the Remove() method of the Parameter nothing still was still populated.

    One other thing is that i am using One class definition to handle many queries.

    please your idea will be appreciated, i am total new to this forum i believe i will learn how things are done here.

    Thanks to Kimble and Viorel.

    Friday, May 26, 2017 4:25 PM
  • Hi Pat,

    As Viorel's opinion,if you want to clear the combobox and cancel data binding, you can also execute ‘University_Choice.cboFaculty.DataSource = Nothing’, so you can use University_Choice.University_NameComboBox.DataSource=Nothing instead of University_Choice.University_NameComboBox.Items.Clear().

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, June 7, 2017 8:56 AM
    Moderator