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
Solved

Executing an Aggregated SQL Query using ASP

Posted on 2003-11-12
9
352 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
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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

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

Suggested Solutions

Article by: Matthew
I am a very big proponent of technology compliance standards and strive to meet such criteria in all of my work. That includes my site, which is 100% XHTML 1.0 compliant as determined by the World Wide Web Consortium. https://www.matthewstevenkel…
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…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)

856 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