directoryservices.directorysearcher loop has error A column named 'cn' already belongs to this datatable

Question

I have a function that searches AD for members of various groups. If I remove the loop, I don't get the error "A column named 'cn' already belongs to this datatable", however, I need to iterate through each OU.

Function getCOMDLNames(ByVal searchStr As String) As DataTable
    Dim MySearchRoot As DirectoryEntry = New DirectoryEntry("path", "usr", "pwd")
    Dim MyDirectorySearcher As New DirectorySearcher(MySearchRoot)
    Dim con As New SqlConnection
    Dim cmdSelect As SqlCommand
    Dim da As New SqlDataAdapter
    Dim cdt As New DataTable
    Dim row As DataRow
    Dim campusGroupArray() As String
    Dim j As Integer = 0

    Try
        con.ConnectionString = ConfigurationManager.AppSettings("sql_dir")
        con.Open()
        cmdSelect = New SqlCommand("...")
        cmdSelect.Connection = con
        da.SelectCommand = cmdSelect
        da.Fill(cdt)
        ReDim campusGroupArray(0 To cdt.Rows.Count)

        'Fill array
        For i As Integer = 0 To cdt.Rows.Count - 1
            row = cdt.Rows(i)
            campusGroupArray(i) = row(0)
        Next

        'Remove empty elements from array
        Dim tempStr As String = String.Join("'", campusGroupArray)
        campusGroupArray = Nothing
        campusGroupArray = tempStr.Split(New Char() {"'"c}, StringSplitOptions.RemoveEmptyEntries)

        Dim MySearchResult As SearchResultCollection

        'Loop through OU's until members are found
        Do
            MyDirectorySearcher.Filter = ("(&(objectCategory=person)(memberof=cn=" & searchStr & ", ou=groups, ou=" & campusGroupArray(j) & ", dc=.., dc=.., dc=.., dc=..))")

            MyDirectorySearcher.SearchScope = SearchScope.Subtree
            MyDirectorySearcher.PropertiesToLoad.Add("cn")

            MyDirectorySearcher.Sort.Direction = System.DirectoryServices.SortDirection.Ascending
            MyDirectorySearcher.Sort.PropertyName = "cn"

            MySearchResult = MyDirectorySearcher.FindAll()
            j += 1
        Loop Until MySearchResult.Count > 0 Or j > campusGroupArray.Length - 1


        Dim myTable As New DataTable
        Dim colName As String

        'Create columns in table
        For Each colName In MyDirectorySearcher.PropertiesToLoad
            myTable.Columns.Add(colName, GetType(System.String))
        Next

        Dim result As SearchResult

        'Fill table
        For Each result In MySearchResult
            Dim dr As DataRow = myTable.NewRow()
            For Each colName In MyDirectorySearcher.PropertiesToLoad
                If result.Properties.Contains(colName) Then
                    dr(colName) = CStr(result.Properties(colName)(0))
                Else
                    dr(colName) = ""
                End If
            Next
            myTable.Rows.Add(dr)
        Next
        Return myTable
    Catch ex As Exception
        Return Nothing
    End Try
End Function

Answer 1

You can loop through any number of OU you want - but you need to define the list of properties to load (the .PropertiesToLoad property) once before your loop starts (this really applies to all the properties that aren't going to change between calls to the Directory Searcher - like search scope, the sort direction etc - there's absolutely no point in setting those for each iteration - set them once and be done with it.

' define the list of properties to load for each search result ONCE before the loop
MyDirectorySearcher.PropertiesToLoad.Add("cn")
MyDirectorySearcher.SearchScope = SearchScope.Subtree
MyDirectorySearcher.Sort.Direction = SortDirection.Ascending
MyDirectorySearcher.Sort.PropertyName = "cn"

 ' *THEN* Loop through OU's until members are found
Do
    MyDirectorySearcher.Filter = ("(&(objectCategory=person)(memberof=cn=" & searchStr & ", ou=groups, ou=" & campusGroupArray(j) & ", dc=.., dc=.., dc=.., dc=..))")

     MySearchResult = MyDirectorySearcher.FindAll()
     j += 1
Loop Until MySearchResult.Count > 0 Or j > campusGroupArray.Length - 1

The PropertiesToLoad just defines the list of properties that the search results will contain - this is the same list for each of the searches, and can only contain the property name (e.g. cn) once.