homepage Welcome to WebmasterWorld Guest from 54.234.225.23
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member

Visit PubCon.com
Home / Forums Index / Microsoft / Microsoft IIS Web Server and ASP.NET
Forum Library, Charter, Moderators: ocean10000

Microsoft IIS Web Server and ASP.NET Forum

    
Query works in SQL Srvr not in Access/ASP
MaxGrenk




msg:4202020
 7:53 am on Sep 15, 2010 (gmt 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.

 

Ocean10000




msg:4202146
 1:51 pm on Sep 15, 2010 (gmt 0)

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;

MaxGrenk




msg:4202436
 9:19 pm on Sep 15, 2010 (gmt 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!

bmcgee




msg:4203543
 2:35 am on Sep 18, 2010 (gmt 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)

MaxGrenk




msg:4203582
 5:41 am on Sep 18, 2010 (gmt 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!

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Microsoft / Microsoft IIS Web Server and ASP.NET
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved