Listing the users in a group

Here’s a stored procedure that has two uses. When sp_helpgroup doesn’t have a parameter, it will return a list of the groups in the database, as shown on the following page.

sp_helpgroup

Group_name                     Group_id 
------------------------------ -------- 
Admin                          16385    
public                         0        
Users                          16384    

When it’s called with a parameter that is a group name, sp_helpgroup will list the users in that group, as shown in this example:

sp_helpgroup Users

Group_name          Group_id      Users_in_group         Userid 
------------------- --------      ---------------------- ------ 
Users               16384         ColinT                 3      
Users               16384         GlennM                 5      
Users               16384         SallyG                 6      

You can use the sp_helpgroup procedure in your Form_Load event to populate the TreeView control:

    ' Retrieve the groups in the database...
    strSQL = "sp_helpgroup"
    Set rsGroups = conPiConnection.OpenResultset(strSQL, _
        rdOpenForwardOnly, rdConcurReadOnly, rdExecDirect)

    ' And the users in those groups...
    strSQL = "sp_helpgroup ?"
    Set qryUsers = conPiConnection.CreateQuery("UsersInGroup", _
        strSQL)

    Do Until rsGroups.EOF
        Set objNode = trvUsers.Nodes.Add(, , _
            rsGroups!group_name, rsGroups!group_name, "Group")

        qryUsers.rdoParameters(0) = rsGroups!group_name

        If rsUsers Is Nothing Then
            Set rsUsers = qryUsers.OpenResultset( _
                rdOpenForwardOnly, rdConcurReadOnly)
        Else
            rsUsers.Requery
        End If

        Do Until rsUsers.EOF
            Call trvUsers.Nodes.Add(objNode, tvwChild, _
                rsUsers!users_in_group, rsUsers!users_in_group, "Person")
            rsUsers.MoveNext
        Loop

        rsGroups.MoveNext
    Loop

    rsUsers.Close
    qryUsers.Close
    rsGroups.Close

First retrieve the list of groups; then, as you add each one to the TreeView control, retrieve all the users in that group and add them as children of the group. Because you are going to run the inner query many times, set it up as an rdoQuery and merely change its parameter each time around the loop. For this reason, you call the OpenResultset method only once. For all subsequent calls, call the Requery method instead.