Record Count - SQLDB Query

"jsteinmann" <webforumsuser@macromedia.com> wrote in message
news:ecihja$3r7$1@forums.macromedia.com...
>I still don't understand how I'm going to see the results on the page.
>even if
> i create a repeat region, i just get the same number for all them, and i
> can't
> figure out how to make the states display at all.

To display the state, you have to select the state. In your sample query,
you did that. In the code attached, you did not.
Take a step back an breathe deep - you've got it right, you're just
thrashing a bit and making it harder than it really is. :)

i just get the same number for all them (which isn't right)... but at least i have all the states showing up at this point...

I have:

<%
set rs = Server.CreateObject("ADODB.Recordset")
rs.ActiveConnection = MM_cnConn_STRING
rs.Source = "SELECT state, count(*) AS TotalWidgets FROM dbo.Databasetable WHERE expireDate >= getDate() GROUP BY state"
rs.CursorType = 0
rs.CursorLocation = 2
rs.LockType = 3
rs.Open()
rs_numRows = 0
%>
<%
Dim Repeat1__numRows
Dim Repeat1__index

Repeat1__numRows = -1
Repeat1__index = 0
rs_numRows = rs_numRows + Repeat1__numRows
%>

<%
' *** Recordset Stats, Move To Record, and Go To Record: declare stats variables

Dim rs_total
Dim rs_first
Dim rs_last

' set the record count
rs_total = rs.RecordCount

' set the number of rows displayed on this page
If (rs_numRows < 0) Then
rs_numRows = rs_total
Elseif (rs_numRows = 0) Then
rs_numRows = 1
End If

' set the first and last displayed record
rs_first = 1
rs_last = rs_first + rs_numRows - 1

' if we have the correct record count, check the other stats
If (rs_total <> -1) Then
If (rs_first > rs_total) Then
rs_first = rs_total
End If
If (rs_last > rs_total) Then
rs_last = rs_total
End If
If (rs_numRows > rs_total) Then
rs_numRows = rs_total
End If
End If
%>
<%
' *** Recordset Stats: if we don't know the record count, manually count them

If (rs_total = -1) Then

' count the total records by iterating through the recordset
rs_total=0
While (Not rs.EOF)
rs_total = rs_total + 1
rs.MoveNext
Wend

' reset the cursor to the beginning
If (rs.CursorType > 0) Then
rs.MoveFirst
Else
rs.Requery
End If

' set the number of rows displayed on this page
If (rs_numRows < 0 Or rs_numRows > rs_total) Then
rs_numRows = rs_total
End If

' set the first and last displayed record
rs_first = 1
rs_last = rs_first + rs_numRows - 1

If (rs_first > rs_total) Then
rs_first = rs_total
End If
If (rs_last > rs_total) Then
rs_last = rs_total
End If

End If
%>

and for the results

<%
While ((Repeat1__numRows <> 0) AND (NOT rs.EOF))
%>
<table width="450" border="0">
<tr>
<td><font size="1" face="Verdana, Arial, Helvetica, sans-serif"><font color="#000000"><%=(rs.Fields.Item("state").Value)%></font></font></td>
<td><font color="#000000" size="2" face="Verdana, Arial, Helvetica, sans-serif"><%=(rs_total)%></font></td>
</tr>
</table>
<%
Repeat1__index=Repeat1__index+1
Repeat1__numRows=Repeat1__numRows-1
rs.MoveNext()
Wend
%>

Look up GROUP BY in any SQL primer.


"jsteinmann" <webforumsuser@macromedia.com> wrote in message
news:eci5ov$ioa$1@forums.macromedia.com...
> ASP VB SQL
>
> How do I get the DB to return a count for 50 categories. I want the DB to
> tell me how many 'WIDGETS' (#) are in each 'STATE' (CA, FL, NY, etc.).
>
> To pull a single 'STATE' my query might look like this:
> <%
> set rs = Server.CreateObject("ADODB.Recordset")
> rs.ActiveConnection = MM_cnConn_STRING
> rs.Source = "SELECT count(*) AS TotalWIDGETS FROM dbo.Database WHERE
> STATE =
> 'FL'"
> rs.CursorType = 0
> rs.CursorLocation = 2
> rs.LockType = 3
> rs.Open()
> rs_numRows = 0
> %>
>
> or could also make it information that comes from a Request.QueryString
> like
> this:
>
> <%
> Dim rs__MMColParam
> rs__MMColParam = "1"
> if (Request.QueryString("state") <> "") then rs__MMColParam =
> Request.QueryString("state")
> %>
> <%
> set rs = Server.CreateObject("ADODB.Recordset")
> rs.ActiveConnection = MM_cnConn_STRING
> rs.Source = "SELECT * FROM dbo.Database WHERE state = '" +
> Replace(rs__MMColParam, "'", "''") + "' ORDER BY Date ASC"
> rs.CursorType = 0
> rs.CursorLocation = 2
> rs.LockType = 3
> rs.Open()
> rs_numRows = 0
> %>
>
>
>
> MY QUESTION IS....
>
> How can I have it pull the total record count for all 50 'STATE'
> categories so
> that I receive a page with information like this:
>
> FL: {rs_total}
>