asp looping issues... 1

[LadyDi02]

I am querying an LDAP server with a simple select and storing the result set in hidden variables. I then pass those hidden variables to another page, replace and apostrophes in the data and then split the data into arrays so that they will insert into a database. A sample of some of the code is listed below. This is what gets passed on the second page. ie FullNameDesc, EmailDesc, ManagerDesc and AliasDesc have a replace function on them to include double quotes for any words that have apostrophes.

Code:
strFullName = Split(FullNameDesc,", ")
strEmailName = Split(EmailDesc,", ")
strManagerName = Split(ManagerDesc,", ")
strAliasName = Split(AliasDesc,", ")

Now the Manager name is what I am currently working on and have issues. I am using a regex expression inorder to remove all the words/characters from the data except for the name. I have done this successfully with the following code.

Code:
set objRE = new RegExp
with objRE
..IgnoreCase = true
..Global = true
..pattern = "CN=(.*?),"
end with
set Names = objRE.execute(ManagerDesc)

Then I strip out the CN= and replace any apostrophes with double quotes so that they will insert correctly into a database as follows.

Code:
strName = Replace(Names, ("CN="), " ")
strName = Replace(Names, ",", " ")
strName = Replace(Names, "'", "''")

Then I perform my insert. However I am not sure how to add the regex value to the insert. Can anyone help construct the loop for the regex. If I exclude the variable(the manager variable/field) my insert will work. However, I am not how to insert the contents of the regex expression into my insert. This is the code I do have but receiving an error:

Code:
For i = 0 to Ubound(strFullName)
sSQL = "INSERT into Table (FullName, Email, Manager, Alias)"
sSQL = sSQL & "VALUES ('" & strFullName(i) &"', '"& strEmailName(i) &"', '" & strName(i) &"', '" & strAliasName(i) &"')"
'objConn2.Execute sSQL
NEXT

Code:
Error Type:
Microsoft VBScript runtime (0x800A01C2)
Wrong number of arguments or invalid property assignment
/activedirectory/testpass.asp, line 65

I do not need to use the regex expression for any of my other variables except the managerName variable this is why you see only one instance of regex and not multiples.

Please could you telling me where I am going wrong, possibly modifying my code. Hopefully it can be done or else I will resort to removing the reg ex and insert the whole managername string into the database.

Thanks again.

 

[DotNetGnat]

put your regex stuff inside a function and call the function...something like this:

Function [red]getManagerName[/red](strmanager)
set objRE = new RegExp 
with objRE 
.IgnoreCase = true 
.Global = true 
.pattern = "CN=(.*?)," 
end with 
getManagerName = objRE.execute(strManager)
set objRE = nothing 
End Function

and then in the sql statement you can do the following...

For i = 0 to Ubound(strFullName) 
  sSQL = "INSERT into Table (FullName, Email, Manager, Alias)" 
  sSQL = sSQL &  "VALUES ('" & strFullName(i) &"', '"& strEmailName(i) &"', '" & [red]getManagerName([blue]strManagerName(i)[/blue])[/red] &"', '" & strAliasName(i)  &"')" 
  'objConn2.Execute sSQL 
NEXT

hope it works for you...

-DNG

 

[Tarwn]

You may want to check the value that is coming back from your objRE.execute(ManagerDesc) call. I don't believe this is doing what you were looking for it to do.

When I previously did something like this I actually didn't a second AD call to bring back the user information for the manager (using the value of the manager field) so that I could store their name and email. If you already have the logic to query for an LDAP user, why not put all of the logic for that inside a function so you can re-use it. You could then define your own use object in ASP to hold the data and instead of setting independent variables, create a new copy of that object, send it back, then query for a second object to hold the managers info.

Something like:

Class UserInfo
   Public FullName, Email, Alias, Manager
End Class

'I don't know if your using SQL or actual LDAP queries, but those would go in the funciton call below
UserInfo theUser = YourLdapFunc(searchString)
UserInfo theMgr = YourLdapFunc(mgrSearchString & theUser.Manager)

Obviously this would get a bit more complicated if you use arrays for multiple results, but you would be able to separate your logic a little bit as well as re-use the active LDAP connection to retrieve accurate information about the managers in question.

-T

 

[tsuji]

[1]>strName = Replace(Names, ("CN="), " ")
etc...
You cannot do that. Names is a collection. If you want the replace persists, do this.
[tt]
dim strName() 'again I would point out strName is not a str, this is my second time to point this cruel fact to this op
redim strName(Name.count-1)
for i=0 to Name.count-1
strName(i)=Name(i)
strName(i)=replace(strName(i),"CN=", " ")
strName(i)=replace(strName(i), ",", " ")
strName = Replace(Names, "'", "''")
next
[/tt]
[3] With that, you have to be sure cn be in uppercase. The regexp use ignorecase correctly, but the idea in this realization is not put through to the end in the replace in [2].

