Query works in Access, not in ASP

Spudhead · #1 (**permalink**) 06-21-04, 11:58

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

Seppuku · #2 (**permalink**) 06-21-04, 12:25

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"

Spudhead · #3 (**permalink**) 06-22-04, 04:56

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.

Seppuku · #4 (**permalink**) 06-22-04, 11:08

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.

Internet Services

Web Development

Digital Marketing

Consumer Tech