How do I display the results of a query to the global catalog in AD?

[Rikkimaru]

hello.

I'm working on an ASP page that queries the Global Catalog of Active Directory for domain names and phone numbers.
For the most part, the script works and gets the result set. My problem comes in displaying the records in asp.

Here is the error message i get:
Response object error 'ASP 0106 : 80020005'

Type Mismatch

/phonebook/includes/test.asp, line 0

An unhandled data type was encountered



How do I fix this?
Here's the code:


<%
' If user enters "*", then filter on all people
' Else Search for all users whose surnames start with the last initial.

If letter_input = "*" Then
strName = "(sn=*)"
Else
strName = "(sn=" & letter_input & "*)"
End If





' Create ADO connection using the ADSI OLE DB provider
Set objADOConnection = CreateObject("ADODB.Connection")
objADOConnection.Open "Provider=ADsDSOObject;"

' Create ADO commmand object and associate it with the connection
Set objADOCommand = CreateObject("ADODB.Command")
objADOCommand.ActiveConnection = objADOConnection

' Create the command string using the four parts
objADOCommand.CommandText = "<GC://DC=DOMAIN_NAME,DC=com>;(&(objectCategory=person)" & strName &");sn,givenName;subtree"

' Set the number of records in the recordset logical page
objADOCommand.Properties("Page Size") = 20000

' Set the maximum result size
objADOCommand.Properties("Size Limit") = 20000

' Sort the results based on the cn attribute
objADOCommand.Properties("Sort On") = "cn"

' Execute the query for the user in the directory
Set objADORecordset = objADOCommand.Execute




If objADORecordset.EOF Then
Response.Write("No Records Found")
Else

Response.Write("<table border=1 cellpadding=0 cellspacing=0 align=left>")

While Not objADORecordset.EOF

Response.Write("<tr>")
' Display the row using the selected fields

Response.Write("<td>")
'strDisplayLine = objADORecordset.Fields("sn")
'Response.Write(strDisplayLine)
Response.Write(objADORecordset.AbsolutePosition)
Response.Write(",")
Response.Write(objADORecordset.RecordCount)
'strDisplayLine = objADORecordset.Fields("givenName")
'Response.Write(strDisplayLine)
Response.Write("</td>")

Response.Write("<td>")
strDisplayLine = objADORecordset.Fields("sn").value
Response.Write(strDisplayLine)
Response.Write(",")
strDisplayLine = objADORecordset.Fields("givenName")
Response.Write(strDisplayLine)
Response.Write("</td>")


Response.Write("</tr>")
' Advance to the next record
objADORecordset.MoveNext

Wend
Response.Write("</table>")

'Close Recordset and return memory
objADORecordset.Close

' Close the ADO connection
objADOConnection.Close
%>


Help please...

 

[FesterSXS]

maybe you need single quotes around your value for sn

If letter_input = "*" Then
  strName = "(sn='*')"
Else
  strName = "(sn='" & letter_input & "*')"
End If

Tony
_______________________________________________________________

http://www.SpiraxSarco.com/

 

[tsuji]

Rikkimaru,

Either it is a typo (cn for sn)
[tt] objADOCommand.Properties("Sort On") = "[red]sn[/red]"[/tt]
_or_ you have to retrieve cn as well?
[tt] objADOCommand.CommandText = "<GC://DC=DOMAIN_NAME,DC=com>;(&(objectCategory=person)" & strName &");sn,givenName[red],cn[/red];subtree"[/tt]

regards - tsuji

 

[Rikkimaru]

Thanks for the replies FesterSXS and tsuji.

