dragon384
asked on
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
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("..\dbs\myM
Set rsForum = Server.CreateObject("ADODB
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("to
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.
Dim adoCon, rsForum, sqlQuery, games, sqlQuery2, temp
games = "games"
Set adoCon = Server.CreateObject("ADODB
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("..\dbs\myM
Set rsForum = Server.CreateObject("ADODB
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("to
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.
ASKER
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?
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?
surely you have to open the rsForum connection before you execute the query?
loz
loz
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.allTopicIn
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.allTopicIn
ASKER
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.
ASKER
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.
ASKER
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
ASKER
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
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("..\data\dr
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.allTopicIn
set rsCount=adoCon.Execute(sql
.............html.........
Do While not rsCOunt.eof
response.Write(rsCount("to
response.Write(rsCount("my
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
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
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("..\data\dr
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.allTopicIn
set rsCount=adoCon.Execute(sql
.............html.........
Do While not rsCOunt.eof
response.Write(rsCount("to
response.Write(rsCount("my
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
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("
tell me how it goes
loz