How to obtain the (array) from my function

Hi Expert,

I'd like to be able to send my function a employeeID and return an array of values. I 've tried the function below don't know how to address it and obtain the values from it.
I hope someone can give some help.

function ADS(employeeID)
        FuncADuser = "domain\adsuser" 		
        FuncADpassword = "password" 	
        sDomain = ucase(Mid(sLogonUser, 1, Instr(1, FuncADuser, "\") - 1))
	Set Conn = Server.CreateObject("ADODB.Connection") 
	Set RS = Server.CreateObject("ADODB.Recordset") 
	Conn.Provider = "ADsDSOObject" 
	Conn.Properties("User ID") = FuncADuser
	Conn.Properties("Password") = FuncADpassword
	Conn.Properties("Encrypt Password") = True
	strConn = "Active Directory Provider" 
	Conn.Open strConn , FuncADuser, FuncADpassword
	strRS = "SELECT sn,givenname,mail,department FROM 'LDAP://" & SDomain &"' WHERE employeeID = '" & employeeID & "'" 
	RS.Open strRS, Conn,1,1
	dim ADSarray
	ADSarray = array(RS("sn"), RS("givenname"), RS("mail"), RS("department"))
                RS.Close
end function

Open in new window

LVL 1
SteynskAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

unknown_routineCommented:
The the following code to your function:(last line)

ADS=ADSarray



now you can get an array

Myarray=ADS(employeeID)
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
first, in your function, I would use the GetRows function to turn your recordset into an array, a lot more efficient.  then, when calling the function, just assign it to a variable and then you have the data you need:

function ADS(employeeID)
        FuncADuser = "domain\adsuser" 		
        FuncADpassword = "password" 	
        sDomain = ucase(Mid(sLogonUser, 1, Instr(1, FuncADuser, "\") - 1))
	Set Conn = Server.CreateObject("ADODB.Connection") 
	Set RS = Server.CreateObject("ADODB.Recordset") 
	Conn.Provider = "ADsDSOObject" 
	Conn.Properties("User ID") = FuncADuser
	Conn.Properties("Password") = FuncADpassword
	Conn.Properties("Encrypt Password") = True
	strConn = "Active Directory Provider" 
	Conn.Open strConn , FuncADuser, FuncADpassword
	strRS = "SELECT sn,givenname,mail,department FROM 'LDAP://" & SDomain &"' WHERE employeeID = '" & employeeID & "'" 
	RS.Open strRS, Conn,1,1
	dim ADSarray
	ADSarray = rs.GetRows()
                RS.Close

        ADS = ADSArray
end function

dim myArray
myArray = ADS( employeeID )

Open in new window

0
SteynskAuthor Commented:
Thanks unknown_routine for your quick response.


I've added
ADS=ADSarray  as the last line of my function code

and in my page I use:
dim myarray ()
myarray = ADS("44266")
response.write myarray(0)
response.end

Open in new window

But I keep getting a:
Microsoft VBScript runtime error '800a000d'
Type mismatch
In line 37

I've tested the function with a single value and then it works.
0
Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
change

dim myarray ()

to

dim myarray
0
SteynskAuthor Commented:
No sorry same error on same line.
0
SteynskAuthor Commented:
Sorry my mistake....

I get an other error :
ADODB.Field error '80020009'

Object is no longer valid.

on line 0
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
can you post your full code you're now using?
0
SteynskAuthor Commented:
Yes

this is the function it is included in another file

function ADS(employeeID)
        FuncADuser = "abc\ADSuser" 
		FuncADpassword = "password" ' wachtwoord van de functionele user die de Active Directory mag bevragen
		sDomain = "DC=hosp,DC=dfgh,DC=intra"
		Set Conn = Server.CreateObject("ADODB.Connection") 
		Set RS = Server.CreateObject("ADODB.Recordset") 
		Conn.Provider = "ADsDSOObject" 
		Conn.Properties("User ID") = FuncADuser
		Conn.Properties("Password") = FuncADpassword
		Conn.Properties("Encrypt Password") = True
		strConn = "Active Directory Provider" 
		Conn.Open strConn , FuncADuser, FuncADpassword
		strRS = "SELECT sn,givenname,mail,department FROM 'LDAP://" & SDomain &"' WHERE employeeID = '" & employeeID & "'" 
		RS.Open strRS, Conn,1,1
		dim ADSarray 
		ADSarray = array(RS("sn"), RS("givenname"), RS("mail"), RS("department"))
		RS.Close
		ADS=ADSarray
end function

Open in new window


And the code that's calling it is:

<!--#INCLUDE FILE="_header.asp"-->

<%
Set RS1 = Server.CreateObject("ADODB.Recordset")
SQL = "SELECT * FROM jaargesprek_medewerkers;"
RS1.Open SQL,CS,3,3
response.write("<table align=""center"" border=0>")
response.write("<tr><td>Uw naam</td><td>:</td><td>" & Session("fullname") & "</td><td></td><tr>")
response.write("<tr><td>Uw personeelsnummer</td><td>:</td><td>" & Session("employeeID") & "</td><td></td><tr>")
response.write("<tr><td>Uw e-mail adres</td><td>:</td><td>" & Session("email") & "</td><td></td><tr>")
response.write("<tr><td>Uw afdeling</td><td>:</td><td>" & Session("department") & "</td><td></td><tr>")
if RS1("leidinggevende")=1 then
response.write("<tr><td>Uw bent leidinggevende</td><td>:</td><td>Ja</td><td><a href=""leidinggevende_edit.asp?ID=" & Session("employeeID") &""" Title=""Indien u toch niet leidinggevende bent drukt u hier.""><img src='images/edit.png'style=""border-style: none""></td><tr>")
else
response.write("<tr><td>Uw bent leidinggevende</td><td>:</td><td>Nee</td><td><a href=""leidinggevende_edit.asp?ID=" & Session("employeeID") &""" Title=""Indien u toch wel leidinggevende bent drukt u hier.""><img src='images/edit.png'style=""border-style: none""></td><tr>")
end if
if RS1("gedelegeerd")=1 then
response.write("<tr><td>Uw gedelegeerd verantwoordelijk voor het voeren van jaargesprekken</td><td>:</td><td>Ja</td><td><a href=""gedelegeerd_edit.asp?ID=" & Session("employeeID") &""" Title=""Indien u toch niet gedelegeerd verantwoordelijk bent drukt u hier.""><img src='images/edit.png'style=""border-style: none""></td><tr>")
else
response.write("<tr><td>Uw gedelegeerd verantwoordelijk voor het voeren van jaargesprekken</td><td>:</td><td>Nee</td><td><a href=""gedelegeerd_edit.asp?ID=" & Session("employeeID") &""" Title=""Indien u toch wel gedelegeerd verantwoordelijk bent drukt u hier.""><img src='images/edit.png'style=""border-style: none""></td><tr>")
end if
response.write("</table>")
SQL2 = "SELECT * from jaargesprek_relatie where OWpersoneelsnummer='"&Session("employeeID")&"'"
Set RS2 = Server.CreateObject("ADODB.Recordset")
RS2.Open SQL2, cs ,3,3
response.write("<div class=""tabber"">")
response.write("<div class=""tabbertab"">")
response.write("<h2>Uw leidinggevende(n)</h2>")
response.write("<p align=""center"">Klik op één van beide onderstaande knoppen om een leidinggevende toe te voegen.<br>U kunt indien u meerdere arbeidsrelaties heeft ook meerdere leidinggevenden toevoegen.</p>")
response.write("<p align=""center""><input type=""submit"" name=""sdtlbut"" value=""Een leidinggevende van "& Session("department")& " toevoegen"" onClick=""requotepopup('leidinggevende_add.asp?dep="& Session("department")& "','height=450,width=800,scrollbars=no')""><input type=""submit"" name=""sdtlbut"" value=""Een leidinggevende van een andere afdeling toevoegen"" onClick=""requotepopup('leidinggevende_add.asp','height=450,width=800,scrollbars=no')""></p>")
response.write("<table align=""center"" border=0>")
response.write("<tr><th>naam</th><th>e-mail</th><th>afdeling</th><th>uitnodigen</th><th>verwijderen</th><tr>")
oddsevens = 1
Do While Not rs2.EOF
dim myarray 
'myarray = ADS(rs2("LGpersoneelsnummer"))
myarray = ADS("44266")
test = myarray(1)
response.write test

	If (oddsevens MOD 2 = 0) Then 
		response.write("<tr class='odd'>")
	Else 
		response.write("<tr class='even'>")
	End If
	response.write("<td>"& myarray(1) &" "& myarray(0) &"</td><td></td><td></td><td></td><td align=""center""><a href=""leidinggevende_del.asp?ID=" & Session("employeeID") &""" Title=""Hiermee kunt u deze leidinggevende weer verwijderen"" ><img src='images/delete.gif'style=""border-style: none""></td></tr>")
	rs2.MoveNext 
Loop
RS2.Close
response.write("</table>")
response.write("</div>")

if RS1("leidinggevende")=1 then
response.write("<div class=""tabbertab"">")
response.write("<h2>Uw jaargesprekken met medewerker(s)</h2>")
response.write("<p align=""center"">U heeft te kennen gegeven dat u leidinggevende bent. Klik op één van beide knoppen om<br>medewerkers van of uw eigen afdeling "& Session("department")&" toe te voegen of medewerkers van andere afdelingen.<br>U kunt deze knoppen meerdere keren gebruiken tot dat u alle medewerkers heeft toegevoegd.</p>")
response.write("<p align=""center""><input type=""submit"" name=""sdtlbut"" value=""Medewerkers van "& Session("department")&""" onClick=""requotepopup('invoer.asp','height=450,width=800,scrollbars=no')""><input type=""submit"" name=""sdtlbut"" value=""Medewerkers van een andere afdeling"" onClick=""requotepopup('invoer.asp','height=450,width=800,scrollbars=no')""></p>")

response.write("</div>")
end if

if RS1("gedelegeerd")=1 then
response.write("<div class=""tabbertab"">")
response.write("<h2>Naar u gedelegeerde jaargesprek(ken)</h2>")
response.write("<p align=""center"">U heeft te kennen gegeven dat u door een leidinggevende gedelegeerd verantwoordelijk bent gemaakt voor het voeren van jaargesprekken met medewerkers.<br>Klik op één van beide onderstaande knoppen om medewerkers van of uw eigen afdeling "& Session("department")&" toe te voegen of medewerkers van andere afdelingen.<br>U kunt deze knoppen meerdere keren gebruiken tot dat u alle medewerkers heeft toegevoegd.</p>")
response.write("<p align=""center""><input type=""submit"" name=""sdtlbut"" value=""Klik hier om medewerkers van "&Session("department")&" toe te voegen"" onClick=""requotepopup('invoer.asp','height=450,width=800,scrollbars=no')""><input type=""submit"" name=""sdtlbut"" value=""Klik hier om medewerkers van een andere afdeling toe te voegen"" onClick=""requotepopup('invoer.asp','height=450,width=800,scrollbars=no')""></p>")
response.write("</div>")
response.write("</div>")
end if

%>
<!--#INCLUDE FILE="_footer.asp"-->

Open in new window

0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
change this line in your function:

ADSarray = array(RS("sn"), RS("givenname"), RS("mail"), RS("department"))
            
to:

ADSarray = rs.GetRows()


i believe you're storing a pointer to the recordset in the array and once you close the rs, it becomes invalid
0
SteynskAuthor Commented:
After i've added:

line 38 I get

ADODB.Field error '800a0d5c

Oject is no loger valid

in line 37
0
SteynskAuthor Commented:
Thanks The_Big_daddy,

BUt now I get:

Microsoft VBScript runtime error '800a0009'
Subscript out of range
 in line 37
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
you may want to read up on the GetRows() function:

http://www.w3schools.com/ado/met_rs_getrows.asp

it creates a 2D array, so if you only have one row returned, then your code of

test = myarray(1)

will not work. what you want is

test = myarray( 1, 0 )

this should return the "givenname" field
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
SteynskAuthor Commented:
Thanks a lot. You've solved it I will read up the w3school article.

Have nice weekend.
0
Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
glad I could help :)

have a good weekend yourself!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.