Sending selected items to database table + asp

[LadyDi02]

Hello once again.
Now I am really frustrated. I have an asp page which queries an LDAP server...a simple select of some sort. select name, phonenum FROM "LDAP://dc..........." Select * from blah blah bla. Now the information that get's returned I store into hidden fields after looping. I am putting down the gist of the code

name = objectrecordset.("name") 
<input type = "Hidden" name = "Fname" value = "<%=Name%>"> 
phonenum = objectrecordset.("phoneNumber") 
<input type = "Hidden" name = "Telephone" value = "<%=phonenum%>">

Now on my second page I request the form elements that I selected

FNameRequest = Request.form("Name") 
PNumRequest = Request.form("phonenum")

Hopefully everyone is with me at this point.

Then I do a replace on the variable for my insert since each request is stored as a comman delimited string and then perform a split function

FullNameDesc = Replace(FNameRequest, "'", "''") 
PNumDesc = Replace(PNumRequest , "'", "''") 
strFName = Split(FullNameDesc,", ") 
strPNum = Split(PNumDesc ,", ")

Now after this is where I have the problem..I do my loop

for i = 0 to ubound(strFName) 
.....add insert statement here 
Next

Well it does insert into the database however the data that gets insert is not correct for each user.
For example Paul and his phone number get inserted correctly but Mary get's Paul's phone number and Jim get's Mary's phone number and so forth. There is no correlation between name and phonenumber for each user. Any ideas what I should do? Could you please provide some code for a fix that will guide me in the right direction. If I can't see it visually I will not understand it. Thanks gurus.

 

[LadyDi02]

This looks like it is working perfectly DotNetGnat. Now I am extremely happy. Obviously it would take me a lot longer to check all the data but from what I am testing this is perfect. I really really appreciate this.
Could I just ask two more questions...please
All things the same, I want to delete all the records in the the table first before it does the insert of the arrays. I want this to happen every single time the user clicks submit. How can I delete the contents of the table and then insert all in one click. I'm assuming I need a

delete * from tablex

But where does this go and do I give it the same variable name ....how do I open another connection or do I?

sSQL

Sorry just not sure.


Also since my end goal is just to extract the contents of an LDAP server into a database table, did I do this the hard way? What I mean by this is there an easier way to extract the contents into a databse table. Did I have to go the route of creating a form with a submit or can asp pages be run from a command line or can I just give the user an icon and he or she can click it and it will perform the actions I requested. I know ASP.NET can create an executable but can classic ASP do this? If so any ideas how?.

Thanks again for all your hard work. You really taught me a lesson in arrays and debugging arrays. Mucho Gracias

 

[DotNetGnat]

no problem...glad to assist...and we all learn something new everyday...i am gald it worked out for you...

now to answer your questions...

1) to delete the records from the table, yes you would need to do the following...

sql1 = "Delete from mytable"
con.execute(sql1)

go ahead and declare a new variable to hold the sql string, but you can use the same connection object...and you have to do this at the click event

2) I guess your method of extracting data from LDAP server and storing it in one of your database tables is OK.

-DNG

 

[LadyDi02]

DotNetGnat,

I'm just looking over the data again and it's still misaligned. Any other suggestions?

 

[DotNetGnat]

without knowing the actual data you are working with, i am not sure what else i can suggest you...

-DNG

 

[LadyDi02]

DotNetGnat,

Sorry for the delay but I was trying to see if I could come up with something unfortunately I'm at a loss. Well what I think I will do is exclude the given name. For some reason the fullname and email seem to correspond but the given name get's corrupted. So what I have chosen to do is include manager name. Would you happen to know how to split this value so that I may just get the manager.

CN=David Smith,OU=Users,OU=HQ,DC=xxx,DC=xxx

The manager would be the name after the CN= and then ends before the 1st comma. So I would need to capture all the manager names for each user. Could you help with this split function. Unless you know where in LDAP I can just capture the managers name without all the other variables. I think this will be good enough for me. Thanks very much again for all your help DotNetGnat

 

[DotNetGnat]

do this...

mystring = "CN=David Smith,OU=Users,OU=HQ,DC=xxx,DC=xxx"

dim intcommaposition, intequalsignposition, strmanagername

intequalsignposition = instr(mystring, "=")
intcommaposition = instr(mystring, ",")

strmanagername = MID(mystring, intequalsignposition,intcommaposition)

-DNG

 

[LadyDi02]

O.k so I tried to take your code and came up with the following

mystring = Request.Form("GivenName")
 
