Microsoft KB Archive/263928

= How to Sort the Results of a Site Server Personalization and Membership (P&M) Query =

Article ID: 263928

Article Last Modified on 6/23/2005

-

APPLIES TO


 * Microsoft Site Server 3.0 Standard Edition

-



This article was previously published under Q263928



SUMMARY
This article demonstrates how to sort the results of a Site Server query that is returned using the Active Directory Service Interfaces (ADSI) provider.



MORE INFORMATION
The Site Server Lightweight Directory Access Protocol (LDAP) provider does not provide support for the SQL ORDER BY clause. If you attempt to issue a query using the ADSI provider that uses the SQL syntax and the ORDER BY clause, the results are not sorted. This is the expected behavior, as the Site Server LDAP provider does not support the ORDER BY clause.

To sort the results of a query, the data must be parsed and placed into an array or temporary recordset. Microsoft does not recommend using an array, as this is very expensive on the server and can require a lot of resources. The more efficient approach is to use an ActiveX Data Objects (ADO) recordset to sort the data.

For simplicity, it is important to note that the following Active Server Pages (ASP) sample only takes the first value of multi-value attributes.

  List all members sorted by givenName+cn

<% ' The Site Server LDAP Provider does not provide support for sorting records when queries are issued. ' The Site Server LDAP Provider lacks support for the ORDER BY clause which can be used ' when issuing queries using ADO with the ADSI Provider.

' LDAP Information g_strOrg = &quot;SomeOrg&quot; g_strServer = &quot;SomeServer&quot; g_strPort = &quot;5292&quot; g_strAdminDN = &quot;cn=Administrator, ou=Members, o=&quot; & g_strOrg g_strAdminPW = &quot;password&quot;

' Filter information for query g_strFilter = &quot;(!cn=Administrator)&quot; g_strAttributes = &quot;cn, givenName, AdsPath&quot;

Call ListMembers

Sub ListMembers szSelect = &quot;;(&(objectClass=member)(&quot; & g_strFilter & &quot;));&quot; & g_strAttributes & &quot;;subtree&quot;

' Example of SQL syntax ' strADO = &quot;SELECT AdsPath,givenName, cn FROM ' LDAP://SomeServer:5292/o=SomeOrg/ou=Members' WHERE cn='tr*'&quot; '& _ '           &quot;WHERE objectClass='member'&quot;

Set orsTemp = CreateObject(&quot;ADODB.Recordset&quot;) orsTemp.Fields.Append &quot;AdsPath&quot;, adVarChar, 125 orsTemp.Fields.Append &quot;givenName&quot;, adVarChar, 125 orsTemp.Fields.Append &quot;cn&quot;, adVarChar, 125 orsTemp.CursorLocation = adUseClient orsTemp.CursorType = adOpenKeyset orsTemp.Open

Set oconn = CreateObject(&quot;ADODB.Connection&quot;) Set ors = CreateObject(&quot;ADODB.Recordset&quot;) oconn.Provider = &quot;ADsDSOObject&quot; oconn.Open &quot;ADs Provider&quot;, g_strAdminDN, g_strAdminPW

ors.CursorLocation = adUseClient

ors.Open szSelect, oconn, adOpenStatic, adLockReadOnly, adCmdText

' spefiy the sort order ' sSort = &quot;DESC&quot; szSort = &quot;ASC&quot; orsTemp.Sort = &quot;givenName,cn &quot; & szSort

' Loop through the results and add them to the temporary recordset

While Not ors.EOF For Each Field In ors.Fields szValue = Field.Value szField = Field.Name ' Note Chr(174) is used as a delimiter szFieldList = szFieldList & Field.Name & Chr(174) If IsArray(szValue) Then ' multi-valued array ' We only take the first value of the multi-valued array, otherwise you would need to use ' a one-to-many relationship between two tables or add multiple records for the various values. ' Since this is only an example, we will not indulge into the complexity of creating a       ' one-to-many relationship. szFieldValues = szFieldValues & CStr(szValue(0)) & Chr(174) Else If IsNull(szValue) Then szValue = &quot; &quot; End If     szFieldValues = szFieldValues & CStr(szValue) & Chr(174) End If Next

' Let's strip off the trailing delimiters szFieldValues = Left(szFieldValues, Len(szFieldValues) - 1) szFieldList = Left(szFieldList, Len(szFieldList) - 1) ' Now append the record to the temporary aTempList = Split(szFieldList, Chr(174), -1, 1) aTempValues = Split(szFieldValues, Chr(174), -1, 1) orsTemp.AddNew aTempList, aTempValues orsTemp.Update ' Array(szFieldValues) szFieldValues = Null szFieldList = Null ors.MoveNext Wend orsTemp.MoveFirst

' Display the sorted records in the client's window Response.Write &quot;**************Listing of temporary records************************&quot; & &quot;&quot; While Not orsTemp.EOF Response.Write orsTemp.Fields(&quot;cn&quot;) & &quot; &quot; & orsTemp.Fields(&quot;givenName&quot;) & &quot;&quot; orsTemp.MoveNext Wend ors.Close orsTemp.Close

Set ors = Nothing Set orsTemp = Nothing Set oconn = Nothing

End Sub %> &#xa0;

 

How to Use this Sample
  Copy the sample code and paste it into an ASP page.

Modify the following variables for your environment:

'LDAP Information g_strOrg = &quot;SomeOrg&quot; g_strServer = &quot;SomeServer&quot; g_strPort = &quot;5292&quot; g_strAdminDN = &quot;cn=Administrator, ou=Members, o=&quot; & g_strOrg g_strAdminPW = &quot;password&quot;

' Filter information for query g_strFilter = &quot;(!cn=Administrator)(!cn=Ad*)&quot; g_strAttributes = &quot;cn, givenName, AdsPath&quot;<BR/>

Save the sample to the server, and then test the sample. </li> <li> Modify the following variables for your environment:

'LDAP Information g_strOrg = &quot;SomeOrg&quot; g_strServer = &quot;SomeServer&quot; g_strPort = &quot;5292&quot; g_strAdminDN = &quot;cn=Administrator, ou=Members, o=&quot; & g_strOrg g_strAdminPW = &quot;password&quot;

' Filter information for query g_strFilter = &quot;(!cn=Administrator)(!cn=Ad*)&quot; g_strAttributes = &quot;cn, givenName, AdsPath&quot;<BR/>

Save the sample to the server, and then test the sample. </li> <li>Save the sample to the server, and then test the sample.</li></ol>

NOTE: Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

http://www.microsoft.com/partner/referral/

For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site:

http://support.microsoft.com/default.aspx?scid=fh;EN-US;CNTACTMS

Additional query words: Site Server, Sorting, LDAP, Query, Order By

Keywords: kbhowto KB263928

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.