ASp Results Problem

[Ed Richter]

I'm accessing data from a database and attempting to post it to a page in tabular form. The results includes the number of responses and average values of some of the columns of data.

I first created the query as shown below which seems to be working fine:


<%
Dim objConn
Dim rsobj

League = Request.Form("League")

Set objConn = Server.CreateObject("ADODB.Connection")

objConn.Open Application("cwvo_ConnectionString")
Server.ScriptTimeOut = 600

Set rsObj= objConn.Execute("SELECT Team, Count(Team), Avg(Sportsmanship_Score), Avg(Coach_Score) FROM Sportsmanship WHERE League = '" & League & "' Group by Team ORDER BY Team ASC")

If rsObj.EOF <> true then

rsObj.MoveFirst()
WHILE rsObj.EOF <> true

%>



Next I put a table into the page and attempted to place the results into their respective columns. This is where the problem occurs. The first column rsObj("Team") works fine, but I know the way I'm attempting to write the results of the count and average columns is not in the correct format, but don't know what the correct format should be.


<tr>
<td width="231"><font face="arial,helv" size="3"><% = rsObj("Team") %></td>
<td width="127"><% = rsObj (Count("Team")) %>&nbsp;</td>
<td width="121"><% = rsObj(Avg("Sportsmanship_Score")) %>&nbsp;</td>
<td width="122"><% = rsObj(Avg("Coach_Score")) %>&nbsp;</td>
</tr>






<%


rsObj.MoveNext()
WEND

End IF


%>


</table>
</div>


I've used this basic method many times with satisfactory results when just posting the results, without doing any type of math operations. Apparently when perfoming operations like this, I need a different format??

Can someone explain what proper format for these types of operations should be?? Thanks!

 

[Kevin Spencer]

After looking over your code, it seems that you may be experiencing an error
that is related to the columns in your query. Aggregate columns have to be
assigned an alias in your query, or you won't know what the derived column
name will be (the database will assign one that it makes up. Here's an
example:

Set rsObj= objConn.Execute("SELECT Team, Count(Team) AS TeamCount,
Avg(Sportsmanship_Score) AS SportsmanshipAverage, Avg(Coach_Score) As
CoachAverage FROM Sportsmanship WHERE League = '" & League & "' Group by
Team ORDER BY Team ASC")

Then you refer to the aliases in your code.

--
HTH,
Kevin Spencer
..Net Developer
Microsoft MVP
Big things are made up
of lots of little things.

I'm accessing data from a database and attempting to post it to a page in
tabular form. The results includes the number of responses and average
values of some of the columns of data.

I first created the query as shown below which seems to be working fine:


<%
Dim objConn
Dim rsobj

League = Request.Form("League")

Set objConn = Server.CreateObject("ADODB.Connection")

objConn.Open Application("cwvo_ConnectionString")
Server.ScriptTimeOut = 600

Set rsObj= objConn.Execute("SELECT Team, Count(Team),
Avg(Sportsmanship_Score), Avg(Coach_Score) FROM Sportsmanship WHERE League =
'" & League & "' Group by Team ORDER BY Team ASC")

If rsObj.EOF <> true then

rsObj.MoveFirst()
WHILE rsObj.EOF <> true

%>



Next I put a table into the page and attempted to place the results into
their respective columns. This is where the problem occurs. The first
column rsObj("Team") works fine, but I know the way I'm attempting to write
the results of the count and average columns is not in the correct format,
but don't know what the correct format should be.


<tr>
<td width="231"><font face="arial,helv" size="3"><% =
rsObj("Team") %></td>
<td width="127"><% = rsObj (Count("Team")) %>&nbsp;</td>
<td width="121"><% = rsObj(Avg("Sportsmanship_Score"))
%>&nbsp;</td>
<td width="122"><% = rsObj(Avg("Coach_Score")) %>&nbsp;</td>
</tr>






<%


rsObj.MoveNext()
WEND

End IF


%>


</table>
</div>


I've used this basic method many times with satisfactory results when just
posting the results, without doing any type of math operations. Apparently
when perfoming operations like this, I need a different format??

Can someone explain what proper format for these types of operations should
be?? Thanks!

 

[Ed Richter]

Your suggestion fixed it. I kind of figured that was the problem, but I
didn't know how to format the alias.

Your code took care of it nicely. Thanks for the help!