ASP/SQL/MSAccess problem

[fluxdemon]

This sql statement works in ASP w/MSAccess
- select distinct * from [codeview]

but these aren't
- select distinct section from [codeview]
- select distinct [codeview].section from [codeview]

All three queries work when I run them in MSAccess. What do I have to change to get it to work?

 

[oldwen]

Could you post a little more of your code sample, so I can see what is happening for you.

 

[fluxdemon]

Here it is. Everything else is working... I am using the database functions for other parts of my program.


sub sqlSelect(conn,sql,rs)
rs.Open sql, conn
end sub

sub closeDatabase(conn)
conn.close
set conn=nothing
end sub

function openDatabase(conn)
conn.ConnectionString="Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & server.mappath("wp.mdb") & ";"
conn.Open
end function

dim sql,rs,conn

set conn = Server.CreateObject("ADODB.Connection")
set rs = Server.CreateObject("ADODB.Recordset")

openDatabase(conn)

sqlSelect conn,"select distinct * from [codeview]",rs
writeCodeViewMenu(rs)
closeDatabase(conn)

sub writeCodeViewMenu(rs)

if not(rs.eof) then
do until rs.eof
response.write(" <tr>" & vbCrLf)
response.write(" <td id=" & chr(34) & "codeviewmenu" & rs("id") & chr(34) & " class=" & chr(34) & "menuitem" & chr(34) & " style=" & chr(34) & "cursor: hand;" & chr(34) & vbCrLf)
response.write(" onClick=" & chr(34) & "window.location=" & chr(39) & "section.asp?section=" & rs("section") & chr(39) & ";" & chr(34) & vbCrLf)
response.write(" onMouseOver=" & chr(34) & "codeviewmenu" & rs("id") & ".align=" & chr(39) & "right" & chr(39) & ";" & chr(34) & vbCrLf)
response.write(" onMouseOut=" & chr(34) & "codeviewmenu" & rs("id") & ".align=" & chr(39) & "left" & chr(39) & ";" & chr(34) & ">" & vbCrLf)
response.write(" &nbsp;&nbsp;" & rs("section") & "&nbsp;&nbsp;" & vbCrLf)
response.write(" </td>" & vbCrLf)
response.write(" </tr>" & vbCrLf)
rs.movenext
loop
else
response.write("No sections to display.")
end if

end sub