Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 340
  • Last Modified:

Calculating two totals from a recordset

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


Set myConn = Server.CreateObject("ADODB.Connection")
Set myCmd = Server.CreateObject("ADODB.Command")
Set myRS = Server.CreateObject("ADODB.Recordset")

myConn.Open("dsn=lifenet;uid=brianog;password=boggle")


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
.CommandType = 1 'adCmdText
End With

myRS.Open myCmd
TotalEmails=myRS.RecordCount


response.Write TotalEmails
response.end

MyConn.Close
Header.lblHeaderLeft.Value="School email addresses"

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



0
bogorman
Asked:
bogorman
  • 2
2 Solutions
 
deathtospamCommented:
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 myConn = Server.CreateObject("ADODB.Connection")
Call myConn.Open("dsn=lifenet;uid=brianog;password=boggle")
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)
Header.lblHeaderLeft.Value="School email addresses"
%>
==============================================================


-= DeathToSpam =-
0
 
jmanGJHS97Commented:
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
 
bogormanAuthor Commented:
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
 
jmanGJHS97Commented:
Glad we could help.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now