Error '80004005' only when SELECT DISTINCT with ASP Classic

Question

I am developing in ASP VBScript at work and need to run a SELECT DISTINCT query but I am having some troubles.

I have other queries in my code that work perfectly fine, that do not use SELECT DISTINCT.

Here is what I am using:

            Dim sections()
            c = 1
            set conn=Server.CreateObject("ADODB.Connection")
            set rs=Server.CreateObject("ADODB.Recordset")
            conn.Open "PROVIDER=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Database Password=******;User ID=Admin;" & _
                    "DATA SOURCE=" & Server.MapPath("modules.mdb")
            rs.open "SELECT DISTINCT section FROM modules WHERE area='First' ORDER BY lvl ASC",conn
            ReDim sections(10)
            do while not rs.EOF
              sections(c) = rs("section")
              c = c + 1
              rs.MoveNext
            loop
            rs.Close
            conn.Close
            set rs = nothing
            set conn = nothing

Which gives me this error:

error '80004005'

on the line of the SQL query

just thought to check reserved words.. realized 'section' is in there... — beyondit01, Jul 20 '15 at 13:36
Even if you change the name of the column, I don't see how that SQL statement could work: you're trying to order by a column that is not in the select list. — Martha, Jul 20 '15 at 15:30

score 1 · Answer 1 · edited Nov 27 '19 at 22:30

1

The only way to fix this is to use "GROUP BY" instead of "DISTINCT"

SELECT DISTINCT section FROM modules WHERE area='First' ORDER BY lvl ASC
SELECT section FROM modules WHERE area='First' GROUP BY section ORDER BY lvl ASC

edited Nov 27 '19 at 22:30

Jack Fleeting

24,385
6
23
45

answered Nov 27 '19 at 22:14

Frank

11
1

Error '80004005' only when SELECT DISTINCT with ASP Classic

1 Answers1