Help with blogger... Counting total rows in database table.

Hello,

I am creating a simple one page blogger for page visitors to 'rant' so to speak... This is a remake of the old one and I've got about 49 pages of 5 entries each from the old one...

I need to make different dynamic pages using querystrings in the url which is simple enough. The page numbers are dynamic based upon totall records in the 'rants' table divided by 5 entries per page...

Previously I've used a 'for next' statement nested in an 'if then' to actually go throuch each record and count it but it taxes the server while proccessing other parts of the page...

MY QUESTION IS: is there a SQL command that I can use for my ODBC connection to get the total number or rows in the 'rants' table?

SELECT COUNT(*) from TABLE_NAME will return the total number of rows in the table.

Cool but how do I get that into a variable?

Huh ?

1) Create a Connection object to connect to your database

2) Createa a Recordset object that will hold the result of your Query

3) Buil your SQL Query(showed by karsh44)

4) Declare a variable that will hold the value of your recordset

5) Execute the SQL Query

6) Put the value of the recordset inside the variable you've define

7) Close recordset and connection object

8) Response.Write the variable


Hope this helps!
Sincerely

Vlince

Here's What I have. I hate asking you all this and anoying you.

Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.ConnectionTimeout=60
MyConn.Open "DSN=files"

Set recordSet=Server.CreateObject("ADODB.Recordset")
recordSet.Open "SELECT COUNT(*) FROM rant", MyConn,1,2

Dim totalRants

totalRants = "What Goes Here?"

<%
. . .
. .
.

Dim totalRants

If recordSet.EOF Then

Response.Write "No records found!"
Response.End

Else

totalRants = recordSet(0)

End If

recordSet.Close
Set recordSet = nothing

MyConn.Close
Set MyConn = nothing


'FOR DEBUG ONLY
Response.Write "-->" & totalRants & "<--<hr>"
Response.End
%>

Hope this helps!
Sincerely

Vlince

Thank you so much

the (0) part was what I was having trouble with... if it was a field I was working with it would have been simple.

Many thanks to the ASP Gods.

Well what you should've done is create an alias in your query something like:

"SELECT COUNT(*) as Total FROM rant"

Then you'd use it like this:
totalRants = recordSet("Total")

Hope this helps!
Sincerely

Vlince