Display Records from Access tables as a total in a table in a ASP Page

[Richo1980]

Hi all,

I'm trying to display records from a MS Access DB in an ASP page (table).. I don't want to display all the records though, I just want to display the total of these records..i.e in my table I would like to show how many calls someone has closed this year..

Joe Bloggs ---> 100 Closed

Is this possible? I was thinking that I need to write an ADO that includes COUNT but I'm not too sure how to write this.. Here's what I have so far:

<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "\\pathtoserver\gms_stats.mdb"
DIM mySQL, objRS
mySQL = "SELECT Count(*) AS intTotal FROM Closed_Remedy_2006 ORDER BY TechName"
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open recSQL, objConn

' Display result
Response.Write objRS("intTotal")


objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>

If anyone could point me in the right direction or offer any advise on what I'm doing wrong, I would be very grateful

Thanks
-Dave

 

[Chopstik]

I think you need to group your data by TechName. Try this:

mySQL = "SELECT COUNT(*) AS intTotal FROM Closed_Remedy GROUP BY TechName ORDER BY TechName"

This will allow you to return the number for each technician. If you need only for one technician, then just put that into a WHERE statement in your SQL code.

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[Chopstik]

Also, as a general rule in the forums, there is no need to double post in separate forums unless your question was inappropriately posted in one. Just a thought...

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[Richo1980]

Hi Chopstik,

My apologies for posting in 2 forums...I stuffed up my inital post in the Access forum and thought I should correct it..

Cheers
-Dave

 

[Chopstik]

Did you resolve your original problem?

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[Richo1980]

Hi,

Hmmm still no luck..

Here's the code I'm using:

<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "\\servername\c$\Inetpub\

http://wwwroot\Stats\UATDev\skidoo_too\gms_stats.mdb"

DIM mySQL, objRS
mySQL = "SELECT Count(*) AS intTotal FROM Closed_Remedy_2006 ORDER BY TechName"
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open recSQL, objConn
%>

<table border="1">
<%do until objRS.EOF%>
<tr>
<td><%Response.Write objRS("intTotal")%></td>
<%next
rs.MoveNext%>
</tr>
<%loop
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
%>

I think that the error may be in my output to the table...I haven't coded in a while

Thanks for your help so far, if you could offer any more suggestions I'd be very thankful

Cheers
-Dave

 

[Chopstik]

Try this (note how it is grouping by the TechName):

<%
set conn=Server.CreateObject("ADODB.Connection")
conn.Provider="Microsoft.Jet.OLEDB.4.0"
conn.Open "\\servername\c$\Inetpub\[URL unfurl="true"]wwwroot\Stats\UATDev\skidoo_too\gms_stats.mdb"[/URL]
DIM mySQL, objRS
[COLOR=red]mySQL = "SELECT COUNT(*) AS intTotal FROM Closed_Remedy GROUP BY TechName ORDER BY TechName" [/color]
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open recSQL, objConn
%>

<table border="1">
<%do until objRS.EOF%>
    <tr>
       <td><%Response.Write objRS("intTotal")%></td>
    <%next
    rs.MoveNext%>
    </tr>
<%loop
objRS.Close
Set objRS = Nothing 
objConn.Close
Set objConn = Nothing
%>

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill

 

[Richo1980]

Hi,

I now get the following error when I attempt to open page

Error Type:
ADODB.Recordset (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.
/skidoo_too/test.asp, line 85

Line 85 refers to

objRS.Open recSQL, objConn

I've checked out the error on Google and it appears to be something to do with the way I'm opening the Recordset..Any ideas?

Thanks again
-Dave

 

[Chopstik]

Yeah, you're telling it that your SQL string is recSQL, but it should probably be "mySQL". Try this:

objRS.Open mySQL, objConn

------------------------------------------------------------------------------------------------------------------------
"Men occasionally stumble over the truth, but most of them pick themselves up and hurry off as if nothing ever happened."
- Winston Churchill