Record Count

[RTDesi]

I'm trying to get a record count of all the records in each table, then display it next to each table name (which corresponds to each category)
As of now I have :

<%
dim rs, oConn, tSQL

Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot

Set oConn = Server.CreateObject(&quot;ADODB.Connection&quot

oConn.Open &quot;DSN=uploadsn&quot;

'aftermarket
tSQL=&quot;SELECT Count(*) AS TOTAL_AFTRMKT FROM AFTERMARKET;&quot;

'customerservice
tSQL = &quot;SELECT COUNT(*) AS TOTAL_CS FROM CUSTOMER_SERVICE;&quot;

RS.Open tSQL, oConn

oConn.Execute tSQL
set oConn = nothing

Then, later on in the body of the document, I reference each query and write the total number of records in each table:

Aftermarket (<% Response.Write(rs(&quot;TOTAL_AFTRMKT &quot)%>)
Cust. Service (<% Response.Write(rs(&quot;TOTAL_CS&quot)%>)

But, this is not working for some reason.
Any info on this will be greatly appreciated. Thanks in advance.

 

[Mighty]

You need to do the following:

dim rs, oConn, tSQL
dim total_aftrmkt, totalcs

Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot

Set oConn = Server.CreateObject(&quot;ADODB.Connection&quot

oConn.Open &quot;DSN=uploadsn&quot;

'aftermarket
tSQL=&quot;SELECT Count(*) AS TOTAL_AFTRMKT FROM AFTERMARKET;&quot;
RS.Open tSQL, oConn
total_aftrmkt = RS(&quot;TOTAL_AFTRMKT&quot
RS.close

'customerservice
tSQL = &quot;SELECT COUNT(*) AS TOTAL_CS FROM CUSTOMER_SERVICE;&quot;
RS.Open tSQL, oConn
total_cs = RS(&quot;TOTAL_CS&quot
RS.close

oConn.close
set rs = nothing : set oConn = nothing
.
.
.
.
Aftermarket (<%=TOTAL_AFTRMKT%>)
Cust. Service (<%=TOTAL_CS%>)



Mise Le Meas,

Mighty

 

[RTDesi]

The above sugguestion, for some reason, did not work for me. Any further info?

 

[Mighty]

What do you mean it didn't work?
What's happenning. Are you getting errors? Is there no output??

More info please. Mise Le Meas,

Mighty

 

[RTDesi]

I'm not getting any errors, however I am not getting any output either. Here's what I currently have, per your sugguestion:

dim rs, oConn, tSQL
dim total_aftermarket

Set rs = Server.CreateObject(&quot;ADODB.Recordset&quot

Set oConn = Server.CreateObject(&quot;ADODB.Connection&quot

oConn.Open &quot;DSN=uploadsn&quot;

'aftermarket
tSQL=&quot;SELECT Count(*) AS TOTAL_AFTRMKT FROM AFTERMARKET;&quot;
RS.Open tSQL, oConn
total_aftermarket = RS(&quot;TOTAL_AFTRMKT&quot
RS.close

oConn.close
set rs = nothing : set oConn = nothing

To display outupt:
Aftermarket (<%=total_aftermarket%>)

Thanks agian

 

[Mighty]

Are you using MS Access as a database. If so, acn you try rinning the SQL command in Access to make sure that the command does actually return a value.

apart from that, can you post your entire code so that I can take a closer look. Mise Le Meas,

Mighty

 

[link9]

If I could interject for a sec...

You can always use the trusty ole .recordCount property for the recordset --

rs.cursorType = 3
RS.Open tSQL, oConn

totalRecords = rs.recordCount

Just make sure your your cursorType is 1 or 3 (keyset and static, respectively) and it should work just fine.

Just a thought, and not to contradict Mighty, because his method should also work just fine.

good luck!
Paul Prewett

 

[Mighty]

I thought about using that but it meant pulling the entire table from the database and I was unsure if this would be a performance hit. Mise Le Meas,

Mighty

 

[RTDesi]

I am using an MSAccess database. The queries work fine there. The above code is all that's really pertinent here, the rest is just extranneous JavaScript/HTML stuff(and its like PAGES of stuff)

In order to do the .recordCount property that was mentioned earlier, do I have to adjust anything in the database itself? You'll have to excuse the ignorance, I'm fairly new at this whole ASP thing. Thanks in advance.

rs.cursorType = 3 --how do I know the cursor type?
RS.Open tSQL, oConn

totalRecords = rs.recordCount -- is totalRecords here referring to the query name or the name of the table?

 

[Mighty]

You don't have to do anything to your table to use the recordcount property. You need to set the cursor type to a certain value to be able to use this property. The best one to use is a cursor type of aduseclient ( i.e. 3)
totalrecords is just a variable that you declare in your asp program to store the value of rs.recordcount. Mise Le Meas,

Mighty

 

[link9]

Ah, yes, but adUseClient is is a cursorLocation, not a cursorType --

A cursor type of 3 is adOpenStatic


Paul Prewett