Solved

Count Records Query

Posted on 2014-02-06
12
459 Views
Last Modified: 2014-02-07
I need to count records that meet certain criteria. I thought this was very straightforward but I'm missing something.
	    sql = "SELECT COUNT(*) AS iTotal FROM studentsAll WHERE (lastName = '" & last_name & "' AND firstName = '" & first_name & "');"
		Set rs=conn.Execute(sql)
		   		  
		textfile.WriteLine Now
		textfile.WriteLine "<BR><B>SQL:   </B>  " & (sql)
		textfile.WriteLine "<BR><B>Total:   </B>  " & iTotal
		textfile.WriteLine "<br>===============================<p>"

Open in new window

     
Nothing prints for iTotal. I've tried CInt(iTotal) and it always produces a zero.
0
Comment
Question by:slegy
  • 6
  • 5
12 Comments
 
LVL 32

Expert Comment

by:Big Monty
ID: 39840400
there's nothing wrong with the actual sql, my guess is it's the where clause that causing the issue. did you try running the query directly in the database, plugging in values for first and last name? are those fields matched up case-wise, meaning if they're all lower case, then that is how it should appear in the query
0
 

Author Comment

by:slegy
ID: 39840422
Log:
2/6/2014 3:57:07 PM
SQL:  SELECT COUNT(memberID) AS iTotal FROM membersAll WHERE (lastName = 'Ellis' AND firstName = 'Scot');
Total:

Partial database fields:
ELLSC000      CN      173      Mr      Ellis      Scot                        M
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 39840427
that log is showing a table of membersAll where your original query is showing a table of studentsAll
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 39840428
where do last_name and first_name come from? A form?
0
 

Author Comment

by:slegy
ID: 39840438
Stumbled across this example and it works:

	    sql = "SELECT COUNT(memberID) AS iTotal FROM membersAll WHERE (lastName = '" & last_name & "' AND firstName = '" & first_name & "');"
		Set rs=conn.Execute(sql)
		   		  
		textfile.WriteLine Now
		textfile.WriteLine "<BR><B>SQL:   </B>  " & (sql)
		textfile.WriteLine "<BR><B>Total:   </B>  " & rs("iTotal")
		textfile.WriteLine "<br>===============================<p>"	

	    If rs("iTotal") > 0 Then

Open in new window

I'm a little confused!
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 39840450
thats because you're selecting from a different table
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:slegy
ID: 39840487
I changed the name of the table when I posted the question. I'm confused about the rs("iTotal") causing it to work. None of the examples I've been reviewing for the past couple of hours looked like this.
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 39840503
it didnt work originally because you never assigned the database value to the asp variable iTotal, that is why nothing was showing. Once you did that, it displayed as it should have. if you changed your code to the following, it'll also work:

sql = "SELECT COUNT(memberID) AS iTotal FROM membersAll WHERE (lastName = '" & last_name & "' AND firstName = '" & first_name & "');"
		Set rs=conn.Execute(sql)
		iTotal = rs("iTotal")   		  
		textfile.WriteLine Now
		textfile.WriteLine "<BR><B>SQL:   </B>  " & (sql)
		textfile.WriteLine "<BR><B>Total:   </B>  " & iTotal
		textfile.WriteLine "<br>===============================<p>"

Open in new window

0
 

Author Comment

by:slegy
ID: 39840539
I so apologize for confusing this. My initial changes to the query were meant to make it generic and remove actual names - which I did not do so well. last_name and first_name come from info returned from a social media login.

I just ran a test where I had changed the code in several places and it worked:

	    sql = "SELECT COUNT(memberID) AS iTotal FROM membersAll WHERE (lastName = '" & last_name & "' AND firstName = '" & first_name & "');"
		Set rs=conn.Execute(sql)
		   		  
		textfile.WriteLine Now
		textfile.WriteLine "<BR><B>SQL:   </B>  " & (sql)
		textfile.WriteLine "<BR><B>Total:   </B>  " & rs("iTotal")
		textfile.WriteLine "<br>===============================<p>"	

	    If rs("iTotal") > 0 Then...

Open in new window

iTotal is not a field in the database.
0
 
LVL 32

Accepted Solution

by:
Big Monty earned 250 total points
ID: 39841986
correct, iTotal is an alias in your query that counts the number of records (memberID) that meets you where clause conditions.

the latest code you just gave us, is that now working? if so, do you have any other questions?  I'm guessing there may be a disconnect in what you're asking and what we're understanding...not a big deal, i just want to make sure you get this resolved :)
0
 

Author Closing Comment

by:slegy
ID: 39842389
Yes, everything is working well. I guess I just didn't completely understand how the "AS" works. As always, you've been great. Thank you.
0
 
LVL 32

Expert Comment

by:Big Monty
ID: 39842586
many thanks :)

think of the AS keyword that allows you to name a column whatever you like:

select count( userID ) as numberOfUsers, firstName + ' ' + lastName as fullName, ......
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now