ADO and DISTINCT keyword and MSSQL7

ADO and DISTINCT keyword and MSSQL7

Post by Joe Dal » Fri, 14 Jan 2000 04:00:00



I have a VB which utilizes an ADO connection to return a recordset based on
a varible query input which is placed in astrQry in the code below.  All my
queries work except when I use the DISTINCT keyword which causes
MSSQLRecordSet.RecordCount = -1.  Any ideas anybody

TIA.

ex: (actual use a more complex query)
    good query
        astrQry = "SELECT EMAIL_ADDR FROM EMPLOYEES"
then
        MSSQLRecordSet.RecordCount = 1234

   bad query
        astrQry= "SELECT DISTINCT EMAIL_ADDR FROM EMPLOYEES"
then
        MSSQLRecordSet.RecordCount = -1

   Set MSSQLConnection = New ADODB.Connection
    Set MSSQLRecordSet = New ADODB.Recordset

    MSSQLConnection.ConnectionString =
"Provider=SQLOLEDB.1;Password=xxxx;Persist Security Info=True;User
ID=abc;Initial Catalog=abc;Data Source=PEACOCK"
    MSSQLConnection.Open
    If MSSQLConnection.State = adStateOpen Then
        MSSQLRecordSet.Open astrQry, MSSQLConnection, adOpenKeyset,
adLockOptimistic
    Else
        MsgBox ("The connection could not be made")
        MSSQLConnection.Close
        Exit Function
    End If
    If MSSQLRecordSet.RecordCount < 0 Then
        MSSQLConnection.Close
        Exit Function
    End If

 
 
 

ADO and DISTINCT keyword and MSSQL7

Post by Anthony C. Perkin » Sat, 15 Jan 2000 04:00:00


Joe,

You may want to take a look at the value of CursorLocation.  The RecordCount
property is not supported with CursorLocation = adUseServer

Hope this helps,
Anthony


Quote:> I have a VB which utilizes an ADO connection to return a recordset based
on
> a varible query input which is placed in astrQry in the code below.  All
my
> queries work except when I use the DISTINCT keyword which causes
> MSSQLRecordSet.RecordCount = -1.  Any ideas anybody

> TIA.

> ex: (actual use a more complex query)
>     good query
>         astrQry = "SELECT EMAIL_ADDR FROM EMPLOYEES"
> then
>         MSSQLRecordSet.RecordCount = 1234

>    bad query
>         astrQry= "SELECT DISTINCT EMAIL_ADDR FROM EMPLOYEES"
> then
>         MSSQLRecordSet.RecordCount = -1

>    Set MSSQLConnection = New ADODB.Connection
>     Set MSSQLRecordSet = New ADODB.Recordset

>     MSSQLConnection.ConnectionString =
> "Provider=SQLOLEDB.1;Password=xxxx;Persist Security Info=True;User
> ID=abc;Initial Catalog=abc;Data Source=PEACOCK"
>     MSSQLConnection.Open
>     If MSSQLConnection.State = adStateOpen Then
>         MSSQLRecordSet.Open astrQry, MSSQLConnection, adOpenKeyset,
> adLockOptimistic
>     Else
>         MsgBox ("The connection could not be made")
>         MSSQLConnection.Close
>         Exit Function
>     End If
>     If MSSQLRecordSet.RecordCount < 0 Then
>         MSSQLConnection.Close
>         Exit Function
>     End If


 
 
 

1. Recordset error using DISTINCT in MSSQL7 query

I have a VB app which utilizes an ADO connection to return a recordset
based on
varible query input which is placed in astrQry in the code below.  All
my queries work except when I use the DISTINCT keyword which causes
MSSQLRecordSet.RecordCount = -1 and in going thru the records - nothing
is returned.  Any ideas anybody

TIA.

ex: (actual use a more complex query)
    good query
        astrQry = "SELECT EMAIL_ADDR FROM EMPLOYEES"
then
        MSSQLRecordSet.RecordCount = 1234

   bad query
        astrQry= "SELECT DISTINCT EMAIL_ADDR FROM EMPLOYEES"
then
        MSSQLRecordSet.RecordCount = -1

   Set MSSQLConnection = New ADODB.Connection
    Set MSSQLRecordSet = New ADODB.Recordset

    MSSQLConnection.ConnectionString =
"Provider=SQLOLEDB.1;Password=xxxx;Persist Security Info=True;User
ID=abc;Initial Catalog=abc;Data Source=PEACOCK"
    MSSQLConnection.Open
    If MSSQLConnection.State = adStateOpen Then
        MSSQLRecordSet.Open astrQry, MSSQLConnection, adOpenKeyset,
adLockOptimistic
    Else
        MsgBox ("The connection could not be made")
        MSSQLConnection.Close
        Exit Function
    End If
    If MSSQLRecordSet.RecordCount < 0 Then
        MSSQLConnection.Close
        Exit Function
    End If

2. Nested Transactions in Rose DAA

3. using DISTINCT keyword with Text datatype

4. Newbie: how to know which table contains a specified field?

5. SQL Statement question, DISTINCT keyword

6. DIMENSIONs

7. Distinct keyword

8. Storing Image Data Types

9. DISTINCT keyword not working.

10. Problem in DISTINCT keyword usage with TEXT datatype

11. top and distinct keyword did not work together

12. keyword DISTINCT

13. DISTINCT Keyword