[4] Then you have to make sure ubound of all the components be consistently the same as ubound(strFullName).

[5] If you know enough ldap, rdn (cn=...) itself can contain comma (,) in the escape form with comma still exposed bare. Hence, there is a strong limitation imposed on this algorithm that cn does not contain such kind of names.

 

[tsuji]

Amendment
The corresponding line in my post should be read like this instead, as a matter of course.[tt]
[red]'[/red]strName = Replace(Names, "'", "''")
strName(i) = Replace(strName(i), "'", "''")
[/tt]

 

[LadyDi02]

O.k I'm not really sure who to reply too but Tsuji says it can;t be done so I used his code. I apologize if I should be using DotnetGnat or Tarwn's code.

Tsuji this is what I have done.

I tried to correct what you said and this is what I think the code should be but now I am getting another error:

Subscript out of range: '6782'
/activedirectory/testpass.asp, line 76

The code is as follows:

set objRE = new RegExp 
with objRE 
.IgnoreCase = true 
.Global = true 
.pattern = "CN=(.*?)," 
end with 
set Names = objRE.execute(ManagerDesc) 
 
redim strName(Names.count-1)
for i=0 to Names.count-1
    strName(i)=Names(i)
    strName(i)=replace(strName(i),"CN=", " ")
    strName(i)=replace(strName(i), ",", " ") 
    strName(i) = Replace(strName(i), "'", "''")
next
 
For i = 0 to Ubound(strFullName) 
  sSQL = "INSERT into Table (FullName, Email, Manager, Alias)" 
  sSQL = sSQL &  "VALUES ('" & strFullName(i) &"', '"& strEmailName(i) &"', '" & strName(i) &"', '" & strAliasName(i)  &"')" 
  'objConn2.Execute sSQL 
  response.write sSQL & "<BR>"
NEXT 
response.end

I also added

response.write UBound(strFullName) & "<BR>"
response.write UBound(strEmailName) & "<BR>"
response.write UBound(strManagerName) & "<BR>"
response.write UBound(strAliasName) & "<BR>"

and found that all arrays are equal

11434
11434
11434
11434

I think this line is messing it up and cutting the array short

for i=0 to Names.count-1

Thanks.

 

[tsuji]

>but Tsuji says it can;t be done so I used his code
I just pointed out blatant error of the precise part of your approach. I think the error I pointed out is an immutable fact. But I do not guarantee there may not be other problems such as I mentioned in the other sections ([1], [3], [4], [5]).

Back to the error. Have you put this line before redim?
[tt] dim strName()[/tt]
(Also, you are right to stick to Name[blue]s[/blue]. I had a typo of missing s.)

 

[LadyDi02]

Yes I just added back the

dim strName()

and received

Subscript out of range: '6782'

See I think what is happening is not all strfullnames have a manager so the data returned is only 6782. How do I account for blanks? Maybe I am wrong but I think that is the issue because if I do a count on that I get 6782 however the ubound of the others are 11434.

 

[tsuji]

I've to be off for the day. May be somebody else can step in. In any case, as mentioned in [4], that is a factor you have to make sure of one-one correspondance as far as the data structure is concerned.

 

[LadyDi02]

Thanks Tsuji
Can anyone hlep me on this please. I'm stuck.

 

[Tarwn]

The reason that your original array of manager names (strManagerName) has 11434 items, and your new array (strName) has fewer entries is because your running your regular expression against the list of items (ManagerDesc) and the blank entries will not match your expression.

As far as I can tell, you don't need the block with the regular expression at all. You should get what your looking for by doing this:

[COLOR=green]'This section belongs between the code you didn't include and the assignments below
FullNameDesc = Replace(FullNameDesc,"'","''")
EmailDesc = Replace(EmailDesc,"'","''")
ManagerDesc = Replace(Replace(ManagerDesc,"'","''"),"CN=","")
AliasDesc = Replace(AliasDesc,"'","''")[/color]

strFullName = Split(FullNameDesc,", ")
strEmailName = Split(EmailDesc,", ")
strManagerName = Split(ManagerDesc,", ")
strAliasName = Split(AliasDesc,", ") 

[s]set objRE = new RegExp[/s]
[s]with objRE[/s]
[s]..IgnoreCase = true[/s]
[s]..Global = true[/s]
[s]..pattern = "CN=(.*?),"[/s]
[s]end with[/s]
[s]set Names = objRE.execute(ManagerDesc) [/s]

[s]redim strName(Names.count-1)[/s]
[s]for i=0 to Names.count-1[/s]
[s]    strName(i)=Names(i)[/s]
[s]    strName(i)=replace(strName(i),"CN=", " ")[/s]
[s]    strName(i)=replace(strName(i), ",", " ")[/s]
[s]    strName(i) = Replace(strName(i), "'", "''")[/s]
[s]next[/s]
 
For i = 0 to Ubound(strFullName)
  sSQL = "INSERT into Table (FullName, Email, Manager, Alias)"
  sSQL = sSQL &  "VALUES ('" & strFullName(i) &"', '"& strEmailName(i) &"', '" & [COLOR=green]strManagerName(i)[/color] &"', '" & strAliasName(i)  &"')"
  'objConn2.Execute sSQL
  response.write sSQL & "<BR>"
NEXT
response.end

The 4 lines I added to the top protect you against single quotes, with an addition to the manager name to get rid of all the CN='s. We split all 4 strings like you were originally doing to create 4 arrays that will preserve any blank manager values from the original string. We then use that manager array in the SQL instead of recreating the array.
For users that didn't have managers, there will be an empty string inserted in the DB, for users that did have managers, they will get the CN string without the CN= prefix.

The regular expression is actually counter-productive because you then lose any possibility of relating the manager names to the user they manage, having deleted all the empty managers for users that don't have a manager. By doing the necessary replacements before converting the string into an array, you don't have to go through the inefficient process of recreating the array one element at a time.
Replacing the single quotes in the other three strings is a bonus and will be more efficient before splitting into arrays then when building the SQL statement.

I did not read your previous thread, so if there is some piece of requirements not filed by these corrections, please let us know.

-T

 

[LadyDi02]

Tarwn,

I think your almost there Yes there are some requirements that you missed. The replace statement that you used on manager field is cutting out the CN= correctly however now I am left with

Joe Smith,OU=Users,OU=HQ,DC=xxx,DC=XXX,

Is there any way to loop through the manager field and for every string(split) remove the comma and everything after so that just the managers name is showing

Joe Smith

Thank you so much for this insight.

 

[DotNetGnat]

after getting this

Joe Smith,OU=Users,OU=HQ,DC=xxx,DC=XXX,

you need to split again on comma and get the first item of the array ( example: myarray(0) )

-DNG

 

[LadyDi02]

Could you please explain that further or show me what you mean. I don't understand. Could you use the existing code please.

you need to split again on comma and get the first item of the array ( example: myarray(0) )

 

[LadyDi02]

Yes sorry to be in such a rush.

Tarwn can you or anybody explain the last post to me please

 

[Tarwn]

Basically DNG was suggesting that you loop through the strManagerName array, split each entry on the ",", and then assign the first item back to the array.

So basically something like this:

strFullName = Split(FullNameDesc,", ")
strEmailName = Split(EmailDesc,", ")
strManagerName = Split(ManagerDesc,", ")
strAliasName = Split(AliasDesc,", ")

Dim arrTemp, ctr
For ctr = 0 to UBound(strManagerName)
   'if it has a comma, split the string and pull out fist piece
   If InStr(strManagerName(ctr),",") > 0
      arrTemp = Split(strManagerName(ctr),",")
      strManagerName(ctr) = arrTemp(0)
   End If
Next

Another option with that same loop would be to use a Left statement and another InStr instead of the Split and temporary array variable.


An additional option would be to add back in the Reqular Expression, now that I know what you were doing with it
Instead of using it to pull out a set of matches, we could instead use it to strip out all of the extra values we do not want to keep.

FullNameDesc = Replace(FullNameDesc,"'","''")
EmailDesc = Replace(EmailDesc,"'","''")
ManagerDesc = Replace(ManagerDesc,"'","''")
AliasDesc = Replace(AliasDesc,"'","''")

Dim regex
Set regex = New RegExp
//pattern explained below
regex.Pattern = "CN=([^,]+)(,OU=[^,]+)+(,DC=[^,]+)+"
//Replace all full CN strings with just the name in the first group
ManagerDesc = regex.Replace(ManagerDesc,"$1")

strFullName = Split(FullNameDesc,", ")
strEmailName = Split(EmailDesc,", ")
strManagerName = Split(ManagerDesc,", ")
strAliasName = Split(AliasDesc,", ")

