Record Count - SQLDB Query

Report · Aug 23, 2006

ASP VB SQL

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
%>
<%
' *** Recordset Stats, Move To Record, and Go To Record: declare stats variables

' 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
If (rs_last > rs_total) Then rs_last = rs_total
If (rs_numRows > rs_total) Then rs_numRows = rs_total
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
If (rs_last > rs_total) Then rs_last = rs_total

End If
%>

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}
CA: {rs_total}
NY: {rs_total}
TX: {rs_total}
VA: {rs_total}
PA: {rs_total}
etc.
etc.
etc.

Report · Aug 23, 2006

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}
>

Report · Aug 23, 2006

So I have, for example:

SELECT state, count (*) as TotalWidgets
FROM widget_db_table
GROUP BY state;

this works using enterprise manager to do a query of that table in SQL, but how do I apply this to an ASP page? How do I show all these records in a table on the page? What does the recordset look like? How can I get these results to appear in a table looking like:

FL: 234
CA: 834
NY: 123
TX: 23
VA: 234
PA: 567
ETC.

I guess I understand the SQL part, but dont completely understand how these results are going to actually appear on a page (or organize them on a page) or how to create the recordset

Report · Aug 23, 2006

Just treat it as you would any other recordset that you wish to display.
You might have to swap to "Advanced" mode to get the query in there the way
you need it, but after that, it's no different from any other recordset.

"jsteinmann" <webforumsuser@macromedia.com> wrote in message
news:ecicvu$rn1$1@forums.macromedia.com...
> So I have, for example:
>
> SELECT state, count (*) as TotalWidgets
> FROM widget_db_table
> GROUP BY state;
>
> and this works using enterprise manager to do a query of that table in
> SQL,
> but how do I apply this to an ASP page? How do I show all these records
> in a
> table on the page? How can I get these results to appear in a table
> looking
> like:
>
> FL: 234
> CA: 834
> NY: 123
> TX: 23
> VA: 234
> PA: 567
> ETC.
>
>
>
>

Report · Aug 23, 2006

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
%>

Report · Aug 23, 2006

"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. 🙂