Data Reader with Active Directory Group

saidev

Active member
Joined
Dec 22, 2005
Messages
27
Programming Experience
1-3
Hi Guys,

I have 25 users in 1 AD group. i have 50 users in one of the Sql Server table. I want to compare the AD Group users with Sql table users and add to the group which do not exist in the AD group. Here is my code. Can you please modify my code?

Appreciate your help. Thanks


VB.NET:
  Dim result = adSearch.FindOne

                If result IsNot Nothing Then
                    Dim members = result.Properties("member")
                    Dim sql As String
                    sql = "select Distingname from TEMPAD"
                    Dim myconn As New SqlConnection(CONNECTIONSTRING)
                    Dim mycommand As New SqlCommand(sql, myconn)
                    myconn.Open()

                    Dim dbreader As SqlDataReader = mycommand.ExecuteReader

                    For Each member In members

                        While dbreader.Read
                            If member <> dbreader("Distingname") Then
                                group.Properties("member").Add(dbreader("Distingname"))
                                group.CommitChanges()

                            End If
                        End While
                        


                    Next
                End If
 
How about we give you an idea of what's wrong with your current code and then you modify it for yourself?

Think about how a data reader works. It provides read-only, forward-only access to the result set of a query. Each time you call Read it will advance to the next record until there are no records left, at which point it will return False. Now, look at your code and how you're using the data reader. On the first iteration of the For Each loop you have a While loop that iterates until While returns False, i.e. there are no more records to read. Once the While loop completes you go on to the second iteration of the For Each loop. You already know that the data reader has read through the entire result set so what exactly are you expecting that While loop to do the second time and any time after that?

Logically, your While loop should be on the outside. For each record you read from the database, you check whether that user is in the group and, if not, you add them. Only once you have done all the checking and adding do you commit the changes, once and once only.
 
Instead of making this comparison on code side, why don't you do this in SQL Server environment on database level.
You can just join two tables and you will have matched and unmatched users on both tables easily
 
Back
Top