AndyKeen
asked on
SQl question...
Hi All.
We have a web site we are designing but our client has asked for something a little different.
Currently the web site uses the following code to display ALL items from a list with a TOTAL for each item next to it :
SQL="SELECT Distinct Address FROM Listings ORDER BY Address ASC"
set rs=conn.execute(SQL)
do while not rs.eof
Total = conn.execute("SELECT count(Address) FROM Listings WHERE Address ='"&rs("Address")&"'")
This works fine.
However what we now require is for one item in the 'listing' table to be displayed according to a criteria:
i.e. display all addresses for a specific 'x' area and a total of houses in that area - so our code so far at the moment (Which does not work) looks like this:
SQL=(SELECT Address FROM Listings WHERE Address="London")
Why is this not working???
Other info:
These are ASP pages
The database is access
Could someone help us out with the right code and also to summarise next to the location - the total number of "London's" in the list.
Many thanks
Andy
We have a web site we are designing but our client has asked for something a little different.
Currently the web site uses the following code to display ALL items from a list with a TOTAL for each item next to it :
SQL="SELECT Distinct Address FROM Listings ORDER BY Address ASC"
set rs=conn.execute(SQL)
do while not rs.eof
Total = conn.execute("SELECT count(Address) FROM Listings WHERE Address ='"&rs("Address")&"'")
This works fine.
However what we now require is for one item in the 'listing' table to be displayed according to a criteria:
i.e. display all addresses for a specific 'x' area and a total of houses in that area - so our code so far at the moment (Which does not work) looks like this:
SQL=(SELECT Address FROM Listings WHERE Address="London")
Why is this not working???
Other info:
These are ASP pages
The database is access
Could someone help us out with the right code and also to summarise next to the location - the total number of "London's" in the list.
Many thanks
Andy
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
try use a single sql statement like this?
SQL="SELECT Address, Count(Address) As Cnt FROM Listings Group By Address ORDER BY Address ASC"
set rs=conn.execute(SQL)
do while not rs.eof
Total = rs("cnt")
...
rs.movenext
loop
?
SQL="SELECT Address, Count(Address) As Cnt FROM Listings Group By Address ORDER BY Address ASC"
set rs=conn.execute(SQL)
do while not rs.eof
Total = rs("cnt")
...
rs.movenext
loop
?
what error are you getting? make sure your "display friendly HTTP errors" on IE is off. You'd get a better error message. Also consider right clicking and view source of the page. It should help you see where the code failed.
also - you'd get more help if you posted this in the SQL section...
Some of us HTML experts are actually SQL gurus believe it or not. The statement from isisgate seems fine to me, Are you certain you've entered it exactly? Perhaps you should repost your code, and I agree, that if you're getting page not displayed ya need to get another server cause, ya gotta trap those errors, you could do it manually, looks like VB?
...Anyway, I'd like to see the current code you're using to attempt to extract the goods from the database, it's possible there's an error somewhere else other than the SQL.
...Anyway, I'd like to see the current code you're using to attempt to extract the goods from the database, it's possible there's an error somewhere else other than the SQL.
ASKER
Hi All.
Thanks - Isisgate did in fact give me the correct code - I however did not do the rest correctly.
Upon examination - and correction of my error - all now works fine.
Thanks you.
Thanks - Isisgate did in fact give me the correct code - I however did not do the rest correctly.
Upon examination - and correction of my error - all now works fine.
Thanks you.
>>Some of us HTML experts are actually SQL gurus believe it or not.
Not trying to imply anything like that. Simply pointing out that he would probably get more responses posting in the appropriate section. =P
Andy - Nice to know you got it fixed. =)
Not trying to imply anything like that. Simply pointing out that he would probably get more responses posting in the appropriate section. =P
Andy - Nice to know you got it fixed. =)
It's hard to convey tone on these pages. ThinkPaper, I'm perhaps a bit too sarcastic. I wasn't offended at all, I agree it's great he got it up and running and you're totally right that this isn't the appropriate TA. ...but I think y'all did a fine job :)
ASKER
Thanks for the info - sadly - page cannot be displayed which to me means the code is not working...
Any ideas?