Welcome to WebmasterWorld Guest from 54.163.84.187

Forum Moderators: ocean10000

Query works in SQL Srvr not in Access/ASP

   
7:53 am on Sep 15, 2010 (gmt 0)

10+ Year Member



I have this query working in SQL, but it does not work when passed in via ASP into MS Access:

SELECT m.menu_title, (l.menu_count / (l.menu_lastaccess-m.menu_created)) AS HitsPerDay, m.menu_id
FROM webdb.mdb.tblMenu AS m,
webdblog.mdb.tblMenuCounter AS l
WHERE m.menu_hidden = False
AND m.menu_accesslevel = 0
AND l.menu_id = m.menu_id
AND HitsPerDay >= 1
ORDER BY HitsPerDay DESC;



ASP reports the erro as: Microsoft OLE DB Provider for ODBC Drivers (0x80040E10)
[Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1.


When I remove either or both lines referencing HitsPerDay it works:

SELECT m.menu_title, (l.menu_count / (l.menu_lastaccess-m.menu_created)) AS HitsPerDay, m.menu_id
FROM webdb.mdb.tblMenu AS m,
webdblog.mdb.tblMenuCounter AS l
WHERE m.menu_hidden = False
AND m.menu_accesslevel = 0
AND l.menu_id = m.menu_id;


Any hint would be much appreciated. Thanks.
1:51 pm on Sep 15, 2010 (gmt 0)

WebmasterWorld Administrator 10+ Year Member Top Contributors Of The Month



try the following. I don't think access handles HitsPerDay the same way as MS SQL does.

SELECT m.menu_title, (l.menu_count / (l.menu_lastaccess-m.menu_created)) AS HitsPerDay, m.menu_id
FROM webdb.mdb.tblMenu AS m,
webdblog.mdb.tblMenuCounter AS l
WHERE m.menu_hidden = False
AND m.menu_accesslevel = 0
AND l.menu_id = m.menu_id
AND (l.menu_count / (l.menu_lastaccess-m.menu_created)) >= 1
ORDER BY (l.menu_count / (l.menu_lastaccess-m.menu_created)) DESC;
9:19 pm on Sep 15, 2010 (gmt 0)

10+ Year Member



Thank you kindly -- this works!

I played with the query in design view, and sorted on the HitsPerDay, which generated the code for the ORDER BY, which I then copied into the AND >=1.

Again, thank you kindly for your reply!
2:35 am on Sep 18, 2010 (gmt 0)

10+ Year Member



You can also use ordinal positions in your SORT BY in Access. So you could have Sort By 1 (I think it is 0-based but you can test to confirm)
5:41 am on Sep 18, 2010 (gmt 0)

10+ Year Member



 SELECT m.menu_title, (l.menu_count / (l.menu_lastaccess-m.menu_created)) AS HitsPerDay, m.menu_id
FROM webdb.mdb.tblMenu AS m,
webdblog.mdb.tblMenuCounter AS l
WHERE m.menu_hidden = False
AND m.menu_accesslevel = 0
AND l.menu_id = m.menu_id
AND (l.menu_count / (l.menu_lastaccess-m.menu_created)) >= 1
ORDER BY 1 DESC;


... works too :)
Thanks!
 

Featured Threads

Hot Threads This Week

Hot Threads This Month