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.

 

[onpnt]

I think the key debug method for you to do is find out what you have in here

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

or at leastit will help me understand what you are doing

So what is outputted when you do this

FNameRequest = Request.form("Name")
PNumRequest = Request.form("phonenum") 
Response.Write FNameRequest & "<br />"
Response.Write PNumRequest & "<br />"
Response.End

[sub]____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done[/sub]

 

[DotNetGnat]

may be posted the question wrong but you need to have this:

[s]FNameRequest = Request.form("Name")[/s]
FNameRequest = Request.form("FName")
 
[s]PNumRequest = Request.form("phonenum")[/s]
PNumRequest = Request.form("Telephone")

you access the form field by its name or id not the value...

and the next thing i would suggest is response.write your form variables to see if they are holding the right values as onpnt suggested...

-DNG

 

[bslintx]

good catch dng , however, how is data getting into the database if she is requesting name and/or phonenum if both are empty?


perhaps you should post the sql statement too

 

[LadyDi02]

Thanks for the replies everyone. Here is the results after the response.write portion. I get a long string of characters separated by a comma. Too big to display. But what happens is I get all the usersnames and then all the phone numbers after all the usernames displayed in one string. So I don't know which phone number is assocaited to which user.

ie.

john smith, frank o'neil, Larry Cole, Tina Sweet, sherry sinnger,505-654-3214, 619-456-7598,915-658-5432 etc ect.

Therefore I don't know what phone number is associated to what username. Hope that helps. Also, if I add more fields to the select it just continues on with adding that field to the end of the phone numbers etc. This is why in my previous code I have showed the split and replace functions.

 

[DotNetGnat]

can you post your SQL query...we can rewrite your sql query to get the desired results...do it at the query level would be more efficient than doing it at the asp page level...

-DNG

 

[LadyDi02]

Please remember that data is coming from an LDAP server. My end goal is to get the data from the ldap server that I need into an oracle database. So here is the info you requested.

objCommand.CommandText = _ 
   "SELECT Name,mail, givenname FROM 'LDAP://dc=xxx,dc=xxx" & _ 
       "WHERE objectCategory='user' order by Name"

 

[DotNetGnat]

i dont see any phone number in that query...also can you tell us what response.write statement resulted in the following ouput...

john smith, frank o'neil, Larry Cole, Tina Sweet, sherry sinnger,505-654-3214, 619-456-7598,915-658-5432

-DNG

 

[LadyDi02]

My apologizes,

I was using the data coming back as an example of the true data minus the phone number.

If you want me to be accurate the data looks as follows

Jim Bean, Joe Smith, Frank Russo, Chuck Brown, Lina Seed, jbean@hotmail.com, jsmith@yahoo.com, frusso@yahoo.com, cbrown@riverstar.net, lseed@gmail.com

This response.write produced this:

Response.Write FNameRequest & "<br />"
Response.Write EmailRequest & "<br />"
Response.End
[code]
 
Please also not that these requests are coming from hidden variables that I mentioned in my previous code minus the hidden variable for the phone number:)  My select list will grow to include phone number and all other variables that the objectclass user from the LDAP server supports.  However, I wanted to make sure that I could get some data into the database.  Sorry if I was incorrect with the select of the phone number but I have been playing around with different selects and obvious removed the phone number in my tests but  this is the gist of the code whether it be select phone number or select email it won't matter because it all gets captured as a string of comma delimited text where there is no relationship between the select elements.  Thanks again.

 

[DotNetGnat]

ok if i understand you correctly...here is what yopu have so far

Response.Wirte FNameRequest -> gives you

Jim Bean, Joe Smith, Frank Russo, Chuck Brown, Lina Seed

now let say you have to insert all of these in the database table...you need to do this

myarray = split(FNameRequest,",")
for i = 0 to ubound(myarray)
sql = Insert into mytable(Name) values ('"&myarray(i)&"')
con.execute(sql)
next

-DNG

 

[DotNetGnat]

similarly you can deal with the other variables...but in case you want to do the insert of all the variables at a single time then do it something like this...

create as many arrays as there are variables....

myarray1 = split(name, ",")
myarray2 = split(phonenum, ",")
myarray3 = split(email,",")

then you can do...

