Why DISTINCT -command does not work with ADO?

[jkettunen]

Hello

I'm using ADO connection.

I have this kind on code:

...
Dim rsRecordset As ADODB.Recordset
Let rsRecordset.CursorLocation = adUseClient
Call rsRecordset.Open("SELECT DISTINCT A, B FROM ...", conDB, adOpenKeyset,
adLockOptimistic, adCmdText)

The DISTINCT does not work; it has no effect: if I do select-directly to the
database (for example with Oracle Sql*Plus), I get 1 row, but rsRecordset retunrs
2 rows.

"GROUP BY " - seems to work...

We use Oracle-database.

How can I get DISTINCT-work? (This is a quite big problem in our project at the
moment.)

- I'm using NT 4.0

Thank you.

[Iouri]

First of all check the syntax of the distinct keyword for your database. They are slightly different for different databases. It can be DISTINCT or DISTINCTROW.
Second,
In your SQL SELECT DISTINCT A, B FROM , the recordset will return distinct combination of A and B. If you want only distinct A you have to write
SELECT DISTINCT A FROM

Iouri Boutchkine
iouri@hotsheet.com

[jkettunen]

Thank you. I tried DISTINCTROW, but it seems, that my database does not regognize it.

I already found one solution from the link:

http://support.microsoft.com/support/kb/articles/q181/4/79.asp

It works when I use adUseServer instead of adUseClient like it was said in link.

Juha