Getting Number of Records - Total and In a Field

[fillup07]

1) What is the BEST way to get the total number of records in a table?


2) What is the BEST way to get the total number of records in a certain field (ex: 10 total rows, 7 of them have info in column A)?

-Phil
fillup07@hotmail.com

 

[snowboardr]

Here is question 1 answer, there is other ways though... and question 2 is below, although Im not sure if that is correct, but I think it is.
<%
Dim ConnString, strSQL

ConnString = &quot;Provider=Microsoft.Jet.OLEDB.4.0; Data Source=&quot; & Server.MapPath(&quot;database.mdb&quot

' ADO connection class
set my_conn= Server.CreateObject(&quot;ADODB.Connection&quot

' rs recordset will contain all fields in db such as userID, name, email
set rs = Server.CreateObject(&quot;ADODB.RecordSet&quot

' Connect ADO & open database
my_conn.Open ConnString

strSQL = &quot;SELECT COUNT(*) FROM table_name&quot;

' Execute SQL statement
rs.open strSQL, my_conn

' Write out the number of records
Response.Write &quot;There are <b>&quot; & rs.Fields(0) & &quot;</b> records in the database.&quot;

rs.close ' Close database connection
set rs = nothing 'obj variable released

%>
2

change your sql statement to:

strSQL = &quot;SELECT COUNT(*) FROM table_name WHERE table_name NOT NULL&quot;

www.vzio.com

/

 

[fillup07]

Okay, I was told to do something similar... but instead of using Count(*) to use Count(ID) or something. What's the difference. And, if I want to use the data from the database as well, do I need to close the RS and open a new one? -Phil
fillup07@hotmail.com

 

[snowboardr]

Yes close it then open it, because you have to change the sql statement. As far as I know you can select which columns you want to count, but that doesnt really make since because if you are counting all records, why does it need to have a specific column?

the format is like this:

Count(ID, user_name, user_email) etc..
www.vzio.com

/

 

[fillup07]

I don't really understand it then...

If the format is like Count(ID, user_name, user_email) etc..

Then what's the difference what column or columns you put in there if they all return the total number of records???? Maybe I'm not understanding this correctly... -Phil
fillup07@hotmail.com

 

[snowboardr]

Thats exactly what I was saying, but thats the format of SQL... but nevermind that doesnt work, only one column name has to placed in the () or you can just use an astrick which is a wild card symbol. www.vzio.com

/