Welcome to WebmasterWorld Guest from 54.227.68.206

Forum Moderators: ocean10000

Message Too Old, No Replies

Query works in SQL Srvr not in Access/ASP

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

Junior Member

10+ Year Member

joined:May 11, 2003
posts: 56
votes: 0


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 Sept 15, 2010 (gmt 0)

Administrator

WebmasterWorld Administrator 10+ Year Member Top Contributors Of The Month

joined:Jan 14, 2004
posts:859
votes: 3


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 Sept 15, 2010 (gmt 0)

Junior Member

10+ Year Member

joined:May 11, 2003
posts:56
votes: 0


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 Sept 18, 2010 (gmt 0)

Full Member

10+ Year Member

joined:May 14, 2001
posts:262
votes: 0


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 Sept 18, 2010 (gmt 0)

Junior Member

10+ Year Member

joined:May 11, 2003
posts: 56
votes: 0


 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!