for i = 0 to ubound(myarray1)
sql = Insert into mytable(Name,Phonenum, email) values ('"&myarray1(i)&"','"&myarray2(i)&"','"&myarray3(i)&"')
con.execute(sql)
next

or you can use the multi-dimension arrays if you are comfortable working with them...

-DNG

 

[LadyDi02]

Actually DotNetGnat,

Your code is exactly what I did however what is happening is some of the users email address and given names are not lining up or being associated to the FNAMEREQUEST. So if you recall I am getting

Jim Bean   jbean@hotmail.com   Jim
Joe Smith  jbean@hotmail.com  Jim
Frank Russo jsmith@yahoo.com Joe

Looking at the values I just presented Joesmith is acquiring Jim Bean's email address. Just to double check that there is nothing wrong with the data rather then inserting I tried to display my data in an HTML table to see if there is something wrong with the data. Well nothing is wrong with it as it matches the way it should match to each user. However it goofs up during the insert or something. This is the issue, I don't know where it is messing up or why it is messing up. If I response write the SQL statement before submission to the database I get.

INSERT into Tablex(FullName, Email, GivenName)VALUES ('Jim Bean', 'jbean@hotmail.com', '', 'Jim')
INSERT into Tablex(FullName, Email, GivenName)VALUES ('Joe Smith', 'jbean@hotmail.com', '', 'Jim')
INSERT into Tablex (FullName, Email, GivenName)VALUES ('Frank Russo', 'jsmith@yahoo.com', '', 'Joe')

Please not that it is not doing this for every record some records are entered correctly why others are not lining up. It's almost like when it tries to perform an insert it misses some data but I ensured all the data is there.

Just for the record, in my code I have done similarily what you have done. I have done the following

For i = 0 to Ubound(strFullName)
   sSQL = "INSERT into Tablename (FullName, Email, GivenName,Address)"
   sSQL = sSQL &  "VALUES ('" & strFullName(i) &"', '"& strEmailName(i) &"', '" & NULL &"', '" & strStreetAddress(i) &"')"
   'objConn.Execute sSQL

Hope I haven't confused anyone. Thanks again

 

[DotNetGnat]

ok so...you are getting the wrong values for some records before the insert happens...

response.write on the following variables...

strFullName
strEmailName
strStreetAddress

then create arrays for the aboe 3 variables splitting them at the ","...

then for debugging purposes...
1) check the datalength of each array...they all should be same
2) response.write out the array items separately just to check if you have all the data in the right place...

please post back your findings...

-DNG

 

[LadyDi02]

Sorry now I am bit confused.

I am doing my split's at the top just before my for loop

strFullName = Split(FullNameDesc,", ")
strEmailName = Split(EmailDesc,", ")
strGivenName = Split(GivenNameDesc,", ")

I can't seem to response.write those variables. I get error...I'm assuming because it is an array I would need to conduct the response.write in the loop.

Response object, ASP 0106 (0x80020005)
An unhandled data type was encountered.

When I response out the arrays I'm aasumming I would need to do this in my for loop. If I write them out separately

Response.Write strFullName(i) & "<BR>"

the data returned looks fine to me. I do get all the usernames correctly. Mind you there is thousands. Also, like I said if I put this in a HTML table with all the necessary variables the data get's displayed properly. No issues there. Sorry not sure if I provided everything you needed.

How do I check the length of my arrays? I thought my arrays were my variables I showed just before my for loop as noted below.

strFullName = Split(FullNameDesc,", ")
strEmailName = Split(EmailDesc,", ")
strGivenName = Split(GivenNameDesc,", ")

If I response write those in my loo remember I get all the username separated by commas then the email addresses separated by commas then the givenames separated by commas but again no association. Please let me know what you mean? Thanks again

 

[DotNetGnat]

do this...

.....
.....

strFullName = Split(FullNameDesc,", ")
strEmailName = Split(EmailDesc,", ")
strGivenName = Split(GivenNameDesc,", ")

response.write UBOUND(strFULLName) & "<br>"
response.write UBOUND(strEmailName) & "<br>"
response.write UBOUND(strGivenName) & "<br>"

....
....

you should get all of them to be equal...after you make sure of that....if you want you can response.write the above arrays to see the items in the array...some thing like this...

for i=0 to UBOUND(strFULLName)
response.write strFULLName(i) & "<br>"
next

you need to check the same for the other two arrays...now you are seeing what you expect...

