PRB: Getting SELECT DISTINCT To Work Under ADO with SQL Server

Last reviewed: February 23, 1998
Article ID: Q181479
The information in this article applies to:
  • ActiveX Data Objects (ADO), versions 1.0, 1.5

SYMPTOMS

Under certain conditions with ActiveX Data Objects (ADO), SELECT DISTINCT in your SQL statement will not provide distinct records. However, you have to meet all of the following conditions to cause the problem to occur:

  • You opened a standalone ADO recordset without opening it off a Command or Connection object.
  • The data source you are opening is SQL Server.
  • The recordset CursorLocation is set to adUseClient.

This happens when either the Recordset.Open method is used or when the recordset is obtained from a command object by setting the recordset equal to the return value of Command.Execute.

CAUSE

The reason this does not work with adUseClient is that ADO uses SQL Server's FOR BROWSE option on the SQL statement to get extended metadata in order to process updates. The use of this option causes SQL version 6.5 to ignore the DISTINCT keyword.

RESOLUTION

Here are the two possible workarounds to get SELECT DISTINCT to work as expected:

  • Set the CursorLocation to adUseServer instead of adUseClient.
  • When using the Open method with adUseClient you can workaround this problem by using a command object and setting its ActiveConnection to the connection used in the Open statement.

The Steps to Reproduce Behavior below demonstrates the workarounds for both the Open and the Command methods.

STATUS

Microsoft is researching this problem and will post new information here in the Microsoft Knowledge Base as it becomes available.

MORE INFORMATION

Steps to Reproduce Behavior

  1. Create a standard .exe project, and under Project, References select Microsoft ActiveX Data Objects 1.5 Library.

  2. Paste the following code in the Declaration section of the form:

          Option Explicit
    

          Dim conn As New Connection
          'Dim com As New Command  'Uncomment to make DISTINCT work.
          Dim rs As New Recordset
    

          Private Sub Command1_Click()
    

            conn.open "mymachine", "sa", ""
            conn.cursorlocation = adUseClient  'Change to aduseServer.
            'com.ActiveConnection = conn  'Uncomment to make DISTINCT work.
    
            rs.open "select distinct state from authors", conn, _
                adOpenKeyset, adLockOptimistic
            MsgBox rs.RecordCount
          End Sub
    
          Private Sub Command2_Click()
            Dim counter As Integer
            counter = 0
            conn.Open "dsn=mymachine;id=sa;pwd=;"
            com.ActiveConnection = conn
            conn.CursorLocation = adUseClient   'Comment this
            'conn.CursorLocation = adUseServer  'and uncomment this.
            com.CommandText = "select distinct state from authors"
            com.CommandType = adCmdText
            rs.CursorType = adOpenKeyset
            'cursortype will be adopenforwardonly with adUseServer.
            Set rs = com.Execute
            While Not rs.EOF
              Debug.Print rs!State
              counter = counter + 1
              rs.MoveNext
            Wend
            Debug.Print "--------------------"
            MsgBox counter  'recordcount return -1 with adUseServer
          End Sub
    
    

  3. Run the code and click Command1. Note the number of records returned. Change adUseClient to adUseServer in the CursorLocation. Run the code again and fewer records are returned if there are duplicate states.

  4. Change aduseServer back to adUseClient. Uncomment the Dim of the command object and the code in Command1. Run the code again. You receive the correct number of records using this technique also.

  5. Run the code again and click Command2. Note the counter. Comment the CursorLocation for adUseClient and uncomment the adUseServer CursorLocation. Run the code and note the counter again.

Keywords          : kbcode adoall adogenis
Version           : WINDOWS:1.0,1.5
Platform          : WINDOWS
Issue type        : kbprb
Solution Type     : kbnofix


================================================================================


THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: February 23, 1998
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.