intequalsignposition = instr(mystring, "=")
intcommaposition = instr(mystring, ",")
 
strmanagername = MID(mystring, intequalsignposition,intcommaposition)
 
'response.write strmanagername

I then place the variable in the insert as follows:

For i = 0 to Ubound(strFullName)
   sSQL = "INSERT into LDAP_Users(FullName, Email, GivenName)"
   sSQL = sSQL &  "VALUES ('" & strFullName(i) &"', '"& strEmailName(i) &"', '" & strmanagername(i) &"')"
   'objConn.Execute sSQL
 
   response.write sSql & "<BR>"

And received an error type mismatch on the insert line

sSQL = sSQL &  "VALUES ('" & strFullName(i) &"', '"& strEmailName(i) &"', '" & strmanagername(i) &"')"

I also did a response write before the loop

'response.write strmanagername

and received back just an equal sign. Any ideas?

 

[DotNetGnat]

hmm...i had couple errors in the previous code that i posted...i was just giving you an idea...any ways try soemthing like this:

mystring = Request.Form("GivenName")
 
intequalsignposition = instr(mystring, "=")
intcommaposition = instr(mystring, ",")
 
strmanagername = MID(mystring, intequalsignposition+1,intcommaposition-intequalsignposition-1)

For i = 0 to Ubound(strFullName)
   sSQL = "INSERT into LDAP_Users(FullName, Email, GivenName)"
   sSQL = sSQL &  "VALUES ('" & strFullName(i) &"', '"& strEmailName(i) &"', '" & strmanagername &"')"
   'objConn.Execute sSQL
 
   response.write sSql & "<BR>"

-DNG

 

[DotNetGnat]

please note that the above script gives you same manager name all the times...you need to use the string manipulation code i proivided at the array level to get the different manager names...

post back if you have problems doing that...

-DNG

 

[LadyDi02]

hahah. I was just going to write back. This is part I am confused at. I should have mentioned in my previous post that I got the split to work in the previous code using

mystring = Request.Form("Name")
intequalsignposition = instr(mystring, "=")+ 1
intcommaposition = instr(mystring, ",")-4
 
strmanagername = MID(mystring, intequalsignposition,intcommaposition)

and was not able to loop the names and only received the first user. So to answer your question yes I will need help on this.
I rather use your code though because I haven't really tested mine.

 

[DotNetGnat]

lets say the name of your array that holds manager names for each employee is [red]myarray[/red]

then you need to do this:

For i = 0 to Ubound(strFullName)
   sSQL = "INSERT into LDAP_Users(FullName, Email, GivenName)"
   sSQL = sSQL &  "VALUES ('" & strFullName(i) &"', '"& strEmailName(i) &"', '" & [red]MID(myarray(i),instr(myarray(i),"=")+1, instr(myarray(i),",")-instr(myarray(i),"="))[/red] &"')"
   'objConn.Execute sSQL
 
   response.write sSql & "<BR>"

-DNG

 

[LadyDi02]

DotNetGnat,

I implemented the code that you provided and i declared an array.

dim myarray
myarray=request.form ("givenname")
[\code]

Once i run this i get a type mismatch error in the insert statement

I think it has to do with the midfunction

 

[DotNetGnat]

can you tell me how does the output look when you do this

response.write request.form ("givenname")

-DNG

 

[LadyDi02]

It's one long string as such. This is just a part of it

, , , , , , , , , , , , CN=Joe Smith,OU=Users,OU=HQ,DC=xxx,DC=xxx, , , , , , , , , , , , , , , , , , , , CN=John Bear,OU=Users,OU=HQ,DC=xxx,DC=xxx, CN=Jimmy Bean,OU=Users,OU=HQ,DC=xxx,DC=xxx, CN=Ron Howard,OU=Users,OU=HQ,DC=xxx,DC=xxx, CN=Free Willy,OU=Users,OU=HQ,DC=xxx,DC=xxx, CN=Tanya Harding,OU=Users,OU=HQ,DC=xxx,DC=xxx, etc etc etc

 

[DotNetGnat]

ok its getting ugly...i think you can use "name" attribute to directly get the common name or manager's name...look into that...

-DNG

 

[LadyDi02]

DotNetGnat,


Sorry for the delay. I have been searching for the names table that you have mentioned but have not found anything. I have however found information on regular expressions. I have started another thread because I wasn't sure if I could add to this. Sorry if I should have but if you could have a look at the other thread that would be great. Thanks again for your help.