Do untill loop

[pilg]

Hi,

I'm trying to create a stats web application using ASP. I've ran a query on my database to extract the information I require and this works fine.

The data I have extracted can either be a number between 1 and 5. I need to count up the number of 1's, 2's, 3's etc.

The loop I have written is:

<%
Do until objrs.eof
if objrs = 1 then
one + 1
end if
if objrs = 2 then
two + 1
end if
if objrs = 3 then
three + 1
end if
if objrs = 4 then
four + 1
end if
if objrs = 5 then
five + 1
end if
objrs.movenext
Loop
%>

Is this the right way to go about it?

Please help.

Pilg!

 

[mumbojumbo]

the increment you need to one = one + 1 and the objrs needs to refer to the column name ie: objrs(&quot;columnname&quot

ooooooooooooooooooooooooh bugger.

 

[Tarwn]

Another possibility, that should be more efficient, is to allow the database to do the counts for you.

I don't know anything about your requirements or table layout, so this may not work in your situation, but here is the general idea:

sqlStr = "SELECT myNumberField, count(myNumberField) as ttlCount FROM MyTable GROUP BY myNumberField ORDER BY myNumberField"

So basically what this is doing is counting how many records are returned for each number then returning your recordset with the number it counted and that total count. This could then be used like so:

Dim rs
Dim conn

'create the connection to the database
Set conn = Server.CreateObject("ADODB.Connection")
conn.Open "your connection string here"

'execute the sql query abd get the resulting recordset back into the object rs
Set rs = conn.Execute(sqlStr)

'make sure we are not at EOF (ie, no records returned)
If Not rs.EOF Then
   'move to the beginning of the recordset, always important to do this anytime you order in your sql
   rs.MoveFirst

   'Start looping through the recordset
   Do Until rs.EOF
      'Write some results for this record
      Response.Write &quot;Number: &quot; & rs(&quot;myNumberField&quot;) & &quot; has &quot; & rs(&quot;ttlCount&quot;) & &quot; entries<br>&quot;

      'queue up the next record
      rs.MoveNext
   Loop
Else
   'in this case nothing ws returned, so we should output a message to that effect
   Response.Write &quot;No records returned.&quot;
End If

So basically if the numbers only range from 1 to 5, then we will get at most 5 records back. In each record there will be two fields, myNumberField and the new field we created in the sql statement: count(myNumberField) as ttlCount
Basically we told it to execute this count function and then name the resulting field ttlCount.

-Tarwn

_{01010100 01101001 01100101 01110010 01101110 01101111 01101011 00101110 01100011 01101111 01101101}
^{29 3K 10 3D 3L 3J 3K 10 32 35 10 3E 39 33 35 10 3K 3F 10 38 31 3M 35 10 36 3I 35 35 10 3K 39 3D 35 10 1Q 19}
Do you know how hot your computer is running at home? I do

http://www.tiernok.com/hscripts/systemp.asp