Recordset and Query Results Differ 1

[Genimuse]

I've got a page with a fairly simple query (it has a nested subquery, but it's pretty straightforward). If I display my SQL string and run it as a query in the same Access database I'm connected to in ASP, I get the results I expect, specifically something useful from the subquery part (a SUM).

However, when it's embedded in the ASP page, it always returns nothing for that subquery field. I've checked the base ASP many times (referring to the right field name, etc.), so am pretty sure that's all good.

Is there a reason the connection (through a DSN) and the direct query would return different results?

 

[Chopstik]

Though no clear answer here, this might offer some additional things to try:

http://groups.google.com/group/micr...17ddbda5dbac8a8?sa=X&oi=groupsr&start=0&num=3

I'll keep looking to see if I find anything else. Out of curiosity, what is the code you're using?

------------------------------------------------------------------------------------------------------------------------
"I am not young enough to know everything."
Oscar Wilde (1854-1900)

 

[Chopstik]

Without seeing the code, I wonder if this might be the issue...

http://www.thescripts.com/forum/thread58654.html

------------------------------------------------------------------------------------------------------------------------
"I am not young enough to know everything."
Oscar Wilde (1854-1900)

 

[Genimuse]

Bingo on the second one!

Well, I haven't tried it, and I don't have access to that system right now, but I'm sure it's right.

I wrote the query in Access first, to play with it until I got the results I wanted. I used

LIKE 'D-%'

in the query in Access and it always failed. Eventually I changed it to

LIKE 'D-*'

and it ran nicely in Access. I thought, "Hmm, I guess Access doesn't like the percent sign, it needs an asterisk... too many years of programming to SQL Server>"

And then I put the query in ASP, and whomp, it stopped working. Copied my exact strSQL over to Access, worked like a charm.

To sum, ADODB wants a % for a wildcard, while Access wants a *. If you move a query in either direction, remember to change the character.

Thanks!

 

[Genimuse]

And... I tried it, and poof, problem solved.

 

[Chopstik]

Excellent! Glad that one worked for you.

------------------------------------------------------------------------------------------------------------------------
"I am not young enough to know everything."
Oscar Wilde (1854-1900)