Dealing with ASPError 80040e10

[slavdaddy]

Okay, hi everyone. I am trying to get this ODBC-Access thing working, but it seems to be really picky (or I may just be working in the dark). Anyway, the problem is that I am getting an error code telling me that I am not passing enough arguments to objConn.Execute, and that it expects one argument. I am passing it strSQL, as you can see below.
Can anyone point me in the right direction? Oh, below is the code and the output. objConn.Execute(strSQL) is on the line it is complaining about - line 17.


Script Code ------------------------------------------------

<% @LANGUAGE = VBScript %>

<%

Response.write &quot;Hi<br><BR>&quot;

Response.Expires = 0

strQuery = &quot;SELECT * FROM (CUSTOMER INNER JOIN ORDITEMS ON CUSTOMER.ORDERNO=ORDITEMS.ORDERNO) INNER JOIN ORDERS ON CUSTOMER.ORDERNO=ORDERS.ORDERNO WHERE CUSTOMER.ORDERNO=&quot; & chr(34) & &quot;003000&quot; & chr(34) & &quot; ORDER BY CUSTOMER.ORDERNO&quot;

'Making sure it does hold something
'Because of the error message
Response.write strQuery
strConnection=&quot;DSN=XXXX;Database=XXXX;&quot;
strConnection=strConnection & &quot;UID=username;PWD=password&quot;

Set objConn = Server.CreateObject(&quot;ADODB.Connection&quot
objConn.Open strConnection
Set objRS = objConn.Execute(strQuery)

count = 1
While Not objRS.EOF
'Just trying to see if it returns anything, and if so, how
'many records
Response.write count & &quot;<br>&quot; & vbcrlf
count = count + 1
objRS.MoveNext
Wend

objRS.close
objConn.close
set objRS = nothing
set objConn = nothing

%>

Script Output ---------------------------------------------

SELECT * FROM (CUSTOMER INNER JOIN ORDITEMS ON CUSTOMER.ORDERNO=ORDITEMS.ORDERNO) INNER JOIN ORDERS ON CUSTOMER.ORDERNO=ORDERS.ORDERNO WHERE CUSTOMER.ORDERNO=&quot;003000&quot; ORDER BY CUSTOMER.ORDERNO

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'

[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.

/import.asp, line 17
------------------------------------------------------------

Where line 17 has the objConn.Execute line on it. Thanks ahead of time for any help.

Thanks,
Corey Tisdale

 

[slavdaddy]

Okay, I have found the problem. It is my where clause. The whole thing works fine inside of Access, but going through ODBC makes life hell. Can someone please tell me why

strQuery = &quot;SELECT * FROM (CUSTOMER INNER JOIN ORDITEMS ON CUSTOMER.ORDERNO=ORDITEMS.ORDERNO) INNER JOIN ORDERS ON CUSTOMER.ORDERNO=ORDERS.ORDERNO WHERE CUSTOMER.ORDERNO=&quot; & chr(34) & &quot;003000&quot; & chr(34) & &quot; ORDER BY CUSTOMER.ORDERNO&quot;

doesn't work while

strQuery = &quot;SELECT * FROM (CUSTOMER INNER JOIN ORDITEMS ON CUSTOMER.ORDERNO=ORDITEMS.ORDERNO) INNER JOIN ORDERS ON CUSTOMER.ORDERNO=ORDERS.ORDERNO ORDER BY CUSTOMER.ORDERNO&quot;

does?
Thanks,
Corey

 

[slavdaddy]

Problem solved. I had to switch to using an autonumber primary key field instead of the text field I was orignally attempting to use. I don't know if this is an error due to the text nature of the beast or if it has to do with the primary keys. I do know that I like Access even less now.

So, if anyone cares, if you get this error 80040e10 and you are not mentioning any invalid tables/fields, try creating another index column, and use that to limit results while still joining on other criteria.

PEACE, Y'ALL!

-Corey