for i=0 to UBOUND(strFULLName)
'your insert statement here
next

should work just fine...

-DNG

 

[LadyDi02]

Ok I so I have inserted the code you provided

response.write UBOUND(strFULLName) & "<br>" 
response.write UBOUND(strEmailName) & "<br>" 
response.write UBOUND(strGivenName) & "<br>"

and got the results
11621
11621
11623

Then I did

for i=0 to UBOUND(strFULLName) 
response.write strFULLName(i) & "<br>" 
next

and got my list of emails

However, I wasn't sure what I had to do with the others but all I did was replace the strFullName with StrEmailName and then strGivenName. For each one I got results back if that is what you are looking for. Now what should I do? Thanks again

 

[DotNetGnat]

i was asking you to insert the code just for debugging purposes...you actually dont need those extra lines in the code...

i was just making sure that you were getting arrays of same size so that all the names are matched with their corresponding email address and phone numbers...

hope you got what i am trying to say...

now if you see you got a discrepancy in the array lengths you got 11622 items in the full name and email array but you have 11624 items in the given name array...so that makes me think that there is something wrong with the data...

go ahead and do this and see if you get the data inserted correctly...

For i = 0 to Ubound(strGivenName)-2
   sSQL = "INSERT into Tablename (FullName, Email, GivenName,Address)"
   sSQL = sSQL &  "VALUES ('" & strFullName(i) &"', '"& strEmailName(i) &"', '" & NULL &"', '" & strStreetAddress(i) &"')"
   'objConn.Execute sSQL

also i am confused with how many arrays are you using in total...

-DNG

 

[LadyDi02]

DotNetGnat,

My apologizes for the confusion. Lets work with the 3 arrays for now just to make sure everything is working. The 3 are

strFullName = Split(FullNameDesc,", ")
strEmailName = Split(EmailDesc,", ")
strGivenName = Split(GivenNameDesc,", ")

So the insert code should be changed to

For i = 0 to Ubound(strGivenName)-2
   sSQL = "INSERT into Tablename (FullName, Email, GivenName)"
   sSQL = sSQL &  "VALUES ('" & strFullName(i) &"', '"& strEmailName(i) &"', '" & strGivenName(i) &"')"
   'objConn.Execute sSQL

Now that this is clear, I have implemented your code and still getting the same issue. However at least now I understand what you mean about array size and what you are trying to do. Irregardless of what I change the minus number from the following, it makes no difference.

For i = 0 to Ubound(strGivenName)-2

So if I change -2 to -14 the results are exactly the same. I've been looking at the results and it looks like the email username and email address is correct but still the given names are not corresponding.

 

[DotNetGnat]

yes i was expecting that you would see that Given names are not matched and that is because the array size of the given names is different from the array size of the other two...

you need to mgo through the array data and see how you can match all of them...let me take a another guess...try this...

For i = 0 to [red]Ubound(strFullName)[/red]
   sSQL = "INSERT into Tablename (FullName, Email, GivenName)"
   sSQL = sSQL &  "VALUES ('" & strFullName(i) &"', '"& strEmailName(i) &"', '" & [red]strGivenName(i+2)[/red] &"')"
   'objConn.Execute sSQL

-DNG

 

[tsuji]

If you have mis-alignment (your term) like that, you can use regexp in place of split.
[tt]
dim rx, cm_FullName, cm_EmailName, cm_GivenName
set rx=new regexp
with rx
.global=true
.pattern="[A-Za-z@.]+"
end with
set cm_FullName=rx.execute(FullNameDesc)
set cm_EmailName=rx.execute(EmailNameDesc)
set cm_GivenName=rx.execute(GivenName)
[/tt]
Then proceed with the construction "as if" cm_xxx is array indexing with i=0 to cm_xxx.count-1. Like this.
[tt]
For i = 0 to (cm_GivenName.count-1)-2
sSQL = "INSERT into Tablename (FullName, Email, GivenName)"
sSQL = sSQL & "[highlight] [/highlight]VALUES ('" & cm_FullName(i) &"', '"& cm_EmailName(i) &"', '" & cm_GivenName(i) &"')"
objConn.Execute sSQL
[/tt]
(Note:[1] space in front of VALUES is not critical; [2] if you want to use type prefix as you did, why you stick a str prefix to an array? That's self-defeating.)