Solved

Executing an Aggregated SQL Query using ASP

Posted on 2003-11-12
9
353 Views
Last Modified: 2010-04-06
I am very tierd and am not seeing this.  I am developing a custom message board for my web site.  I executed a query to output the the subtopic in a main topic area.  However I would like to count the number of posts within that sub topic and list it on the same page prior to the end user going to that particular subtopic.  Since I am using an aggregate query to count the number of messages I can't pull the information from any particular table and thats where I am getting stuck.  here is the code and then I will tell you what I tried and what the results were.

Dim adoCon, rsForum, sqlQuery, games, sqlQuery2, temp
games = "games"
                  
Set adoCon = Server.CreateObject("ADODB.Connection")

adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("..\dbs\myMessageBoard.mdb")

Set rsForum = Server.CreateObject("ADODB.Recordset")

sqlQuery = "SELECT t.topicTitle FROM fourmMain as m, topicsMain as t WHERE m.allTopics = t.allTopics AND t.allTopics = "& Chr(39) & games & Chr(39)
                  
sqlQuery2 = "Select Count(*) From allTopicInfo"
sqlQuery2.execute
                  
rsForum.Open sqlQuery, adoCon

...................html.....................

Do While not rsForum.EOF
            
response.Write(rsForum("topicTitle"))
response.Write(sqlQuery2)
response.Write("<br />")
                              
rsForum.MoveNext
Loop                  
rsForum.Close
Set rsForum = Nothing
Set adoCon = Nothing

....html..........

when I try to run it without executing it, it lists my sql statement, which makes sense.  When I execute it give me an error, stating that there is something wrong with my query.  Do I need to execute the statement and then place it in another variable and then pass that variable?  Or do I have to open the statement like I did with my first query?? I am just not seeing it!!  Any help would be appreciated!!  Thanks.
0
Comment
Question by:dragon384
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 2
9 Comments
 
LVL 13

Expert Comment

by:lozloz
ID: 9737869
hi,

sqlQuery = "SELECT t.topicTitle FROM fourmMain as m, topicsMain as t WHERE m.allTopics = t.allTopics AND t.allTopics = '" & games & "'"

you've spelt forum wrong there, don't know if that's causing the problem..

also.. i had a problem using COUNT(*) in asp due to writing the brackets, so the solution was to have COUNT(*) AS count

so try:

sqlQuery2 = "Select Count(*) AS count From allTopicInfo"
and response.Write(sqlQuery2("count")) if you're planning on printing out the count later on

tell me how it goes

loz
0
 

Author Comment

by:dragon384
ID: 9738976
Wow I was tired, I posted this in the general web lang. topics!!!

thanks lozloz,

one thing that is consistant is my spelling or mis-spelling I should say.  As you can see I even misspeled tired.  But fortunately that wasnt it, but it will be something I go back and correct.  

I tried you suggestion and it didnt work work, it gave me a type mismatch on my response.write.  This error was given to me without the .execute.  With it I get an Object Required display a portion of my query.  I tried this same thing last night after I posted this message with much of the same results.  The .execute needs to be there right? or is it automatically executing it and putting it in sqlQuery2?

 
0
 
LVL 13

Expert Comment

by:lozloz
ID: 9739229
surely you have to open the rsForum connection before you execute the query?

loz
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Accepted Solution

by:
YZlat earned 150 total points
ID: 9739262
Dim adoCon, rsForum, sqlQuery, games, sqlQuery2, temp,iCount
games = "games"
               
Set adoCon = Server.CreateObject("ADODB.Connection")

adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("..\dbs\myMessageBoard.mdb")

Set rsForum = Server.CreateObject("ADODB.Recordset")

sqlQuery = "SELECT t.topicTitle FROM fourmMain as m, topicsMain as t WHERE m.allTopics = t.allTopics AND t.allTopics = "& Chr(39) & games & Chr(39)
               
sqlQuery2 = "Select Count(*) AS myCount From allTopicInfo"
set rsCount=adoCon.Execute(sqlQuery2)
if not rsCount.eof and not rsCount.bof then
        iCount=rsCount("myCount")
end if
               
set rsForum=adoCon.Execute(sqlQuery)

if not rsForum.eof and not rsForum.bof then

....do whatever to the recordset

end if

0
 

Author Comment

by:dragon384
ID: 9739733
lozloz - thats what I figured, but it ppears that I was executing it incorrectly.

YZlat - this seems to work, but I ran into a problem.  It no longer posts my other topics.  so let say this:

fungames 6

....instead of saying

fungames 6
dumbgames 2
coolgames 3
and so on.

now I put the if satatemet inside my while loop and it gave this result
fungames 6
dumbgames 6
coolgames 6

so I tried to modify my query added the join and grouped by the subTopicName and it still gave me
fungames 6
dumbgames 6
coolgames 6

here is the query I used, also I modied some of the table names, so it may be a little different from above.  This is the query that I used.

sqlQuery2 = "SELECT Count(*) AS myCount, t.topicTitle FROM topicInformation AS ti, topicsMain AS t WHERE t.topicTitle=ti.allTopicInfo GROUP BY t.topicTitle"



0
 

Author Comment

by:dragon384
ID: 9739758
Also I should mention that when I run the query in access, I only get the values back from fungames, and I would think that I would get the values back for all my topics.
0
 

Author Comment

by:dragon384
ID: 9739887
never mind the last comment, I didnt have any values in there.  However, when I did put values in there, I ge the same results when I try to outpu!!  In access it works fine, So I am thing the problem may have shifted to my control structure.  
0
 

Author Comment

by:dragon384
ID: 9740015
I made more modifications.  Since I am capturing all the data in sqlQuery2, I removed rsForum and sqlQuery altogether.  However I am having the same problem.  Only displays the first one.  I am getting close I can feel it.  I am still thinking it may be my control structure
0
 

Author Comment

by:dragon384
ID: 9740392
I got it.  FINALLY!!!


It wasnt the control structure, it was my own stupidity.  Ok this is what I did.  YZlat, based on the sugesstion you gave and my modified query,  I managed to do it all at once.  I was outputting iCount rather than outputting the field name.  I guess I was try ing to be slick!  Well it wasnt working.  I changed that to the fieldName and then I noticed that I could actually do one query rather than doing 2 seperate ones.  That way it took care of both values and is spitting out both values.  Also, which this would make sense,  I had values in the subTopics without having any values as far as messages.  So those werent showing up anyway, which they shouldnt.  Cant have a subTopic with out a messge attached to it.  This may cause me to clean the db once in a while for people posting a topic without a message or I can just validate that text field.  ANyway here is the modified code.

Dim adoCon, rsForum, sqlQuery, games, sqlQuery2
games = "games"
                  
Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("..\data\dragongraphx.mdb")
            
sqlQuery2 = "SELECT Count(ti.allTopicInfo) AS myCount, t.topicTitle, m.allTopics FROM  topicInformation AS ti, topicsMain AS t, FourmMain AS m WHERE t.topicTitle=ti.allTopicInfo GROUP BY t.topicTitle, m.allTopics HAVING m.allTopics=" & Chr(39) & games & Chr(39)
                  
set rsCount=adoCon.Execute(sqlQuery2)

.............html....................

Do While not rsCOunt.eof

response.Write(rsCount("topicTitle"))
response.Write(rsCount("myCount"))
response.Write("<br />")

rsCount.MoveNext
Loop
                              
rsCount.Close
Set rsCount = Nothing
Set adoCon = Nothing


Thanks to you both, because both of you pointed me to the correct path
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Tech Writing Terminology - Pane or Panel? 8 119
CSS Question.. 3 97
jQuery or Javascript animation question 6 73
Animated .jpg? 13 104
Shoutout to Emily Plummer (http://www.experts-exchange.com/members/eplummer26.html) for giving me this article! She did most of it, I just finished it up and posted it for her :)    Introduction In a previous article (http://www.experts-exchang…
I found this questions asking how to do this in many different forums, so I will describe here how to implement a solution using PHP and AJAX. The logical flow for the problem should be: Write an event handler for the first drop down box to get …
Viewers will learn about the regular for loop in Java and how to use it. Definition: Break the for loop down into 3 parts: Syntax when using for loops: Example using a for loop:
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

733 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