Build string in VBscript based on array of groups

Question

I've got an ADO query (ADODB.Recordset) in VBscript that will return a result like below:

nId wstrName              nParentId
0   Managed computers     2
1   Unassigned computers  NULL
2   Master Server         NULL
3   pseudohosts           NULL
5   Server 2              0
8   Group100              5
10  Group22               5
11  Group47               5
13  Group33               5
14  Group39               5
15  Group11               5

I need to build a complete location string based on the result when I know the top level group ID.

E.g. if the top level group ID is 11 then the complete location string will be "Master Server/Managed computers/Server 2/Group47" after traversing the groups by looking at the "nId" and "nParentId" values.

The number of parent groups can vary, so I need to loop until I reach a group without a parent group. I would also like to avoid making multiple SQL queries so I'm assuming the result should get loaded into an array and then handle the information from there.

What is the best way to handle this?

Thanks in advance :)

Ansgar Wiechers · Accepted Answer · 2013-03-19 23:24:14Z

1

You can work with the recordset as it is. Try this:

groupname = "..."

rs.MoveFirst
rs.Find("wstrName = '" & groupname & "'")
location = rs("wstrName")
parent   = rs("nParentId")
Do Until parent = "NULL"
  rs.MoveFirst
  rs.Find("nId = " & parent)
  location = rs("wstrName") & "/" & location
  parent   = rs("nParentId")
Loop

You may need to adjust the condition of the loop depending on whether the NULL values in your recordset are the string "NULL" or actual Null values.

answered Mar 19, 2013 at 23:24

Ansgar Wiechers

202k27 gold badges286 silver badges363 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Ekkehard.Horner Over a year ago

+1.4 for a solution that does not need an extra data structure; -0.4 for four spurious parentheses.

Ekkehard.Horner · Accepted Answer · 2013-03-19 22:52:50Z

Use a Dictionary to store your tabular data and a recursive function to build the location string.

Demo script:

  Dim dicData : Set dicData = CreateObject("Scripting.Dictionary")
'         nId         wstrName                nParentId
  dicData( 0) = Array("Managed computers"   , 2   )
  dicData( 1) = Array("Unassigned computers", NULL)
  dicData( 2) = Array("Master Server"       , NULL)
  dicData( 3) = Array("pseudohosts"         , NULL)
  dicData( 5) = Array("Server 2"            , 0   )
  dicData( 8) = Array("Group100"            , 5   )
  dicData(10) = Array("Group22"             , 5   )
  dicData(11) = Array("Group47"             , 5   )
  dicData(13) = Array("Group33"             , 5   )
  dicData(14) = Array("Group39"             , 5   )
  dicData(15) = Array("Group11"             , 5   )
  Dim nId
  For Each nId In dicData.Keys()
      WScript.Echo Right(100 + nId, 2), buildLoc(dicData, nId, Array())
  Next

Function buildLoc(dicData, nId, aTmp)
  ReDim Preserve aTmp(UBound(aTmp) + 1)
  aTmp(UBound(aTmp)) = dicData(nId)(0)
  If IsNull(dicData(nId)(1)) Then
     reverseArr aTmp
     buildLoc = Join(aTmp, "/")
  Else
     buildLoc = buildLoc(dicData, dicData(nId)(1), aTmp)
  End If
End Function

Sub reverseArr(aX)
  Dim nUB  : nUB  = UBound(aX)
  Dim nUB2 : nUB2 = nUB \ 2
  Dim i, vt
  For i = 0 To nUB2
      vt          = aX(i)
      aX(i)       = aX(nUB - i)
      aX(nUB - i) = vt
  Next
End Sub

output:

00 Master Server/Managed computers
01 Unassigned computers
02 Master Server
03 pseudohosts
05 Master Server/Managed computers/Server 2
08 Master Server/Managed computers/Server 2/Group100
10 Master Server/Managed computers/Server 2/Group22
11 Master Server/Managed computers/Server 2/Group47
13 Master Server/Managed computers/Server 2/Group33
14 Master Server/Managed computers/Server 2/Group39
15 Master Server/Managed computers/Server 2/Group11

Collectives™ on Stack Overflow

Build string in VBscript based on array of groups

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related