Solved

SQl question...

Posted on 2006-11-20
9
230 Views
Last Modified: 2010-04-09
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
0
Comment
Question by:AndyKeen
  • 3
  • 2
  • 2
  • +2
9 Comments
 
LVL 11

Accepted Solution

by:
Isisagate earned 500 total points
ID: 17982422
does this work....
SQL="SELECT Address FROM Listings WHERE Address='London'"

?
0
 
LVL 1

Author Comment

by:AndyKeen
ID: 17982451
Hi Isisgate.

Thanks for the info - sadly - page cannot be displayed which to me means the code is not working...

Any ideas?
0
 
LVL 49

Expert Comment

by:Ryan Chong
ID: 17984399
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

?
0
 
LVL 16

Expert Comment

by:ThinkPaper
ID: 17990275
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.
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 16

Expert Comment

by:ThinkPaper
ID: 17990287
also - you'd get more help if you posted this in the SQL section...
0
 
LVL 12

Expert Comment

by:jessegivy
ID: 17993959
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.  
0
 
LVL 1

Author Comment

by:AndyKeen
ID: 17994233
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.
0
 
LVL 16

Expert Comment

by:ThinkPaper
ID: 17995315
>>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. =)
0
 
LVL 12

Expert Comment

by:jessegivy
ID: 17997441
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 :)
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
CSS grid style change 2 34
SVG Star 4 35
would like the bottom of this page to be smaller 3 18
how to handle long text strings in javascript or ajax 1 7
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Showing your events from Google Calendar in Google Maps Why? I travel all week and I thought it would be ideal if staff in office knew where I was based on my calendar. (OK real reason: my son wanted to see where I would be working, and I thoug…
In this tutorial viewers will learn how add a scalable full-width header using CSS3. Create a new HTML document with an internal stylesheet. Set a tiled background.:  Create a new div and name it Header. Position it with position:absolute at the top…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

920 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now