Solved

# Calculating two totals from a recordset

Posted on 2007-10-01
337 Views
I am using CodeCharge Studio to develop a website.
In the BeforeShow event of a webpage, I want to count:
1. the number of records which have a particular field which has data in it
2. the number of records where this field has no data in it.
I have used this coding which displays -1 for the first.

Dim SQLEmails, SQLNoEmails, TotalEmails, TotalNoEmails, UserID, Connection
Dim myConn, myCmd, myRS

Set Connection = New clsDBConnection1
Connection.Open

UserID = Connection.ToSQL(CCGetUserID(), ccsInteger)

Connection.Close
Set Connection = Nothing
On Error Goto 0

SQLEmails = "SELECT COUNT(*) FROM tblselectedschools WHERE fldUserID = " & UserID & " AND TRIM(tblselectedschools.fldEmail) <> ''"
SQLNoEmails = "SELECT COUNT(*) FROM tblselectedschools WHERE fldUserID = " & UserID & " AND TRIM(tblselectedschools.fldEmail) = ''"

With myCmd
.ActiveConnection = myConn

.CommandText = SQLEmails ' This should be your SQL select statement
End With

myRS.Open myCmd
TotalEmails=myRS.RecordCount

response.Write TotalEmails
response.end

MyConn.Close

I am sure there is an easier way of doing this. Would be grateful for anyone's help.

0
Question by:bogorman

LVL 9

Accepted Solution

Bogorman --

Give something like this a shot:

==============================================================
<%
Dim SQLEmails, SQLNoEmails, TotalEmails, TotalNoEmails, UserID, Connection
Dim myConn, myCmd, myRS

Set Connection = New clsDBConnection1
Connection.Open

UserID = Connection.ToSQL(CCGetUserID(), ccsInteger)

Connection.Close
Set Connection = Nothing
On Error Goto 0

SQLEmails = "SELECT COUNT(*) FROM tblselectedschools WHERE fldUserID = " & UserID & " AND TRIM(tblselectedschools.fldEmail) <> ''"
SQLNoEmails = "SELECT COUNT(*) FROM tblselectedschools WHERE fldUserID = " & UserID & " AND TRIM(tblselectedschools.fldEmail) = ''"
sSqlQuery = "SELECT emailIsNotBlank = (" & SQLEmails & "), emailIsBlank = (" & SQLNoEmails & ")"

Set myRS = myConn.Execute(sSqlQuery)
iEmailIsNotBlank = CLng(myRS("emailIsNotBlank"))
iEmailIsBlank = CLng(myRS("emailIsBlank"))
Set myRS = Nothing
Call myConn.Close()
Set myConn = Nothing

Call Response.Write("There are " & iEmailIsNotBlank & "entries with non-blank emails.<br>" & vbNewLine)
Call Response.Write("There are " & iEmailIsBlank & "entries with blank emails.<br>" & vbNewLine)
%>
==============================================================

-= DeathToSpam =-
0

LVL 9

Assisted Solution

Use a UNION to do it in 1 query, which will save you a round-trip to the DB.

SELECT SUM(P.NotNullCnt) AS NotNullCount, SUM(P.NullCnt) AS NullCount
FROM
(
SELECT
COUNT(*) AS NotNullCnt,
0 AS NullCnt
FROM tblselectedschools t
WHERE fldUserID = " & UserID & " AND RTRIM(t.fldEmail) = ''

UNION

SELECT
0 AS NotNullCnt,
COUNT(*) AS NullCnt
FROM tblselectedschools t
WHERE fldUserID = " & UserID & " AND RTRIM(t.fldEmail) <> ''
) AS P
0

Author Comment

Hi,
Thanks so much for your help.
Could not get deathtospam's SQL to work, but pasted jman's in its place and the combined code is excellent.
Thank you both again. Have divided the points.
Brian
0

LVL 9

Expert Comment

0

## Featured Post

### Suggested Solutions

Hello, all! I just recently started using Microsoft's IIS 7.5 within Windows 7, as I just downloaded and installed the 90 day trial of Windows 7. (Got to love Microsoft for allowing 90 days) The main reason for downloading and testing Windows 7 is t…
When it comes to writing scripts for a Client/Server computing environment it is essential to consider some way of enabling the authentication functionality within a script. This sort of consideration mainly comes into the picture when we are dealin…
The viewer will learn how to use and create keystrokes in Netbeans IDE 8.0 for Windows.
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.