The pattern I made breaks down like so (I don't know how much you know about regular expressions, so if I go to in depth I apologize):
"CN=([^,]+)"
Here we are saying that we want to start matching with the characters "CN=". After that has to come 1 or more characters that are not a comma (in this case, the name of the manager). By placing parentheses around that section with the manager name, we are telling the regexp object to keep this submatch so that we can later reference it. When keeping submatches, they are numbered in the order they start in the string, so this would be submatch 1.
"(,OU=[^,]+)+"
1 or more repitions of: a comma followed by OU= followed by 1 or more characters that are anything but comma
"(,DC=[^,]+)+"
1 or more repitions of: a comma followed by DC= followed by 1 or more characters that are anything but comma

When we go to do our replace this should match any string that has a CN then 1 or more OU's and one or more DC's. it is possible to combine the OU and DC section, but I wanted to keep them apart a little a clearer for this example.

ManagerDesc = regex.Replace(ManagerDesc,"$1")
Basically this replace statement is saying "Replace all matches inside ManagerDesc with the contents of sub-match 1"
So what we should get is all full CN strings replaced with their manager's name, but all empty values in the ManagerDesc string will be untouched because they don't match the pattern we are replacing on.

Once we finish the replace we can go ahead and continue to split the value as we were originally doing.


I think this method is going to be much more efficient than the loop that DNG suggested. I'm not sure if it is faster if you just needed to extract one name, but if you needed to extract 20 or 30 it is likely the loop method would be much slower. The regular expression will have more work to do as well, but will not decrease in efficiency as fast as a for loop with a split.

-T

 

[LadyDi02]

Tarwn,

Wow very concise explanation and I really appreciate this Please excuse my ignorance but no I do not know much about regular expressions but all my reading pointed to using them. As for the code you provided, sorry bear with me please I am still lost on something. I have included your regex expression and have placed my split and replace variables at the top of my page like I do with all my variables.

Dim regex
Set regex = New RegExp
'pattern explained below
regex.Pattern = "CN=([^,]+)(,OU=[^,]+)+(,DC=[^,]+)+"
'Replace all full CN strings with just the name in the first group
ManagerDesc = regex.Replace(ManagerDesc,"$1")

Now after the regex expression should I be doingnot sure what i should be doing)

For i = 0 to Ubound(strFullName) 
  sSQL = "INSERT into Table (FullName, Email, Manager, Alias)" 
  sSQL = sSQL &  "VALUES ('" & strFullName(i) &"', '"& strEmailName(i) &"', '" & ManagerDesc(i) &"', '" & strAliasName(i)  &"')" 
  'objConn2.Execute sSQL 
  response.write sSQL & "<BR>"
NEXT 
response.end

[code]

Because if I should be well then I am getting an error
[code]
Type mismatch line 60

This is line 60
sSQL = sSQL &  "VALUES ('" & strFullName(i) &"', '"& strEmailName(i) &"', '" & ManagerDesc(i) &"', '" & strAliasName(i)  &"')"

Sorry if I misunderstood but I think I went back to square one. Your help is appreciated.

 

[DotNetGnat]

you should use strManagerName(i) instead of ManagerDesc(i)

-DNG

 

[LadyDi02]

Sorry for the confusion,

I should have mentioned that I did try both variables. I tired ManagerDesc(i) and received the error I mentioned and then I did try strManagerName(i) and my resultset actually shows

CN=Joe Smith,OU=Users,OU=HQ,DC=xxx,DC=XXX,

The regex did not do anything.

 

[DotNetGnat]

LadyDi02, I am sorry to say but i would suggest reading and understanding the suggestions made in this forum instead of just copy-pasting them in your code...

any ways, make sure that your code looks as follows:

FullNameDesc = Replace(FullNameDesc,"'","''")
EmailDesc = Replace(EmailDesc,"'","''")
ManagerDesc = Replace(Replace(ManagerDesc,"'","''"),"CN=","")
AliasDesc = Replace(AliasDesc,"'","''")

Dim regex
Set regex = New RegExp
regex.Pattern = "CN=([^,]+)(,OU=[^,]+)+(,DC=[^,]+)+"
ManagerDesc = regex.Replace(ManagerDesc,"$1")

strFullName = Split(FullNameDesc,", ")
strEmailName = Split(EmailDesc,", ")
strManagerName = Split(ManagerDesc,", ")
strAliasName = Split(AliasDesc,", ") 

 
For i = 0 to Ubound(strFullName)
  sSQL = "INSERT into Table (FullName, Email, Manager, Alias)"
  sSQL = sSQL &  "VALUES ('" & strFullName(i) &"', '"& strEmailName(i) &"', '" & strManagerName(i) &"', '" & strAliasName(i)  &"')"
  'objConn2.Execute sSQL
  response.write sSQL & "<BR>"
NEXT
response.end

-DNG