Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: Query works in Access, not in ASP

    Hello chaps.

    Ok; just built a query in Access 2000:

    Code:
    SELECT
    [tblRooms].[id],
    [tblRooms].[roomName],
    [tblDirectionConstraints].[userLevel],
    [tblDirectionConstraintObjects].[objectID]
    FROM ((([tblRoomDirections]
    INNER JOIN [tblRooms] ON [tblRoomDirections].[targetRoomID] = [tblRooms].[id])
    LEFT JOIN [tblDirectionConstraints] ON [tblDirectionConstraints].[roomID]=[tblRoomDirections].[roomID] AND [tblDirectionConstraints].[directionID]=[tblRoomDirections].[directionID])
    LEFT JOIN [tblDirectionConstraintObjects] ON [tblDirectionConstraintObjects].[directionConstraintID] = [tblDirectionConstraints].[id])
    WHERE [tblRoomDirections].[roomID] = 4 AND [tblRoomDirections].[directionID] = 1;
    The above code is pasted faithfully from a "response.write(strSQL)" debug call. If I paste it into Access query builder thingy and run it, I get results. If I run the SQL query from ASP, I get an error, "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another", on the line that opens the recordset.

    I can think of a few possible causes:

    1. JOIN types - does Access not like LEFT JOIN or something?
    2. AND in a JOIN...ON statement - can I do that? Join tables on TWO shared fields?
    3. Something else I've overlooked? Syntax screwed? Typo?

    heeelp

  2. #2
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    Can you post the code where your ASP is executing the SQL? You could also try "INNER JOIN" instead of "LEFT JOIN"... unless you want that to be an outer join, then do "LEFT OUTER JOIN"
    That which does not kill me postpones the inevitable.

  3. #3
    Join Date
    Jan 2002
    Posts
    189

    Talking

    I'm sorry. I've been a complete muppet.

    Actual execution is in the following function:
    Code:
    function doQuery(strSQL)
    	'response.write(strSQL)
    	dim rs, rv
    	set rs=server.createobject("ADODB.Recordset")
    	rs.open strSQL, cn, 1
    	if not rs.EOF then
    		rv=rs.getRows()
    	else
    		rv=strSQL
    	end if
    	rs.close
    	set rs=nothing
    	doQuery=rv
    end function
    Where "cn" is always, infallibly, completely without fail, a database connection object that I'd remembered to create and open before calling the function.

    Or, umm, not.
    Last edited by Spudhead; 06-22-04 at 06:01.

  4. #4
    Join Date
    Jul 2003
    Location
    SoCal
    Posts
    721
    I honestly can't remember if you supply a value for cursor type, you may be required to supply values for the rest.

    Try:
    rs.open strSQL, cn, 1, 3, &H0001

    Or if you have an ADO constants file:
    rs.open strSQL, cn, adOpenKeySet, adLockOptimistic, adCmdText

    I know you said it, but I'll reiterate it, make sure cn is instantiated and open.
    That which does not kill me postpones the inevitable.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •