Solved

Executing an Aggregated SQL Query using ASP

Posted on 2003-11-12
9
346 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
  • 6
  • 2
9 Comments
 
LVL 13

Expert Comment

by:lozloz
Comment Utility
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
Comment Utility
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
Comment Utility
surely you have to open the rsForum connection before you execute the query?

loz
0
 
LVL 35

Accepted Solution

by:
YZlat earned 150 total points
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:dragon384
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Preface This article introduces an authentication and authorization system for a website.  It is understood by the author and the project contributors that there is no such thing as a "one size fits all" system.  That being said, there is a certa…
Preface In the first article: A Better Website Login System (http://www.experts-exchange.com/A_2902.html) I introduced the EE Collaborative Login System and its intended purpose. In this article I will discuss some of the design consideratio…
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 receive an overview of the basics of CSS showing inline styles. In the head tags set up your style tags: (CODE) Reference the nav tag and set your properties.: (CODE) Set the reference for the UL element and styles for it to ensu…

743 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

14 Experts available now in Live!

Get 1:1 Help Now