Classic ASP Discussion - SQL Syntax

Print Page | Close Window

SQL Syntax - COUNT(*)

Printed From: Web Wiz Forums
Category: General Discussion
Forum Name: Classic ASP Discussion
Forum Description: Discussion on Active Server Pages (Classic ASP).
URL: https://forums.webwiz.net/forum_posts.asp?TID=17517
Printed Date: 29 March 2026 at 10:14am
Software Version: Web Wiz Forums 12.08 - https://www.webwizforums.com

Topic: SQL Syntax - COUNT(*)

Posted By: faca5
Subject: SQL Syntax - COUNT(*)
Date Posted: 12 December 2005 at 6:03am

Very easy question. What is wrong? Why I get "-1" If table have some record?


Dim aaaa

Set adoCon = Server.CreateObject("ADODB.Connection")

adoCon.Open = "Provider=Microsoft.Jet.OLEDB.4.0; 

Data Source=" & Server.MapPath("testing.mdb")

Set testing = Server.CreateObject("ADODB.Recordset")

strSQL = "SELECT COUNT(*) FROM table;" 
 
testing.Open strSQL, adoCon
 

 
 aaaa = testing.RecordCount

 Response.Write aaaa

 
 
testing.Close

Set testing = Nothing

Set adoCon = Nothing

-------------
http://www.studiofaca.com/" rel="nofollow - StudioFACA Portal :: http://www.renes.si/" rel="nofollow - Nepremičnine

Replies:

Posted By: ljamal
Date Posted: 12 December 2005 at 11:16am

The RecordCount Method isn't supported by some cursors and it will return -1. However, your SQL statement is returning a count so use:


aaaa = testing(0)

-------------
L. Jamal Walton

http://www.ljamal.com/" rel="nofollow - L. Jamal Inc : Web/ Print Design and ASP Programming

Posted By: jeffdaro
Date Posted: 12 December 2005 at 11:29am

You need a client side cursor to get a count from the recordset.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdconcursorlocationsignificance.asp - http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdconcursorlocationsigni ficance.asp

Posted By: faca5
Date Posted: 12 December 2005 at 1:30pm

Ok. Thanks!

P.S. Juhu! It work!

-------------
http://www.studiofaca.com/" rel="nofollow - StudioFACA Portal :: http://www.renes.si/" rel="nofollow - Nepremičnine

Posted By: faca5
Date Posted: 12 December 2005 at 1:51pm

Another easy question.

How I can GROUP BY some argument?


strSQL = "SELECT COUNT(*) FROM table GROUP BY argument;"

I get "1"? It isn't correct!

-------------
http://www.studiofaca.com/" rel="nofollow - StudioFACA Portal :: http://www.renes.si/" rel="nofollow - Nepremičnine

Posted By: ub3rl337ch3ch
Date Posted: 12 December 2005 at 4:59pm

http://www.w3schools.com/sql/sql_groupby.asp - http://www.w3schools.com/sql/sql_groupby.asp

the syntax seems right. I think the problem is that you have values of 'argument' like:

argument

xxx

yyy

zzz

is what you're wanting a count of where argument = zzz?

then you'll need to cycle through your recordset to get there, because the first value in the recordset will be '1' (from xxx), then '2' (from yyy) then '4' which is what you want...

try tacking "WHERE argument = 'zzz'" on the end there... or actually, replace the groupby with that: it'll give you the count of the specific group you want...

Posted By: michael
Date Posted: 12 December 2005 at 6:03pm

Don't do a Count(*) when you group.

Try
Select Count(argument) from table group by argument

-------------
http://baumannphoto.com" rel="nofollow - Blog | http://mpgtracker.com" rel="nofollow - MPG Tracker

Posted By: faca5
Date Posted: 13 December 2005 at 6:52am

Thanks again!

-------------
http://www.studiofaca.com/" rel="nofollow - StudioFACA Portal :: http://www.renes.si/" rel="nofollow - Nepremičnine

Posted By: theSCIENTIST
Date Posted: 17 December 2005 at 3:47pm

If you are counting the ammount of records in the table you should do something like this;

Set testing = Server.CreateObject("ADODB.Recordset")



strSQL = "SELECT COUNT(id) AS myc FROM table;" 



testing.Open strSQL, adoCon



Response.Write(testing("myc"))

---- Or ----

Set testing = Server.CreateObject("ADODB.Recordset")



strSQL = "SELECT COUNT(id) myc FROM table;" 



Set testing = adoCon.Execute(strSQL)



Response.Write(testing("myc"))

This is how you should count, and if your column (id) is indexed, the count will be extremely fast.

-------------
:: http://www.mylittlehost.com/ - www.mylittlehost.com