ASP/Access through OLE problem

[Darth Bagel]

I've got a client's website here written in ASP and connecting to an Access database. Their old host allowed ODBC connections, but they moved to a new host that only supports DSN-less OLE connections. Changing the site from using the ODBC DSN to the OLE connection string should have been as easy as changing the connectionstring property of the recordsets I'm using for select operations, but there's one query that's giving me a lot of crap.

sql = "SELECT pcode_item.discount, pcode_size_price.size, pcode_size_price.size_text, pcode_size_price.price"
sql = sql & " FROM pcode_item INNER JOIN pcode_size_price ON pcode_item.pcode_id = pcode_size_price.pcode_id"
sql = sql & " WHERE (((pcode_item.productID)=" & prodlist(1,i) & ") AND ((pcode_item.categoryID)=" & catID & ") AND ((pcode_item.catalogID)=1))"
sql = sql & " ORDER BY pcode_size_price.size;"

Or, in straight SQL

SELECT pcode_item.discount, pcode_size_price.size, pcode_size_price.size_text, pcode_size_price.price FROM pcode_item INNER JOIN pcode_size_price ON pcode_item.pcode_id = pcode_size_price.pcode_id WHERE (((pcode_item.productID)=288) AND ((pcode_item.categoryID)=6) AND ((pcode_item.catalogID)=1)) ORDER BY pcode_size_price.size;

When this query is executed, I receive the following:

error '80004005'

and no more. No error message (if I run it locally on a newer version of IIS it says "unspecified error" but that's just as useless as not seeing anything at all).

Points of confusion:
- Several other queries are executing perfectly fine using the same connection string, the same recordset object, the same everything except SQL code. This suggests that there's a problem with the SQL query.
- I copy/pasted the previous SQL query into an Access SQL view of a query and it executed perfectly. The result set is 2 rows for that particular set of values, so it's not bloody likely that anything is overflowing.
- Thinking that maybe it was something to do with the circumstances surrounding that particular query execution, I changed the SQL code to something trivial (a select * query from a single table), and it also executed happily, dying another 50 lines down in the page when it realized it didn't have the proper columns in the results.


Things I've tried:
- Entered the query into Access SQL view, switched to design view and then back to SQL view in the hopes of getting it to format properly
- Tried to make a parameterized query that encompassed this particular select operation into which I could just feed the parameters. I'm not sure I got it right since nobody seems to want to tell me what the actual syntax for doing such a thing is, but when I used the following SQL statement the error message went from "error in FROM clause" back to "Unspecified Error" so I'm guessing I got the syntax correct

SELECT sizes_and_prices.* FROM sizes_and_prices WHERE product=288 AND category=6 AND catalog=1

Where product, category, and catalog are defined as parameters of the sizes_and_prices query (under Query>Parameters).


Now, it appears that I've found a way around the problem by modifying sizes_and_prices so that it does no filtering, just joins pcode_item and pcode_size_price and returns the result, and then filtering the result from the SQL command like so:

SELECT sizes_and_prices.* FROM sizes_and_prices WHERE productID=288 AND categoryID=6 AND catalogID=1;

While this does work, I expect it to be even slower than a "proper" Access query, and given that it's Access I don't really have a whole lot of room for slow-downs. Can anybody give me any ideas or things to try?

 

[Nevermind]

Your where clause is missing a right paranthesis. None of your parathesises in the where clause are needed, btw, they only make your SQL harder to read.

 

[Darth Bagel]

Are you sure about the missing parenthesis? I count the right number. And yes, I know they're not needed. That's what Access did to the query, though, so I thought (hoped) it might be happier.

I think I just now made significant progress, though. It appears that OLE thinks "size" is a reserved or otherwise un-usable word, but ODBC does not (baffling, since the ODBC driver is built on OLE).

 

[Nevermind]

You're right, I'm an idiot.
Size is a reserved word in Jet, but not access, AFAIK.
http://support.microsoft.com/?kbid=248738

 

[Nn'theraq'pss]

If it's a reserved word, then just enclose the field in brackets like so to get around the error it's spewing

[size]

 

[Darth Bagel]

A further shortcut! Now I don't have to change the database, excellent.

Thanks, guys.