Calculating two totals from a recordset
Posted on 2007-10-01
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
UserID = Connection.ToSQL(CCGetUserID(), ccsInteger)
Set Connection = Nothing
On Error Goto 0
Set myConn = Server.CreateObject("ADODB.Connection")
Set myCmd = Server.CreateObject("ADODB.Command")
Set myRS = Server.CreateObject("ADODB.Recordset")
SQLEmails = "SELECT COUNT(*) FROM tblselectedschools WHERE fldUserID = " & UserID & " AND TRIM(tblselectedschools.fldEmail) <> ''"
SQLNoEmails = "SELECT COUNT(*) FROM tblselectedschools WHERE fldUserID = " & UserID & " AND TRIM(tblselectedschools.fldEmail) = ''"
.ActiveConnection = myConn
.CommandText = SQLEmails ' This should be your SQL select statement
.CommandType = 1 'adCmdText
Header.lblHeaderLeft.Value="School email addresses"
I am sure there is an easier way of doing this. Would be grateful for anyone's help.