Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 358
  • Last Modified:

Executing an Aggregated SQL Query using ASP

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
dragon384
Asked:
dragon384
  • 6
  • 2
1 Solution
 
lozlozCommented:
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
 
dragon384Author Commented:
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
 
lozlozCommented:
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!

 
YZlatCommented:
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
 
dragon384Author Commented:
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
 
dragon384Author Commented:
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
 
dragon384Author Commented:
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
 
dragon384Author Commented:
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
 
dragon384Author Commented:
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

Technology Partners: 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!

  • 6
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now