But I think you misunderstood my question (or I didn't ask properly).

For the most part, the script works and gets the result set. My problem comes in displaying the records in asp.

When I query the number of people whose name starts with "Z" for example, I know that the query executes properly because I see the correct number of rows returned in the recordset. However for some reason, I can't display the field values. It is that that I really wanted help with.

I included the code just in case there was an alternative approach that I could have used.

Thanks anyways guys...
Keep helping

 

[tsuji]

>queries the Global Catalog of Active Directory for domain names and phone numbers.
nowhere I see phone numbers
>For the most part, the script works
what is the "entier minus most" part which does not work?
>Type Mismatch
It means literally that.
>/phonebook/includes/test.asp, line 0
What is test.asp? Line 0 is somehow usually misleading.
>An unhandled data type was encountered
This explains type mismatch.

Also since question I asked is not answered, I can only advise you: if it is only the display problem, replace all variables in the response.write by typename(variables) and see what you get.

 

[JSpicolli]

Make sure you are not querying on any fields that return an array from AD.

Some of the fields in AD are multi-dimensional and return arrays, not strings.

 

[Rikkimaru]

Thanks for the ideas guys.
Let me clear up some stuff for tsuji first:

>queries the Global Catalog of Active Directory for domain names and phone numbers.
nowhere I see phone numbers

Ok...well, I edited my code for the sake of brevity, so the code extracting the phone numbers etc isn't there. I should have stated that though

>For the most part, the script works
what is the "entier minus most" part which does not work?

I'm guessing you're referring to my statement in the first post "For the most part, the script works". This is a colloquial way of saying, "I see most of my script working" except for this part here:

Response.Write("<td>")
            strDisplayLine = objADORecordset.Fields("sn").value
            Response.Write(strDisplayLine)
            Response.Write(",")
            strDisplayLine = objADORecordset.Fields("givenName")
            Response.Write(strDisplayLine)
            Response.Write("</td>")

I only included this section of the output because I assumed that the principles to make this work would make all the other output sections work.

Also since question I asked is not answered,

Was that "_or_ you have to retrieve cn as well?" in your first post? It was kinda of jumbled in and I guess I missed it the first time. I'm selecting all attributes in the query, so I should be able to sort on any of them. Well, I tried sorting on "sn" too but that didn't work either

Thanks alot for your input Tsuji...

Now on to JSpicolli

Make sure you are not querying on any fields that return an array from AD.
Some of the fields in AD are multi-dimensional and return arrays, not strings.

Thanks for the reply JSpicolli, and I've experimented with loops. I haven't had much luck however. Would you or someone else be able to illustrate what you mean?

Thanks to everyone who has helped. I really appreciate it.

Ciao

 

[tsuji]

Have you tried replacing all instances of objADORecordset.Fields("xxx") by typename(objADORecordset.Fields("xxx")?

The problem is all the lines you show retrieve attributes of simple type! that is why what is the part not working!

 

[Rikkimaru]

So tsuji, are you saying that I should try something like:

StrDisplayLine = String(objADORecordset.Fields("sn").value)

Is that right? I'll test it out, but let me know if I did something wrong.

Thanks

 

[tsuji]

I meant when you want to display one attribute xxx, you're now putting it into a variable or response.write it out directly. Now, as it is all cloud surrounding what retrieved eventually, might be long, might be array of variant, might be array of bytes, might be null which may pose all sorts of problem before displaying it by a uniform response.write rs.fields("xxx").value.

We typename(rs.fields("xxx").value), you are certain to get a string and displayable. By inspecting the display, you know where the problems---type match---lay.

Typename() is a vbscript function.

 

[Rikkimaru]

Ahhhhhhhhhhhhhhhhhhhhhhhh...
Many thanks tsuji.
It becomes clear and now I am getting something.

What I've found interesting is that a line like

lastname = Typename(objADORecordset.Fields("xxx"))
Response.Write(lastname)

returns a "Field" datatype (surprise surprise) but a line like

lastname = Typename(objADORecordset.Fields("xxx").value)
Response.Write(lastname)

returns a "Variant()" datatype(or object?)...
Interesting...I'm looking into it right now, but in the meantime can anyone explain why?

I think I'll post the completed code and perhaps I can save another poor intern the agony I went through over such a simple thing. A little knowledge does go a long way.

 

[Rikkimaru]

Still not working... *sigh*
What could I be doing wrong?

In my last post I noted the difference in when I used ".value".

I managed to learn a bit about VBScript in the process and understand that the variant datatype is a parent datatype and all other datatypes are sub types of this.

I guess I'm a bit frustrated because in all the ADO examples I've found refferencing the result set is so easy and this is so hard.

If anyone has any ideas, feel free to share...

Thanks

	Dim lastname
    	Dim firstname
	Dim mail

	
	Dim strName
	strName = "(sn=Z*)"

	Dim suser
	suser = "Domain\username"

	Dim spass
	spass = "password"
    
' Create ADO connection using the ADSI OLE DB provider
	Dim objADOConnection
	Set objADOConnection = CreateObject("ADODB.Connection")
	objADOConnection.provider ="ADsDSOObject"
	objADOConnection.Open "Active Directory Provider",suser,spass

    
' Create ADO commmand object and associate it with the connection
	Dim objADOCommand
	Set objADOCommand = CreateObject("ADODB.Command")
	Set objADOCommand.ActiveConnection = objADOConnection

    
' Create the command string using the four parts
	objADOCommand.CommandText = "<GC://DC=DOMAIN,DC=com>;(&(objectCategory=person)" & strName &");sn,givenName,mail,physicalDeliveryOfficeName,telephoneNumber;subtree"

' Response.Write(objADOCommand.CommandText)
	Dim objADORecordset
	Set objADORecordset = objADOCommand.Execute


	If objADORecordset.EOF Then
    		Response.Write("No Records Found")
    
	Else

        ' Loop through all the returned records
        	
While Not objADORecordset.EOF	
       
			' Display the row using the selected fields
            		'email = TypeName(objADORecordset.Fields("mail").value)
            		email = CStr(objADORecordset.Fields("mail"))
			'email = CStr(email)
			Response.Write("<br>"&email)
Wend

	End If

 

[JSpicolli]

Just try displaying each individual record one at atime until you discover which AD fields are returning the multi-value fields.

Start with sn and work your way from there.

Also, if you link SQL Server to AD, you can performe queries much easier.

Some basica info at:

http://www.windowsitpro.com/sqlserver/forums/messageview.cfm?catid=1669&threadid=100524

http://support.microsoft.com/default.aspx?scid=KB;EN-US;q299410&

AND

http://codebetter.com/blogs/brendan.tompkins/archive/2003/12/19/4746.aspx

~ Spicolli

 

[tsuji]

Rikkimaru,

[1] You query gc. It produces a forest-wide search. Further nail down the search to (objectClass=user).
[2] GC query produces only attributes which are within the replication scheme forest-wide. Make sure all the attributes are designed to be replicated forest-wide.

- tsuji

 

[tsuji]

Further note:

One single change I think might hold the clue.
[3] Before execute, give reasonably long timeout. How long? you have to make your good judgement.
[tt] objADOCommand.properties("timeout")=180 'sec[/tt]
[3a]In the asp, at the top part, set the server scripttimeout to long enough time. (Again good judgement is needed.)
[tt] server.scripttimeout=360[/tt]
In case wildcard is applied, the return set may be big. Maybe a "page size" properties should be set too. But that's for improvement at later stage.

- tsuji

 

[Rikkimaru]

Thanks tsuji and JSpicolli for the ideas and support.

I've been kept busy doing some other things, but I'm back and ready to tear my hair out again. [

http://www.tipmaster.com/images/smiletiniest.gif

]

I've tried displaying the individual fields but that doesn't work either...

Interestingly, this code runs like a dream in VBScript (with some changes of course) and actually, most of this code was adopted from VBScript.
So go figure...

It's 5:00pm and my brain is fried...
Tomorrow I'll try again

 

[Rikkimaru]

Hello again.

I should have mentioned earlier that this code runs on my workstation which runs Windows XP Pro. However all these issues that I've been having happen when I'm using windows 2k Server. I assumed that since I was new to ASP it was a problem with my code but I've found a new approach.

I realised that MDAC (Microsoft Data Access Controls) versions are different between Windows 2k and Windows XP. My code is very similar to working code examples online so could it be MDAC?

JSpicolli and tsuji, if you have such an app (or part of one) working on your machine, would you be so kind as to tell me what version of MDAC is installed? The path to the MDAC version should be something like [link C:\Program Files\Common Files\System\ADO\MDACReadMe.htm]this[/url].
Be warned that this might not be the EXACT location, but it's not too hard to find.

Thanks guys

 

[JSpicolli]

My registry says Version 5,0,00,0 on Win2k3 Server.

Look in:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Active Setup\Installed Components\{E92B03AB-B707-11d2-9CBD-0000F87A369E}

One thing to consider then is the use of the ADSI Flag property of the connection.

Notice it's placement. If you place it anywhere else in the connectionstring, it will fail (weird, but true).


Try using this class:

<%
	'============================================================
	' Utility Class for Active Directory Queries
	'============================================================
	
	Class cLdapUtil
	
		'===========================
		'## Private Class Members ##
		'===========================
		Private m_sConnect
		Private m_oConnection
		Private m_oTempRS
	
		Private m_lErrorNumber
		Private m_sErrDescription
		Private m_lErrorCount
		Private vbQuote
	
		Private m_sName
		Private m_sEmail
		Private m_sPrimaryPhone
		Private m_sMobilePhone
		
		Private m_sDomain
		
		'=================================
		' Private ReadOnly Class Members
		'=================================
		Private m_sLocalLdapUserName
		Private m_sLocalLdapPassword
		Private m_sRemoteLdapUserName
		Private m_sRemoteLdapPassword
		
		Private Property Get LocalLdapUserName()
			LocalLdapUserName = m_sLocalLdapUserName
		End Property
		
		Private Property Get LocalLdapPassword()
			LocalLdapPassword = m_sLocalLdapPassword
		End Property
		
		Private Property Get RemoteLdapUserName()
			RemoteLdapUserName = m_sRemoteLdapUserName
		End Property
		
		Private Property Get RemoteLdapPassword()
			RemoteLdapPassword = m_sRemoteLdapPassword
		End Property
		
		
		
		'==================================
		' Name Property
		'==================================
		
		Public Property Get Name()
			Name = m_sName
		End Property
		
		Public Property Let Name(sInput)
			m_sName = sInput
		End Property
		
		
		'==================================
		' Email Property
		'==================================
		
		Public Property Get Email()
			Email = m_sEmail
		End Property
		
		Public Property Let Email(sInput)
			m_sEmail = sInput
		End Property
		
		'==================================
		' Primary Phone Property
		'==================================
		
		Public Property Get PrimaryPhone()
			PrimaryPhone = m_sPrimaryPhone
		End Property
		
		Public Property Let PrimaryPhone(sInput)
			m_sPrimaryPhone = sInput
		End Property
		
		'==================================
		' Mobile Phone Property
		'==================================
		
		Public Property Get MobilePhone()
			MobilePhone = m_sMobilePhone
		End Property
		
		Public Property Let MobilePhone(sInput)
			m_sMobilePhone = sInput
		End Property
		
		'==================================
		' LDAP Domain Property
		'==================================
		
		Public Property Get Domain()
			Domain = m_sDomain
		End Property
		
		Public Property Let Domain(sInput)
			m_sDomain = sInput
		End Property
		
		'=============================================
		'## Public Error Number Class Property		##
		'=============================================
		Public Property Get ErrorNumber()
			ErrorNumber = m_lErrorNumber
		End Property
	
		Public Property Let ErrorNumber(lInput)
			m_lErrorNumber = lInput
		End Property
	
		'=================================================
		'## Public Error Description Class Property		##
		' holds error msg for LAST error encountered 
		'================================================
		Public Property Get ErrorDescription()
			ErrorDescription = m_sErrDescription
		End Property
	
		Public Property Let ErrorDescription(sInput)
			m_sErrDescription = sInput
		End Property
	
		'=============================================
		'## Public Error Count Class Property		##
		'=============================================
		Public Property Get ErrorCount()
			ErrorCount = m_lErrorCount
		End Property
	
		Public Property Let ErrorCount(lInput)
			m_lErrorCount = lInput
		End Property
		
		'=====================================
		'## Private Recordset Property ##
		'=====================================
		Public Property Get RecordSet()
			Set RecordSet = m_oTempRS
		End Property
		
		Private Property Set RecordSet(ByVal oInput)
			Set RecordSet = oInput
		End Property
		'=============================================
		' Connection Properties
		'=============================================
		
		'=============================================
		'## Private ADO Connection OBJECT Property ##
		'=============================================
		Private Property Get Connection()
			'## IF m_oConnection is an object and Connection are not already Set ##
			'## Create a New instance of Connection Property, otherwise reuse existing ##
			If isObject(m_oConnection) AND Not isObject(Connection) Then
				Set Connection = m_oConnection
			End IF
		End Property
	
		Private Property Set Connection(oInput)
			IF Not isObject(m_oConnection) Then
				Set m_oConnection = oInput
			End IF
		End Property
	
		'=============================================
		'## Public Read/Write ADO Connection String	##
		'=============================================
		Public Property Get ConnectionString()
			ConnectionString = m_sConnect
		End Property
	
		Public Property Let ConnectionString(sInput)
			m_sConnect = sInput
		End Property
		
		
		'============================================
		' Opens a Connection
		'============================================
		Private Sub OpenConnection()
			IF isObject(Connection) Then
				IF (Connection.State = 0) Then
					Connection.Open ConnectionString
				End IF
			Else
				Set Connection = Server.CreateObject("ADODB.Connection")
				Connection.Open ConnectionString
			End IF
		End Sub
	
		'=====================================
		'## Private CloseConnection			##
		'## {Closes an Open Connection}		##
		'=====================================
		Private Sub CloseConnection()
			IF isObject(Connection) AND (Connection.State > 0) Then
				Connection.Close
			End IF
		End Sub
	
		'=====================================================
		'## Private GetStatus								##
		'## Returns The State of the Connection				##
		'## Used internally to determine Connection Status  ##
		'=====================================================
		Private Function GetStatus()
			IF isObject(Connection) Then
				GetStatus = Connection.State
			Else
				GetStatus = 0
			End IF
		End Function
		
		'=========================================
		'## Class_Terminate						##
		'## Fires during class destruction		##
		'=========================================
		Private Sub Class_Terminate()
			If IsObject(m_oConnection) Then
				CloseConnection
				Set m_oConnection = Nothing
			End IF
		
			If isObject(m_oTempRS) Then
				If m_oTempRS.State > 0 Then
					m_oTempRS.Close
				End IF
				Set m_oTempRS = Nothing
			End IF
		End Sub
	
		'================================
		' Class Constructor / Initializer
		'================================
		Private Sub Class_Initialize()
			vbQuote = Chr(34)
			'============================================
			'	Overrideable ConnectionString
			'===========================================
[highlight]
			'ConnectionString = "Data Source=Active Directory Provider;Provider=ADSDSOObject;ADSI Flag=1;User ID=Domain\UserName;Password=MyLDAPPassword;"
			ConnectionString = "Data Source=Active Directory Provider;Provider=ADSDSOObject;ADSI Flag=1;User ID=" & Application("LdapUserName") & ";Password=" & Application("LdapPassword") & ";"
			
		
			Domain = "YourDomain"
[/highlight]
			ErrorCount = 0
			ErrorDescription = Null
			ErrorNumber = 0
		End Sub
			
			
		
		
		'=========================================================
		' ExecuteQuery
		' Output Param: RS Object if successful 
		'=========================================================
		Public Function ExecuteQuery(sql)
	
			On Error Resume Next
			
			ExecuteQuery = True
		
			OpenConnection
			Set m_oTempRS = Server.CreateObject("ADODB.RecordSet")
			m_oTempRS.Open sql, Connection, 3, 2
			
			If err.number <> 0 Then
				IncrementErrorCount
				AddErrortext(Err.Description &  " in cLdapUtil:ExecuteQuery")
				AddErrorNumber(1305)
				ExecuteQuery = False
			
			End If
				'Set ExecuteQuery = m_oTempRS
				
			
		End Function
		
		
		'=========================================================
		' Create_Message
		'=========================================================
		Public Sub GetContactData(username)
			
			On Error Resume Next
			
			Dim domainPath
			domainPath = "LDAP://" & domain 
			
			Dim oRS
			Dim strTo 
			
			'======================================
			' Open Connection
			'======================================
	OpenConnection
			
							
	IF ExecuteQuery("SELECT displayname, telephonenumber, mail, mobile FROM '" & domainPath & "' WHERE samaccountname = '" & username & "'") = True Then
				Set oRS = RecordSet
			Else
				IncrementErrorCount
				AddErrortext(Err.Description &  " in cLdapUtil:GetContactData")
				AddErrorNumber(1306)
			End IF
			
			If Not oRS.EOF Then
	  			Name = oRS("displayname")
				Email = oRS("mail")
				PrimaryPhone = oRS("telephonenumber")
				MobilePhone = oRS("mobile")
	  		End If
				
			oRS.Close
			Set oRS = Nothing
				
		End Sub		
		
		Private Sub IncrementErrorCount()
			ErrorCount = (ErrorCount + 1)
		End Sub
	
	
		Private Sub AddErrorText(sInput)
			ErrorDescription = sInput
		End Sub
	
	
		Private Sub AddErrorNumber(lInput)
			ErrorNumber = lInput
		End Sub
	
	
		'============================================================
		'## Public ResetErrors
		' Returns : nothing
		' Resets Error Properties
		' Use this to reset error properties of this class
		'===========================================================
		Public Sub ResetErrors()
			ErrorCount = 0
			ErrorDescription = Null
			ErrorNumber = 0
		End Sub
	

	End Class


%>

Call this code like:

<%  

	Set oLdap = New cLdapUtil
		
		
		oLdap.ExecuteQuery("SELECT cn,samaccountname,givenname,sn,mail,telephonenumber,department,employeeID FROM 'LDAP://MyDomain' WHERE sn='Da*'")
		
		Set oRS = oLdap.RecordSet
		
		IF oLdap.ErrorCount = 0 Then
			Response.Write(oRS.Fields(2).Value)
		Else
			Response.Write(oLdap.ErrorDescription)
		End IF
		
	Set oLdap = Nothing
	

%>

Remeber to replace 'LDAP://MyDomain' with your Domain name.