asked on

Counting total number of database records using SQL and ASP

Dear EE community,

Very simple and quite embarrassing problem but it's sometimes easier to ask on here. I can't get my asp page to output a total of all records on my database, "callnotes".

I've attached the code below. Anything blatantly obvious that I'm doing wrong?

Thanks as always.

Regards,
Luke

---connstr.asp---
<%
Set OBJdbConn = Server.CreateObject("ADODB.Connection")
OBJdbConn.Open = "Driver={MySQL ODBC 3.51 Driver};Server=centos5;Database=ijtd_export; User=root;Password=****;Option=3;"
Set OBJdbConn2 = Server.CreateObject("ADODB.Connection")
OBJdbConn2.Open = "Driver={MySQL ODBC 3.51 Driver};Server=centos5;Database=outcall; User=root;Password=****;Option=3;"
Set OBJdbConn3 = Server.CreateObject("ADODB.Connection")
OBJdbConn3.Open = "Driver={MySQL ODBC 3.51 Driver};Server=centos5;Database=ijtd_export; User=root;Password=****;Option=3;"
%>
 
---livestats.asp---
<% 
Dim rsQuery
 
SQLQuery = "SELECT count(NoteID) FROM callnotes"
 
Set rsQuery = OBJdbConn2.Execute(SQLQuery)
 
Dim GlobalTotal
GlobalTotal = rsQuery("NoteID")
%>
 
Total records: <%=GlobalTotal%>
 
<%
OBJdbConn2.Close
Set OBJdbConn2 = Nothing
%>

Open in new window

ASKER CERTIFIED SOLUTION

Wouter Boevink

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

BenthamLtd

ASKER

Thank you! lol I can't believe I forgot to add that. Much respect.

Haris V

1. #!/bin/bash

tables=`mysql -uUser -pPass -hHost database_name --skip-column-names -e
"SHOW TABLES"`
total=0
for table in $tables
do
rows=`mysql -uUser -pPass -hHost database_name --skip-column-names
-e "SELECT COUNT(*) from $table"`
total=$(($total+rows))
done
echo $total

2. http://www.base64.co.uk/mysql-paging-results-perl/