ASP - Weird SQL problem

[toastyman]

Hey all,

For a university project i'm being forced to use Visual Studio Web Developer and ASP - I'm hating it so far!

My latest problem is running a query. When running the page I get:

Unspecified error (Exception from HRESULT: 0x80004005 (E_FAIL))
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

The debugger highlights the line

rs.open(strSQL, conn)

Now here's the interesting bit... it only comes up with that error for this query:

SELECT DISTINCT coremodules.course, users.first_name, users.last_name FROM users INNER JOIN (teachers INNER JOIN ((modules INNER JOIN coremodules ON modules.ID = coremodules.module) INNER JOIN teachermodules ON modules.ID = teachermodules.module) ON teachers.ID = teachermodules.teacher) ON users.ID = teachers.uid WHERE (((coremodules.course)=1

If I run this query it works fine:

SELECT users.first_name, users.last_name FROM users

I've tested both queries in the database and they both run fine. Can anyone shed any light as to what's going wrong?

Here's the code:

<% 
    Dim strConnection As Object
    Dim conn As Object
    Dim strSQL As String

    strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & Server.MapPath(".\App_Data\Database.mdb")

    conn = Server.CreateObject("ADODB.Connection")
    conn.Open(strConnection)

    Dim rs As Object
    rs = Server.CreateObject("ADODB.recordset")

    strSQL = "SELECT DISTINCT coremodules.course, users.first_name, users.last_name FROM users INNER JOIN (teachers INNER JOIN ((modules INNER JOIN coremodules ON modules.ID = coremodules.module) INNER JOIN teachermodules ON modules.ID = teachermodules.module) ON teachers.ID = teachermodules.teacher) ON users.ID = teachers.uid WHERE (((coremodules.course)=1))"
    'strSQL = "SELECT users.first_name, users.last_name FROM users"
        
    rs.open(strSQL, conn)
            
    While Not rs.EOF
         Response.Write(rs.Fields("first_name").value)
         rs.moveNext()
    End While

    conn.Close()
    conn = Nothing
%>

 

[Mel_P]

Does "coremodules.course" exist? Spelt correctly?
Try missing out "Distinct"?

 

[toastyman]

Tried taking out both of those items but neither worked

 

[RockLobster]

Try creating the query in Access and then access your query in your ASP.NET code see if that works. I just want to see if it works to rule something out.

 

[toastyman]

Try creating the query in Access and then access your query in your ASP.NET code see if that works. I just want to see if it works to rule something out.

Recreated the query in access and that works. Copied and pasted it to database explorer in visual studio and it worked. Ran the query in the asp code specified in the 1st post and it kicks up the error.

 

[RockLobster]

Recreated the query in access and that works. Copied and pasted it to database explorer in visual studio and it worked. Ran the query in the asp code specified in the 1st post and it kicks up the error.

Maybe I'm getting confused? If you create the query in Access, save it as a query and then try using it in your SQL code:-

'strSQL = "SELECT first_name, last_name FROM MyQueryICreated"

but retrieve all the fields you need to.

 

[toastyman]

Aha, just found out the problem. I defined the relationships in Access, but when I just looked using the database manager in visual studio, all the relationships were completely wrong and linked to random tables!!

Now i've redone the relationships in visual studio the query works fine Cheers to both of you for your ideas.